SQL Analytics

What's being tested

These prompts test SQL analytics: turning messy transactional or event tables into reliable business metrics using joins, filters, aggregation, deduplication, and date logic. For a Data Scientist, the bar is not just syntactically correct SQL; it is metric correctness under edge cases like duplicate rows, missing dates, zero denominators, overlapping ownership, and cost/revenue attribution.

Patterns & templates

• Grain control before joining — define one row per user_id, ticket_id, campaign_id, or date; pre-aggregate to prevent many-to-many duplication.

• Conditional aggregation with SUM(CASE WHEN ... THEN 1 ELSE 0 END) or COUNT(*) FILTER (WHERE ...) for segmented metrics in one query.

• Revenue formulas should be explicit — net revenue often means SUM(revenue) - SUM(cost); use COALESCE for missing costs or zero activity.

• Deduplication via ROW_NUMBER() OVER (PARTITION BY key ORDER BY updated_at DESC); filter rn = 1 before aggregating.

• Date-window filtering with half-open intervals like event_ts >= start_date AND event_ts < end_date + INTERVAL '1 day' to avoid timestamp boundary bugs.

• Window functions such as LAG, SUM(...) OVER, and LAST_VALUE IGNORE NULLS for forward-fill, rolling totals, and time-windowed metrics.

• Safe rate metrics use clicks * 1.0 / NULLIF(impressions, 0); never allow integer division or divide-by-zero errors in CTR.

Common pitfalls

Pitfall: Joining raw fact tables before aggregation can silently multiply revenue, visits, clicks, or donations.

Pitfall: Using COUNT(column) when nulls matter can undercount; use COUNT(*), COUNT(DISTINCT ...), or conditional counts deliberately.

Pitfall: Reporting only rows with activity misses required zero-count entities; use a base table or calendar table plus LEFT JOIN.

Practice these

The practice cards below cover the canonical variants — solve all of them and time yourself.