Lesson 3 of 8 · 48 min

Aggregations & metric definitions

GROUP BY discipline, FILTER/CASE aggregates, distinct metrics, weighted ratios, ROLLUP awareness, and date spines.

Lesson 3 · Aggregations

GROUP BY, HAVING, and metrics that survive review

Aggregation is where product truth dies

Most take-home SQL and live analytics screens are aggregation problems wearing join costumes. You will be graded on GROUP BY completeness, filtered aggregates, distinct counts, ratio metrics, and whether your number matches a stated definition (GMV vs net, paid vs all). This lesson is the metric engineer toolkit.

Every aggregate answer should restate the metric definition in one line: active user = user with ≥1 session in the last 28 days, revenue = sum of paid order amounts excluding tax. Ambiguous English is not a free pass; name your assumption and proceed.

GROUP BY rules and functional dependency

In strict SQL, every non-aggregated SELECT expression must appear in GROUP BY (or be functionally dependent on the group key in some engines). Selecting users.name while grouping only users.country is invalid or nondeterministic. Interview move: group by the key, then join attributes back, or group by key+attribute if 1:1.

sql

1-- Invalid / nondeterministic pattern2SELECT country, name, COUNT(*)   -- name not determined by country3FROM users4GROUP BY country;56-- Valid: metric at country grain7SELECT country, COUNT(*) AS n_users8FROM users9GROUP BY country;1011-- Valid: attributes of the group key (user_id → name)12SELECT u.user_id, u.name, COUNT(o.order_id) AS n_orders13FROM users u14LEFT JOIN orders o ON o.user_id = u.user_id15GROUP BY u.user_id, u.name;

Filtered aggregates and pivot-style metrics

Prefer FILTER (PostgreSQL) or CASE inside aggregates over multiple scans. This is the standard way to build funnels and status breakdowns in one pass.

sql

1SELECT2  user_id,3  COUNT(*) AS orders_all,4  COUNT(*) FILTER (WHERE status = 'paid')   AS orders_paid,5  COUNT(*) FILTER (WHERE status = 'refund') AS orders_refunded,6  SUM(amount) FILTER (WHERE status = 'paid') AS gmv_paid,7  AVG(amount) FILTER (WHERE status = 'paid') AS aov_paid8FROM orders9GROUP BY user_id;1011-- Portable CASE form12SUM(CASE WHEN status = 'paid' THEN amount END) AS gmv_paid,13COUNT(CASE WHEN status = 'paid' THEN 1 END) AS orders_paid

Ratios, averages of ratios, and weighted mistakes

Average conversion is not the average of daily conversion rates if days have different volume. Interviewers plant 'average of averages' traps. Prefer sum(numerator)/sum(denominator) at the right grain.

sql

1-- Wrong: unweighted mean of daily rates2SELECT AVG(daily_cvr) FROM (3  SELECT day, SUM(converts)::float / SUM(visits) AS daily_cvr4  FROM funnel5  GROUP BY day6) d;78-- Right: overall rate (visits-weighted)9SELECT SUM(converts)::float / NULLIF(SUM(visits), 0) AS cvr10FROM funnel;1112-- Per-segment rates then compare - still compute each as ratio of sums

DISTINCT counts and approximate cardinality

COUNT(DISTINCT user_id) is the bread-and-butter unique metric. It is expensive at scale; warehouses may offer HyperLogLog sketches (approx). In interviews, exact DISTINCT is fine unless they ask for approx or streaming. Never COUNT(DISTINCT *) - meaningless/invalid.

sql

1-- DAU / WAU style2SELECT event_date, COUNT(DISTINCT user_id) AS dau3FROM events4WHERE event_name = 'app_open'5GROUP BY event_date;67-- Multi-condition uniques8COUNT(DISTINCT CASE WHEN event_name = 'purchase' THEN user_id END) AS purchasers

HAVING, QUALIFY cousins, and post-aggregate filters

HAVING filters groups. For 'top category per user' you often need windows (L4) or a join to max aggregates. Do not force everything into HAVING; know when a second stage query is cleaner.

sql

1-- Users with ≥3 paid orders and paid GMV ≥ 1002SELECT user_id,3       COUNT(*) AS paid_orders,4       SUM(amount) AS paid_gmv5FROM orders6WHERE status = 'paid'7GROUP BY user_id8HAVING COUNT(*) >= 39   AND SUM(amount) >= 100;

GROUPING SETS, ROLLUP, CUBE (interview awareness)

ROLLUP adds subtotals; CUBE all combinations; GROUPING SETS picks exact levels. You rarely hand-write these under 20 minutes, but recognizing them shows warehouse literacy. Know that NULL in a rollup row can mean 'all' vs 'unknown' - GROUPING() function disambiguates.

sql

1SELECT country, device, SUM(revenue) AS rev2FROM sales3GROUP BY ROLLUP (country, device);4-- rows for (country, device), (country, total), (grand total)56SELECT7  country,8  device,9  GROUPING(country) AS g_country,  -- 1 means rolled up10  SUM(revenue) AS rev11FROM sales12GROUP BY ROLLUP (country, device);

