🧱 WanderBricks SQL Analytics Project

A fully annotated, production-style SQL portfolio project built on the WanderBricks short-term rental dataset (Databricks samples.wanderbricks.*).
20 analytical queries covering data quality, revenue intelligence, funnel analysis, support operations, and guest reviews — each with full commentary, design notes, and extension ideas.

Zack Bradley
Platform: Databricks SQL
Dialect: Apache Spark SQL / ANSI SQL
Dataset: samples.wanderbricks.*

wanderbricks-sql-project/
│
├── README.md                          ← You are here
│
├── schema/
│   └── wanderbricks_schema.sql        ← Full schema reference with all 12 tables
│
├── docs/
│   └── query_index.md                 ← Quick-reference table of all 20 queries
│
└── queries/
    ├── 01_bookings/
    │   ├── q01_booking_status_consistency.sql
    │   ├── q02_average_stay_length.sql
    │   ├── q03_revenue_by_month.sql
    │   └── q04_guest_count_distribution.sql
    │
    ├── 02_properties/
    │   ├── q05_properties_missing_amenities.sql
    │   ├── q06_top_amenity_categories.sql
    │   ├── q07_primary_image_check.sql
    │   ├── q08_destination_coverage.sql
    │   └── q09_country_property_density.sql
    │
    ├── 03_users_and_hosts/
    │   ├── q10_business_vs_individual_spend.sql
    │   └── q11_host_performance.sql
    │
    ├── 04_payments/
    │   ├── q12_refund_detection.sql
    │   └── q13_payment_lag.sql
    │
    ├── 05_engagement/
    │   ├── q14_user_funnel_analysis.sql
    │   └── q15_device_performance.sql
    │
    ├── 06_support/
    │   ├── q16_extract_latest_message.sql
    │   ├── q17_sentiment_analysis.sql
    │   └── q18_conversation_duration.sql
    │
    └── 07_reviews/
        ├── q19_top3_properties_per_destination.sql
        └── q20_low_rating_root_causes.sql

The WanderBricks dataset models a short-term vacation rental marketplace — think Airbnb/VRBO — with 12 core tables spanning bookings, properties, users, payments, and engagement signals.

users ──────────────────────────────────────────────────┐
  │                                                      │
  ├──► bookings ──────► booking_updates                  │
  │         │                                            │
  │         └──► payments                                │
  │                                                      │
  ├──► clickstream ──────► page_views                    │
  │                                                      │
  └──► customer_support_logs (messages[])                │
                                                         │
hosts ──► properties ──► destinations ──► countries      │
              │                                          │
              ├──► property_amenities ──► amenities      │
              │                                          │
              ├──► property_images                       │
              │                                          │
              └──► reviews ◄───────────────────────────-─┘

Identifies bookings where:

• bookings.status and booking_updates.status disagree

Purpose: Detect data pipeline sync failures or race conditions between the source-of-truth table and the event log.

select
a.booking_id
,a.status
,b.status
from samples.wanderbricks.bookings a
inner join samples.wanderbricks.booking_updates b
on a.booking_id = b.booking_id
where a.status != b.status
order by a.booking_id asc

Explanation: Uses an INNER JOIN to compare status values across both tables. Any row returned signals a mismatch that needs reconciliation. An empty result = tables are in sync ✅

<

Calculates:

• Average number of nights stayed per destination
• Sorted longest to shortest

Purpose: Understand destination appeal and inform pricing tiers and marketing campaigns.

select
c.destination 
,round(avg(date_diff(a.check_out, a.check_in)),2) as avg_stay_length
from samples.wanderbricks.booking_updates a
join samples.wanderbricks.properties b
on a.property_id = b.property_id
join samples.wanderbricks.destinations c
on b.destination_id = c.destination_id
group by c.destination
order by avg_stay_length desc

Explanation: Uses DATEDIFF(check_out, check_in) to compute nights per booking, then averages across all bookings per destination via a 3-table join chain.

Calculates per month (completed bookings only):

• Total original booking amount
• Total updated/adjusted amount
• Signed price difference between the two

Purpose: Audit post-booking price adjustments and measure revenue reconciliation gaps month over month.

with main as(
select 
date_format(a.check_in,'yyyy-MM') as month
,sum(a.total_amount) as total_amount
,sum(b.total_amount) as total_amount_updates
,b.status
from samples.wanderbricks.bookings a
join samples.wanderbricks.booking_updates b
on a.booking_id = b.booking_id
where b.status = 'completed'
group by all
order by month desc
)
,
solution (
select
month
,status
,format_number(total_amount, '$###,###.##') as total_amount
,format_number(total_amount_updates, '$###,###.##') as total_amount_updates
,format_number(total_amount_updates - total_amount, '$###,###.##') as price_diff
from main
)
select * from solution

