Optimizing a Slow Query | Dataford Interview Questions

How would you optimize a slow-running query?

Using the provided PostgreSQL schema, write an optimized query that returns monthly metrics for active sites during January and February 2025. The query should avoid unnecessary rows and preserve sites with activity in either metric source.

Output

One row per active site and month with activity
Columns: site_id, site_name, month, pageviews, and revenue
Include only January and February 2025 activity, sort by site_id, then month ascending, and return zero revenue when no non-null revenue exists

Schema

sites

Column	Type	Description
site_idPK	INT	Unique site identifier
site_name	VARCHAR(100)	Display name of the site
is_active	BOOLEAN	Whether the site is currently active

page_views

Column	Type	Description
view_idPK	INT	Unique page-view identifier
site_id	INT	Site associated with the page view
viewed_at	TIMESTAMP	Timestamp when the page was viewed

ad_revenue

Column	Type	Description
revenue_idPK	INT	Unique revenue record identifier
site_id	INT	Site associated with the revenue
recorded_at	TIMESTAMP	Timestamp associated with the revenue record
revenue_amount	NUMERIC(12,2)	Revenue amount for the record

Your question is Optimizing a Slow Query. Start with the requirements and the three 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