Time buckets and reporting calendars

DATE_TRUNC('week', ts) and friends create buckets. Define week start (Mon vs Sun), timezone (store UTC, report in product TZ), and whether partial buckets are included. Missing days need a date spine LEFT JOIN or your chart lies.

sql

1WITH days AS (2  SELECT generate_series(3    DATE '2026-01-01', DATE '2026-01-31', INTERVAL '1 day'4  )::date AS d5),6daily AS (7  SELECT created_at::date AS d, SUM(amount) AS gmv8  FROM orders9  WHERE status = 'paid'10  GROUP BY 111)12SELECT days.d, COALESCE(daily.gmv, 0) AS gmv13FROM days14LEFT JOIN daily ON daily.d = days.d15ORDER BY days.d;

01"GROUP BY rule?" → non-aggregated select cols must be group keys (or FD on them); when unsure, group them.
02"HAVING vs WHERE?" → WHERE row filters pre-agg; HAVING group filters post-agg.
03"Average of ratios?" → usually wrong; use sum(num)/sum(den) for overall rates.
04"COUNT DISTINCT nulls?" → nulls ignored in COUNT(DISTINCT col).
05"FILTER clause?" → COUNT(*) FILTER (WHERE cond) for partial aggregates in one pass.
06"Why date spine?" → zero-activity days must appear for time series correctness.
07"Weighted ASP?" → define weight: rows vs units vs revenue; AVG alone is row-weighted.
08"ROLLUP use?" → subtotals in one query; use GROUPING() to tell 'all' from NULL dimension.
09"Metric definition first?" → yes - paid vs all, tax in/out, refund policy - then code.
10"Double count in agg?" → still usually a join fan-out before the GROUP BY.

docsPostgreSQL aggregate functionsPostgreSQL articleMode - aggregate functionsMode docsPostgreSQL GROUPING SETSPostgreSQL docsUse The Index, Luke - GROUP BY performance notesUse The Index, Luke

Interview habit: before you write a join or window, say the grain of the result set out loud (one row per what?), name the filters that apply before aggregation, and sketch a 4-row toy table. That 30-second ritual catches double-counting and null traps faster than staring at syntax.

When stuck live, reduce to two tables and three rows on the whiteboard. Prove the join, then reintroduce filters and aggregates. Interviewers credit recovery more than a first-draft perfect query that you cannot explain.

State timezone and late-arriving data assumptions once. 'UTC calendar day of paid_at; events may land +2h' prevents half the metric arguments in marketplace and consumer analytics rounds.

Prefer COUNT(DISTINCT key) or EXISTS for existence metrics; prefer SUM of a measure that already lives at the target grain. If you must join a child table, aggregate the child in a CTE first and say why.

Read the English noun in the question: users, orders, sessions, days. That noun is usually the GROUP BY or partition key. If your SELECT invents a different noun, you probably changed the grain by accident.

Checkpoint

Overall conversion across 30 days with very different daily traffic - best CVR?

AAVG of per-day conversion ratesBSUM(conversions) / SUM(visits) over the same 30-day filtered rowsCMAX(daily CVR) as a optimistic bound

Checkpoint

COUNT(CASE WHEN status='paid' THEN 0 END) - what does it count?

ANumber of paid rows only, because THEN 0 marks unpaid as nullBNumber of paid rows (zeros are non-null so COUNT includes them) - misleading pattern; use THEN 1 END insteadCAlways zero because COUNT ignores zeros like SUM might

Checkpoint

You SELECT user_id, country, COUNT(*) FROM orders JOIN users GROUP BY user_id - engine complains or is nondeterministic about country. Why?

ACOUNT(*) is not allowed with JOINBcountry is neither aggregated nor in GROUP BY (and may not be FD-proven on user_id in that query shape)CYou must use HAVING country IS NOT NULL

Checkpoint

Daily revenue chart shows gaps on quiet days. Interviewer wants zeros. Fix?

AORDER BY date ASC so the UI interpolatesBGenerate a date spine and LEFT JOIN aggregated daily revenue with COALESCE(gmv,0)CUNION ALL a row of zeros once at the end

Checkpoint

Metric: 'paying users' = users with at least one paid order in-period. Best expression?

ACOUNT(user_id) FROM orders WHERE status='paid'BCOUNT(DISTINCT user_id) FROM orders WHERE status='paid' AND created_at in periodCSUM(status='paid') over users without distinct

Can you define a metric, pick grain, write GROUP BY/HAVING/FILTER, and avoid average-of-averages mistakes live?

New to itGetting thereConfident

Takeaways

Restate metric definition and grain before aggregating.
GROUP BY must cover non-aggregated select expressions.
Prefer FILTER/CASE aggregates; avoid COUNT(CASE THEN 0).
Overall rates = sum/sum, not avg of rates.
Date spines for zero-filled time series.

Next: window functions - ranking, running totals, and framing traps that separate seniors.

Sources

Free to read · better with Enzo

Learn it with Enzo

Save your progress, answer the checkpoints, and let Enzo quiz you on what you just read.

Continue with Enzo Sign up free