Pivot Daily to Weekly SQL | Dataford Interview Questions

Your question is Pivot Daily to Weekly SQL. Start with the requirements and the two tables on the right.

Run and submit as often as you like. When you're ready, talk me through your approach or go straight to the code.

Problem

Airbnb's Search analytics team wants a weekly view of search activity for active listings. Write a PostgreSQL query that pivots daily listing search views into weekday columns for the weeks beginning March 3 and March 10, 2025.

Requirements

Include every active listing for both weeks, including listings with no recorded metrics.
Return one row per listing and week, with separate search-view columns for Monday through Sunday.
Use conditional aggregation and return missing daily values as zero.
Order results by listing_id and week_start.

Schema

listings

Column	Type	Description
listing_idPK	INT	Unique Airbnb listing identifier
listing_name	VARCHAR(120)	Listing display name
neighborhood	VARCHAR(80)	Listing neighborhood
is_active	BOOLEAN	Whether the listing is currently active

listing_daily_metrics

Column	Type	Description
metric_idPK	INT	Unique daily metric identifier
listing_id	INT	Referenced Airbnb listing
metric_date	DATE	Date of the Search metric
search_views	INT	Listing views generated from Airbnb Search