Common query patterns

A handful of shapes recur across nearly every SQL interview. Recognizing the pattern behind a word problem is most of the battle.

• Filter then aggregate: WHERE (row filter) runs before GROUP BY; HAVING filters after aggregation (e.g. groups with COUNT(*) > 5).
• Deduplication: ROW_NUMBER() partitioned by the key, keep rn = 1.
• Self-join: compare rows in one table to each other (manager/employee, pairs, gaps).
• Conditional aggregation: SUM(CASE WHEN ... THEN 1 ELSE 0 END) to pivot categories into columns.
• Date bucketing: DATE_TRUNC('month', ts) to roll events up to a period.

-- Monthly active users and paid share, one row per month
SELECT
  DATE_TRUNC('month', event_ts)                          AS month,
  COUNT(DISTINCT user_id)                                AS mau,
  COUNT(DISTINCT CASE WHEN plan = 'paid' THEN user_id END) AS paid_mau
FROM events
GROUP BY 1
HAVING COUNT(DISTINCT user_id) > 100
ORDER BY 1;