An analyst uses a small, specific subset of SQL very heavily. This lesson is that subset, plus the four places it quietly gives the wrong answer.
The shape
Every query runs its clauses in this order, which is not the order you write them:
-- Written in this order:
SELECT region, COUNT(*) AS orders, SUM(revenue) AS revenue
FROM orders
WHERE status = 'complete'
GROUP BY region
HAVING SUM(revenue) > 50
ORDER BY revenue DESC
LIMIT 10;
Executed as: FROM (get the rows), WHERE (filter them), GROUP BY (collapse them), HAVING (filter the groups), SELECT (compute the output), ORDER BY, LIMIT.
Two consequences follow from that order and both catch people out.
WHERE filters rows, HAVING filters groups. WHERE cannot see an aggregate because the grouping has not happened yet; HAVING can.
An alias defined in SELECT cannot be used in WHERE, because SELECT runs later. Most databases do let you use it in ORDER BY, which runs later still.
-- WHERE filters before grouping; HAVING after.
SELECT region,
COUNT(*) AS orders,
SUM(revenue) AS revenue
FROM orders
WHERE placed_on >= '2026-01-01'
GROUP BY region
HAVING COUNT(*) >= 1
ORDER BY revenue DESC;
Counting things carefully
SELECT COUNT(*) AS rows_total,
COUNT(region) AS rows_with_region,
COUNT(DISTINCT region) AS distinct_regions,
COUNT(DISTINCT user_id) AS distinct_users
FROM orders;
Three different counts, and choosing the wrong one is the commonest reporting error there is:
COUNT(*)counts rows, including rows where every column is null.COUNT(column)counts rows where that column is not null. The difference between it andCOUNT(*)is your missing-value count, for free.COUNT(DISTINCT column)counts distinct non-null values.
"How many customers ordered" is COUNT(DISTINCT user_id), never COUNT(*) - and after a join it is never COUNT(*) even if it was before.
Conditional counting, without three separate queries:
SELECT region,
COUNT(*) AS all_orders,
SUM(CASE WHEN status = 'complete' THEN 1 ELSE 0 END) AS completed,
SUM(CASE WHEN status = 'cancelled' THEN 1 ELSE 0 END) AS cancelled,
ROUND(
100.0 * SUM(CASE WHEN status = 'complete' THEN 1 ELSE 0 END) / COUNT(*),
1) AS completion_pct
FROM orders
GROUP BY region
ORDER BY all_orders DESC;
SUM(CASE WHEN ... THEN 1 ELSE 0 END) is the portable form and works everywhere. Note the 100.0 rather than 100: integer division is a silent source of zeros in several databases.
The LEFT JOIN that becomes an INNER JOIN
-- A LEFT JOIN keeps every user, even those with no orders.
SELECT u.user_id,
u.country,
COUNT(o.order_id) AS orders
FROM users u
LEFT JOIN orders o ON o.user_id = u.user_id
GROUP BY u.user_id, u.country
ORDER BY u.user_id;
Now put a condition on the right-hand table in the WHERE clause:
-- This LOOKS like a LEFT JOIN and behaves like an INNER one: a user
-- with no orders has NULL in o.status, and NULL = 'complete' is not
-- true, so the row is filtered out again.
SELECT u.user_id,
COUNT(o.order_id) AS orders
FROM users u
LEFT JOIN orders o ON o.user_id = u.user_id
WHERE o.status = 'complete'
GROUP BY u.user_id
ORDER BY u.user_id;
The condition belongs in the ON clause, where it filters what is joined rather than what survives:
SELECT u.user_id,
COUNT(o.order_id) AS complete_orders
FROM users u
LEFT JOIN orders o
ON o.user_id = u.user_id
AND o.status = 'complete'
GROUP BY u.user_id
ORDER BY u.user_id;
The rule: a condition on the right-hand table of a LEFT JOIN goes in ON, not WHERE. The exception is WHERE o.id IS NULL, which is the idiom for "rows on the left with no match".
The other join hazard is multiplication:
-- If a user has three sessions, their single order row is repeated
-- three times, and SUM(o.revenue) is three times too big.
SELECT u.user_id,
COUNT(*) AS joined_rows,
COUNT(DISTINCT o.order_id) AS real_orders,
SUM(o.revenue) AS inflated_revenue
FROM users u
LEFT JOIN orders o ON o.user_id = u.user_id
LEFT JOIN sessions s ON s.user_id = u.user_id
GROUP BY u.user_id
ORDER BY u.user_id;
Joining two one-to-many tables to the same parent multiplies them together. Aggregate each side separately first - in a CTE - and join the summaries.
Window functions
A window function computes across a set of rows without collapsing them, which is the thing GROUP BY cannot do.
SELECT order_id,
region,
revenue,
SUM(revenue) OVER (PARTITION BY region) AS region_total,
ROUND(100.0 * revenue / SUM(revenue) OVER (PARTITION BY region), 1)
AS pct_of_region,
ROW_NUMBER() OVER (PARTITION BY region ORDER BY revenue DESC) AS rank_in_region,
revenue - LAG(revenue) OVER (PARTITION BY region ORDER BY placed_on)
AS change_from_last
FROM orders
WHERE status = 'complete'
ORDER BY region, rank_in_region;
Four patterns cover most analyst use:
SUM(x) OVER (PARTITION BY g)- the group total, on every row. This is the "share of total" calculation with no join.ROW_NUMBER() OVER (PARTITION BY g ORDER BY x DESC)- rank within group, for top-n-per-group.LAG(x) OVER (ORDER BY t)- the previous row's value, for period-on-period change.SUM(x) OVER (ORDER BY t ROWS BETWEEN 6 PRECEDING AND CURRENT ROW)- a rolling window.
The ranking functions differ on ties and it matters:
SELECT region,
revenue,
ROW_NUMBER() OVER (PARTITION BY region ORDER BY revenue DESC) AS row_number,
RANK() OVER (PARTITION BY region ORDER BY revenue DESC) AS rank,
DENSE_RANK() OVER (PARTITION BY region ORDER BY revenue DESC) AS dense_rank
FROM orders
ORDER BY region, revenue DESC;
ROW_NUMBER gives 1, 2, 3 and breaks ties arbitrarily. RANK gives 1, 1, 3. DENSE_RANK gives 1, 1, 2. For "top 3 per group" you want ROW_NUMBER, because the others can return four rows.
A rolling total:
SELECT placed_on,
revenue,
SUM(revenue) OVER (ORDER BY placed_on
ROWS BETWEEN 2 PRECEDING AND CURRENT ROW) AS rolling_3,
SUM(revenue) OVER (ORDER BY placed_on) AS running_total
FROM orders
ORDER BY placed_on;
Note that SUM(x) OVER (ORDER BY t) with no frame clause is a running total, not a total - the default frame is everything up to the current row. Leaving out ORDER BY gives the whole-partition total. That difference is a real source of wrong numbers.
Dates, carefully
-- Comparing a timestamp to a date: anything after midnight on the
-- last day is excluded, because '2026-03-31' means 00:00:00.
SELECT COUNT(*) AS wrong
FROM events
WHERE occurred_at BETWEEN '2026-01-01' AND '2026-03-31';
-- Half-open intervals are right, every time: >= the start, < the day
-- after the end. No BETWEEN, no missing last day, no leap-second care.
SELECT COUNT(*) AS correct
FROM events
WHERE occurred_at >= '2026-01-01'
AND occurred_at < '2026-04-01';
Use half-open ranges for anything with a time component. BETWEEN on timestamps silently drops the last day, and it is the single most common date bug in analytics SQL.
Grouping by period, portably:
SELECT substr(occurred_at, 1, 7) AS month,
COUNT(*) AS events,
COUNT(DISTINCT user_id) AS users
FROM events
GROUP BY substr(occurred_at, 1, 7)
ORDER BY month;
Each database has its own function for this - DATE_TRUNC('month', ts) in PostgreSQL, DATE_FORMAT(ts, '%Y-%m') in MySQL, FORMAT_DATE in BigQuery. They do the same thing; know which one your warehouse uses, and never group by a raw timestamp.
Common table expressions, for readability
WITH complete_orders AS (
SELECT user_id, order_id, region, revenue
FROM orders
WHERE status = 'complete'
),
per_user AS (
SELECT user_id,
COUNT(*) AS orders,
SUM(revenue) AS revenue
FROM complete_orders
GROUP BY user_id
),
per_user_sessions AS (
SELECT user_id, COUNT(*) AS sessions, SUM(seconds) AS seconds
FROM sessions
GROUP BY user_id
)
SELECT u.user_id,
u.country,
COALESCE(p.orders, 0) AS orders,
COALESCE(p.revenue, 0) AS revenue,
COALESCE(s.sessions, 0) AS sessions
FROM users u
LEFT JOIN per_user p ON p.user_id = u.user_id
LEFT JOIN per_user_sessions s ON s.user_id = u.user_id
ORDER BY revenue DESC, u.user_id;
That is the shape to reach for whenever two one-to-many tables are involved: aggregate each to one row per key in its own CTE, then join the summaries. It cannot multiply, it reads top to bottom, and each CTE can be selected from on its own while debugging.
COALESCE turns the nulls a LEFT JOIN produces into zeros, which is almost always what a report wants - a user with no orders has zero revenue, not unknown revenue.
NULL does not behave like a value
-- NULL compared to anything, including NULL, is not true.
SELECT COUNT(*) AS rows_total,
SUM(CASE WHEN region IS NULL THEN 1 ELSE 0 END) AS null_region,
SUM(CASE WHEN region = 'North' THEN 1 ELSE 0 END) AS north,
SUM(CASE WHEN region <> 'North' THEN 1 ELSE 0 END) AS not_north
FROM orders;
north + not_north does not equal rows_total when any region is null, because NULL <> 'North' is neither true nor false. Use IS NULL, IS NOT NULL and COALESCE, and check that your partitions add up.
NOT IN with a null in the subquery returns no rows at all:
-- If any user_id in the subquery is NULL, this returns nothing.
SELECT COUNT(*) AS surviving
FROM users
WHERE user_id NOT IN (SELECT user_id FROM orders WHERE user_id IS NOT NULL);
Prefer NOT EXISTS, which has no such trap:
SELECT COUNT(*) AS users_with_no_orders
FROM users u
WHERE NOT EXISTS (SELECT 1 FROM orders o WHERE o.user_id = u.user_id);
A worked example
-- One query answering a real question: for each country, how many
-- users ordered, what they spent, and what share of revenue came
-- from the top order in that country.
WITH complete_orders AS (
SELECT o.order_id, o.user_id, o.revenue, u.country
FROM orders o
JOIN users u ON u.user_id = o.user_id
WHERE o.status = 'complete'
AND o.placed_on >= '2026-01-01'
AND o.placed_on < '2026-04-01'
),
ranked AS (
SELECT country,
order_id,
user_id,
revenue,
SUM(revenue) OVER (PARTITION BY country) AS country_revenue,
ROW_NUMBER() OVER (PARTITION BY country ORDER BY revenue DESC) AS rank_in_country
FROM complete_orders
),
country_totals AS (
SELECT country,
COUNT(*) AS orders,
COUNT(DISTINCT user_id) AS customers,
SUM(revenue) AS revenue,
MAX(CASE WHEN rank_in_country = 1 THEN revenue END) AS biggest_order
FROM ranked
GROUP BY country
)
SELECT c.country,
c.customers,
c.orders,
ROUND(c.revenue, 2) AS revenue,
ROUND(c.revenue / c.customers, 2) AS revenue_per_customer,
ROUND(100.0 * c.biggest_order / c.revenue, 1) AS pct_from_biggest_order
FROM country_totals c
ORDER BY c.revenue DESC;
Five techniques are in that one query. The date range is half-open. The filter on status is in the CTE rather than repeated. COUNT(DISTINCT user_id) counts customers rather than rows. A window function computes the country total without a second pass. And pct_from_biggest_order is a concentration check: a country where one order is 80% of revenue is a country whose "revenue per customer" means nothing.
When it goes wrong
| Symptom | Cause |
|---|---|
| A LEFT JOIN dropped rows | A condition on the right table in WHERE |
| The total doubled after a join | Two one-to-many joins; aggregate in CTEs first |
| The last day is missing | BETWEEN on a timestamp; use a half-open range |
| A percentage is always 0 | Integer division; multiply by 100.0 |
NOT IN returned nothing |
A NULL in the subquery; use NOT EXISTS |
| Counts do not add up to the total | NULLs excluded by both = and <> |
COUNT(*) over-counted customers |
Use COUNT(DISTINCT user_id) |
A window SUM gave a running total |
ORDER BY with no frame defaults to cumulative |
A check you can run
Take any query of yours with a JOIN in it and run SELECT COUNT(*) on the left table alone, then on the joined result.
If the second is larger, the join multiplied rows, and every SUM in that query is too big. It is a ten-second check, it is the most common wrong number in analytics, and the fix - aggregate each side in its own CTE first - is mechanical.