Explanation: Two-CTE pattern — main aggregates raw sums by month, solution formats them as currency strings and surfaces the delta. A positive price_diff = upward adjustment; negative = discount or correction applied post-booking.

Calculates:

• Number of bookings grouped by guest count
• Broken down by property type

Purpose: Understand which property types attract solo travelers vs. large groups for capacity planning and pricing tiers.

select
b.property_id
,count(a.booking_id) as bookings
,a.guests_count as guest_count
,b.property_type
from samples.wanderbricks.booking_updates a
join samples.wanderbricks.properties b
on a.property_id = b.property_id
group by all
order by guest_count desc

Explanation: Uses GROUP BY ALL (Databricks shorthand) to group on all non-aggregated columns. COUNT(booking_id) tallies bookings per property-type and guest-count combination.

Ranks:

• Amenity categories by how often they appear per property
• Using DENSE_RANK() so tied categories share the same rank

Purpose: Reveal which amenity categories are most common — informs search filters and host guidance on what guests expect most.

select 
property_id
,category
,dense_rank() over(partition by b.property_id order by count(*) desc) ranking
,count(*) as count_frequency
from samples.wanderbricks.amenities a
join samples.wanderbricks.property_amenities b
on a.amenity_id = b.amenity_id
group by b.property_id, category
order by property_id, ranking

Explanation: Groups by property and category to count amenity frequency, then applies DENSE_RANK() so the most-common category per property = rank 1. Unlike RANK(), DENSE_RANK() produces no gaps when categories tie.

Ensures each property has:

• Exactly one is_primary = TRUE image

Classifies each property as:

• Valid — exactly 1 primary image ✅
• Missing primary image — 0 primary images
• Multiple primary images — more than 1

Purpose: Prevent broken thumbnails and ambiguous display states across the platform.

with image_counts as (
select
property_id
,sum(case when is_primary = true then 1 else 0 end) as primary_count
,count(*) as total_images
from samples.wanderbricks.property_images
group by property_id
)

select
ic.property_id
,ic.total_images
,ic.primary_count
,case
when ic.primary_count = 0 then 'Missing primary image'
when ic.primary_count = 1 then 'Valid'
else 'Multiple primary images'
end as image_status
from image_counts ic
order by ic.primary_count, ic.property_id

Explanation: CTE pre-aggregates primary image counts per property. The CASE expression in the main query then classifies each property into one of three audit states — rows where image_status != 'Valid' are remediation targets.

Counts:

• Number of properties per destination
• Enriched with country and continent from reference tables

Purpose: Geographic inventory view to identify underserved markets and guide host acquisition strategy.

with property_counts as (
select
d.destination_id
,d.destination
,d.country
,d.state_or_province
,count(p.property_id) as property_count
from samples.wanderbricks.destinations d
left join samples.wanderbricks.properties p
on d.destination_id = p.destination_id
group by
d.destination_id, d.destination,d.country, d.state_or_province
)
select
pc.destination_id
,pc.destination
,pc.country
,c.continent
,pc.state_or_province
,pc.property_count
from property_counts pc
left join samples.wanderbricks.countries c
on pc.country = c.country
order by pc.property_count desc

Explanation: Double LEFT JOIN preserves destinations with zero properties (registered markets with no supply) and destinations whose country has no entry in the countries table. Both are strategically important gaps to surface.

Ranks:

• Countries by total number of properties listed

Purpose: Country-level supply view to prioritize business development and host recruitment in underserved markets.

with property_count as
(

select 
count(property_id) as pcount
,destination_id
,title
from samples.wanderbricks.properties
group by all
)
,
countries as
(
select 
destination_id
,country
from samples.wanderbricks.destinations

)
select 
sum(pc.pcount) as number_of_properties
-- ,c.destination_id
,c.country
from property_count pc
join countries c
on pc.destination_id = c.destination_id
group by all
order by number_of_properties desc

Explanation: Two-CTE pattern — first counts properties per destination, then re-aggregates by country to sum across all destinations within each nation. Produces a clean country-level supply leaderboard.

Compares:

• Average booking revenue for is_business = TRUE accounts
• vs. is_business = FALSE accounts
• Completed bookings only

Purpose: Quantify the B2B revenue premium to inform corporate pricing strategy and sales team priorities.

