Month-over-Month Active Listings | Dataford Interview Questions

Write a query to calculate the month-over-month growth rate of active listings on the PriceLabs platform.

Use the provided listing and status history data. Treat a listing as active for a month when its latest status event through that month's end is active.

Output

One row per calendar month from the first through last recorded status event.
Columns: month_start, active_listings, and growth_rate_pct.
Set growth for the first month to NULL. Calculate later growth as the percentage change from the prior month.
Order by month_start ascending.

Schema

listings

Column	Type	Description
listing_idPK	INT	Unique PriceLabs listing identifier
listing_name	VARCHAR(100)	Listing display name
region	VARCHAR(80)	Listing market or region

listing_status_history

Column	Type	Description
status_event_idPK	INT	Unique status event identifier
listing_id	INT	Referenced listing identifier
status	VARCHAR(20)	Status recorded by the event
effective_at	TIMESTAMP	Timestamp when the status became effective

Your question is Month-over-Month Active Listings. Start with the requirements and the two tables in the Question tab.

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

Problem