Most data analysis starts with SQL. Five patterns cover most of what you'll need.
5 minute read
In short
JOIN plus GROUP BY answers most 'per something' questions.
LEFT JOIN with IS NULL finds missing matches; HAVING filters after grouping.
Window functions compute running totals and rankings without collapsing rows.
The data
orders: id
user_id
amount
created
1
1
500
2026-10-01
2
1
700
2026-10-15
3
2
300
2026-10-03
4
3
1200
2026-10-20
5
2
800
2026-11-02
6
4
200
2026-11-05
users: id
city
1
Hyderabad
2
Pune
3
Hyderabad
4
Chennai
5
Pune
Five patterns
1. Join and group: revenue by city
SELECT u.city, COUNT(*) AS orders, SUM(o.amount) AS revenue
FROM orders o
JOIN users u ON u.id = o.user_id
GROUP BY u.city
ORDER BY revenue DESC;
-- Hyderabad | 3 | 2400
-- Pune | 2 | 1100
-- Chennai | 1 | 200
2. LEFT JOIN to find what's missing: users with no orders
SELECT u.id, u.city
FROM users u
LEFT JOIN orders o ON o.user_id = u.id
WHERE o.id IS NULL;
-- 5 | Pune
3. HAVING filters groups: users who spent over 1,000
SELECT user_id, SUM(amount) AS total
FROM orders
GROUP BY user_id
HAVING SUM(amount) > 1000;
-- 1 | 1200
-- 2 | 1100
-- 3 | 1200
4. Window function: running total per user
SELECT user_id, amount,
SUM(amount) OVER (PARTITION BY user_id ORDER BY created) AS running
FROM orders
ORDER BY user_id, created;
-- 1 | 500 | 500
-- 1 | 700 | 1200
-- 2 | 300 | 300
-- 2 | 800 | 1100
-- 3 | 1200 | 1200
-- 4 | 200 | 200
5. Counting rows after a join: an inner join of users and orders gives 6 rows (one per order). A left join gives 7, because user 5 appears once with empty order columns. Many wrong results come from joins quietly duplicating or dropping rows.
Your turn
What's the difference between WHERE and HAVING?
Habits that prevent mistakes
Say what each row of the result represents before writing ('one row per city'). Check edge cases: users with no orders, ties, NULLs. Window functions such as ROW_NUMBER, RANK and running SUM come up in nearly every analysis; practise them until they're automatic.
Common mistakes
Using LIMIT 3 on the whole result, which gives the top 3 overall, not per city.
Ranking before aggregating, so each order is ranked instead of each customer.
Not deciding how to handle ties.
Check yourself
Answer in your own words, out loud or on paper, before you open the model answer.
Find the top 3 customers by revenue in each city.
I'd do it in steps. First, aggregate revenue per customer per city: join orders to users on user ID, group by city and user, and sum the amounts. Then, in a second step using a CTE, rank customers within each city with a window function, like ROW_NUMBER over a partition by city, ordered by revenue descending. Finally, select the rows where the rank is 3 or less. I'd mention ties: ROW_NUMBER picks an arbitrary order among equal revenues, RANK lets ties share a rank so a city might return more than three customers, and DENSE_RANK doesn't skip numbers. I'd ask which one the business wants, and check for users with missing cities.
More to think about
Find users who signed up but never placed an order.
Compute each user's running total of spending over time.