select 
b.is_business,
avg(a.total_amount) as avg_total_amount
from samples.wanderbricks.bookings a
join samples.wanderbricks.users b
on a.user_id = b.user_id
where a.status = 'completed'
group by b.is_business
order by b.is_business desc

Explanation: Joins bookings to users on user_id and segments by the boolean is_business flag. Filtering WHERE status = 'completed' ensures only realized revenue is measured — cancellations and pending bookings are excluded.

Calculates:

• Running average rating per host
• Ordered from highest to lowest rating

Purpose: Score host quality for Superhost tiering programs and identify low-performing hosts who need coaching.

select 
a.name
,round(avg(a.rating) over(partition by name order by rating desc),2) as avg_rating
from samples.wanderbricks.hosts a

Explanation: Window function AVG() OVER (PARTITION BY name ORDER BY rating DESC) computes a running cumulative average per host, starting at their highest rating. For a simple single-number average per host, use GROUP BY name with AVG(rating) instead.

Finds bookings that have:

• A completed payment AND a refunded payment

Purpose: Monitor chargeback risk, cancellation policy disputes, and potential fraud patterns.

-- Summary: which booking_ids have both statuses?
select 
booking_id
,amount
,status
from samples.wanderbricks.payments
where status in ('completed','refunded')
and booking_id in (
select booking_id
from samples.wanderbricks.payments
where status in ('completed','refunded')
group by booking_id
having count(distinct status) = 2
)
order by booking_id, status

Explanation: HAVING COUNT(DISTINCT status) = 2 is the core logic — a booking_id only qualifies if both 'completed' and 'refunded' statuses exist for it. The detail query then surfaces full payment rows so original and refund amounts appear side by side for audit.

Calculates:

• Days between booking_updates.created_at and payments.payment_date

Purpose: Measure payment processing delays to detect billing friction, slow invoicing, or pipeline bottlenecks.

select distinct
a.booking_id
,cast(a.created_at as date)
,cast(b.payment_date as date)
,datediff(b.payment_date, a.created_at) as days_between
from samples.wanderbricks.booking_updates a
join samples.wanderbricks.payments b
on a.booking_id = b.booking_id
order by created_at asc

Explanation: Both timestamps are CAST to DATE to normalize any time component before computing the difference. A negative days_between means payment was logged before the booking update — a data ordering anomaly worth investigating.

Calculates conversion rates across:

• View → Click → Search → Booking

Purpose: Pinpoint where users drop off in the booking journey to prioritize UX improvements and A/B test targets.

with events as (
select 
user_id
,event
,min(timestamp) as first_event
from samples.wanderbricks.clickstream
group by user_id, event
),

funnel as (
select
e.user_id
,max(case when e.event = 'view' then 1 else 0 end) as viewed
,max(case when e.event = 'click' then 1 else 0 end) as clicked
,max(case when e.event = 'search' then 1 else 0 end) as searched
,case when b.booking_id is not null then 1 else 0 end as booked
from events e
left join samples.wanderbricks.bookings b
on e.user_id = b.user_id
group by e.user_id, b.booking_id
),

aggregation as (
select
count(*) as total_users
,sum(viewed) as total_views
,sum(clicked) as total_clicks
,sum(searched) as total_searches
,sum(booked) as total_bookings
from funnel
)

select
total_views
,total_clicks
,total_searches
,total_bookings
,total_users
,round((total_views/total_users)*100,2) as pct_viewed
,round((total_clicks/total_users)*100,2) as pct_clicked
,round((total_searches/total_users)*100,2) as pct_searched
,round((total_bookings/total_users)*100,2) as pct_booked
from aggregation

Explanation: Three-CTE pipeline — events collapses clickstream to one row per user/event, funnel pivots events into binary flags using MAX(CASE WHEN), and aggregation sums them to produce platform-wide totals before the final SELECT computes percentage rates.

Counts engagement by:

• Device type: desktop, mobile, tablet
• Referrer channel: ad, direct, email, google

Purpose: Allocate marketing spend and inform responsive design priorities based on where users actually come from.

select 
sum(case when b.device_type = 'desktop' then 1 else 0 end) as total_desktop
,sum(case when b.device_type = 'mobile' then 1 else 0 end) as total_mobile
,sum(case when b.device_type = 'tablet' then 1 else 0 end) as total_tablet
,sum(case when b.referrer = 'ad' then 1 else 0 end) as total_ad
,sum(case when b.referrer = 'direct' then 1 else 0 end) as total_direct
,sum(case when b.referrer = 'email' then 1 else 0 end) as total_email
,sum(case when b.referrer = 'google' then 1 else 0 end) total_google_search
from samples.wanderbricks.clickstream a
left join samples.wanderbricks.page_views b
on a.user_id = b.user_id

