SQL Aggregations, Joins, And Metric Queries

What's being tested

Candidates must show they can write clear, correct SQL to compute product metrics: aggregations, joins, deduplication, and time-bucketing. Interviewers probe whether you can avoid double-counting, pick the right denominators, and express metric logic so analysts and dashboards agree. For a Product Manager, the focus is accuracy and explainability of metrics, not database internals.

Patterns & templates

• Last-event-per-entity — use ROW_NUMBER() OVER (PARTITION BY user ORDER BY ts DESC) to pick a single row; break ties with a stable key like id.

• Dedup + aggregate — dedupe in a CTE: WITH dedup AS (...) SELECT user_id, COUNT(*) FROM dedup GROUP BY user_id.

• Distinct counts — use COUNT(DISTINCT user_id) for unique users; for large cardinalities consider approximate alternatives outside SQL.

• Join types — LEFT JOIN preserves base population (denominator); INNER JOIN filters to matching rows (use deliberately).

• Time bucketing — create windows with date_trunc('day', ts) or ts::date; align event and user tables to same timezone.

• Window vs group — use window functions (SUM(...) OVER (PARTITION BY ...)) for running totals, GROUP BY for per-bucket aggregates.

• NULL handling & defaults — use COALESCE(metric, 0) to avoid NULLs breaking math in downstream code or dashboards.

• Readable composition — split logic into named CTEs for dedupe, filter, join, and final aggregation to make reviews fast.

Common pitfalls

Pitfall: Joining event table to user table with INNER JOIN unexpectedly drops users with no events, shrinking your denominator and inflating rates.

Pitfall: Not deduplicating events before SUM/COUNT leads to double-counting when multiple event rows map to one user action.

Pitfall: Mixing timezones or using ts vs date_trunc inconsistently causes off-by-one-day bugs in retention/DAU metrics.

Practice these

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