Explanation: Conditional aggregation pivots categorical values into separate numeric columns in a single pass — more efficient than multiple filtered subqueries. The LEFT JOIN preserves all clickstream users even if they have no page_view record.

Unnests the messages[] array and returns:

• The most recent message per support ticket

Purpose: Power support dashboards, SLA tracking, and agent handoff summaries with the latest context per ticket.

select
support_agent_id
,cast(message as string) as message
,ticket_id
,regexp_substr(cast(message as string), '[0-9]{4}-[0-9]{2}-[0-9]{2}') as real_date
from (
select
support_agent_id
,ticket_id
,user_id
,explode(messages) as message
from samples.wanderbricks.customer_support_logs
) base
qualify row_number() over(partition by ticket_id order by regexp_substr(cast(message as string), '[0-9]{4}-[0-9]{2}-[0-9]{2}') desc) = 1
order by ticket_id asc

Explanation: Four-CTE pipeline — EXPLODE() flattens the nested messages array into rows, CAST AS STRING enables regex parsing, REGEXP_SUBSTR extracts the embedded ISO date, and ROW_NUMBER() isolates the latest message per ticket before the final filter.

Extracts from each message string:

• reporter — who sent the message (agent or user)
• mood — sentiment label (angry, caring, helpful, etc.)

Purpose: Monitor emotional tone of support interactions to trigger escalations and measure agent quality.

select
support_agent_id
,cast(message as string) as message
,ticket_id
,regexp_substr(cast(message as string), '[0-9]{4}-[0-9]{2}-[0-9]{2}') as real_date
,regexp_extract(CAST(message as string), ',\\s*([^,]+),\\s*[^,]+,\\s*[0-9]{4}-[0-9]{2}-[0-9]{2}T[0-9:]{8}', 1) as reporter
,regexp_extract(cast(message as string), ',\\s*([^,]+),\\s*[0-9]{4}-[0-9]{2}-[0-9]{2}T[0-9:]{8}', 1) as mood
from (
select
support_agent_id
,ticket_id
,user_id
,explode(messages) as message
from samples.wanderbricks.customer_support_logs
) base
qualify row_number() over(partition by ticket_id order by regexp_substr(cast(message as string), '[0-9]{4}-[0-9]{2}-[0-9]{2}') desc) = 1
order by ticket_id asc

Explanation: Two REGEXP_EXTRACT calls target different positional fields within the serialized message struct string. QUALIFY filters to the most recent message per ticket without a subquery wrapper — a Databricks SQL shorthand for post-window filtering.

Calculates:

• Days between first and last message in each support ticket

Purpose: Measure SLA compliance and resolution time benchmarking across support agents.

with exploded as (
select
support_agent_id
,ticket_id
,cast(message as string) as message
,regexp_substr(cast(message as string), '[0-9]{4}-[0-9]{2}-[0-9]{2}') as real_date
from samples.wanderbricks.customer_support_logs
lateral view explode(messages) as message
),
ranked as (
select
support_agent_id
,ticket_id
,message
,real_date
,row_number() over (partition by ticket_id order by real_date asc) as first_message
,row_number() over (partition by ticket_id order by real_date desc) as last_message
from exploded
),
date_extract as (
select
ticket_id
,max(case when first_message = 1 then message end) as first_message
,max(case when last_message = 1 then message end) as last_message
,min(real_date) as first_date
,max(real_date) as last_date
from ranked
group by ticket_id
)
select 
a.ticket_id
,b.messages as message
,first_date
,last_date
,date_diff(a.last_date, a.first_date) as duration
from date_extract a
join samples.wanderbricks.customer_support_logs b
on a.ticket_id = b.ticket_id
order by ticket_id

Explanation: Dual ROW_NUMBER() window functions assign first and last message ranks simultaneously. MAX(CASE WHEN) then pivots the ranked rows into a single-row-per-ticket summary before DATEDIFF computes the conversation span.

Ranks properties within each destination:

• By guest rating using ROW_NUMBER()
• Returns top 3 per destination only

Purpose: Power "Top Picks" features, host recognition programs, and destination marketing pages.

select 
a.property_id
,a.title
,a.property_type
,b.destination
,c.rating
,row_number() over(partition by destination order by rating desc) as rank
from 
samples.wanderbricks.properties a
join samples.wanderbricks.destinations b
on a.destination_id = b.destination_id
join samples.wanderbricks.reviews c
on a.property_id = c.property_id
qualify rank <= 3
order by destination asc

Explanation: ROW_NUMBER() assigns a unique sequential rank within each destination partition — unlike RANK(), it never ties, guaranteeing exactly 3 rows per destination. QUALIFY rank <= 3 filters post-window without a subquery.

Joins reviews under 3 stars to properties and destinations to:

• Surface repeated complaint patterns
• Count frequency of the same comment across the same property and destination

Purpose: Detect systemic quality issues — a comment that recurs many times signals a structural problem, not a one-off complaint.

select 
a.property_id
,a.title
,a.property_type
,b.destination
,c.rating
,c.comment
,count(*) over(partition by comment, b.destination, a.title) as ratings_count
from 
samples.wanderbricks.properties a
join samples.wanderbricks.destinations b
on a.destination_id = b.destination_id
join samples.wanderbricks.reviews c
on a.property_id = c.property_id
where c.rating is not null
and c.rating < 3
order by title, destination, a.property_id, rating asc

Explanation: COUNT(*) OVER (PARTITION BY comment, destination, title) adds a frequency score to every row without collapsing them — high ratings_count values surface recurring complaints that should be prioritized for host coaching or listing removal.

Computes:

• Distance in kilometers between every pair of properties
• Using the Haversine spherical distance formula
• Sorted from nearest to farthest

Purpose:
Identify clusters of nearby properties for dynamic pricing, neighborhood grouping, map-based search features, and geo‑deduplication of duplicate listings.

select
a.property_id as property_id_1
,b.property_id as property_id_2
,a.title as property_title_1
,b.title as property_title_2
,a.property_latitude as lat1
,a.property_longitude as lon1
,b.property_latitude as lat2
,b.property_longitude as lon2
,round(6371 * 2 * asin(sqrt(power(sin(radians((b.property_latitude - a.property_latitude) / 2)), 2) 
+ cos(radians(a.property_latitude)) 
* cos(radians(b.property_latitude)) 
* power(sin(radians((b.property_longitude - a.property_longitude) / 2)), 2)
)), 2) as distance_km
from samples.wanderbricks.properties a
join samples.wanderbricks.properties b
on a.property_id < b.property_id
order by distance_km asc

Explanation: Uses the Haversine formula to compute great‑circle distance between two latitude/longitude points on Earth. The a.property_id < b.property_id condition prevents duplicate pairings (A–B and B–A). Sorting by distance_km surfaces extremely close listings — useful for detecting duplicates, multi‑unit buildings, or hyper‑dense markets.

Flags bookings where:

• bookings.total_amount does not match
• The sum of actual payments recorded in payments.amount

Purpose:
Detect billing inconsistencies, partial payments, overcharges, undercharges, and potential fraud or reconciliation errors.

with base as (
select 
a.booking_id
,check_in
,check_out
,format_number(total_amount,'#.##') as booking_payment
,abs(round(amount,2)) as actual_payment
-- ,payment_method
from samples.wanderbricks.bookings a
join samples.wanderbricks.payments b
on a.booking_id = b.booking_id
),
differences as (
select 
*
,round(try_subtract(booking_payment, actual_payment),2) price_difference
from base
where try_subtract(booking_payment, actual_payment)<> 0
)
select 
*
,case when price_difference > 0 then 'increase'
when price_difference < 0 then 'decrease'
else 'none' end as price_difference
from differences
order by booking_id asc

Explanation: The base CTE aligns booking totals with actual payment rows. try_subtract() safely computes the difference even when formatting or type mismatches occur. Any non‑zero difference indicates a suspicious discrepancy — either overpayment (increase) or underpayment (decrease). This query is essential for financial audits, chargeback investigations, and monthly reconciliation workflows.

• Status mismatches between bookings and booking_updates signal ETL sync gaps that need pipeline fixes
• Business accounts generate higher average booking revenue than personal accounts
• Some properties have no amenities assigned — a completeness gap that hurts conversion
• User engagement drops sharply between viewing and clicking — the biggest funnel leak
• Support tickets with repeated low-rating comments often point to systemic property issues, not one-off complaints

• SQL joins and aggregations across multi-table schemas
• Window functions: ROW_NUMBER(), DENSE_RANK(), AVG() OVER, COUNT() OVER
• CTEs for modular, readable query design
• Anti-join pattern for data completeness checks
• Array handling: EXPLODE(), LATERAL VIEW EXPLODE()
• Regex extraction from nested struct strings
• Conditional aggregation and pivot patterns
• QUALIFY clause for post-window filtering
• Business-driven analytics across 7 analytical domains

I Built this with Apache Spark SQL on Databricks | July 2026