Lesson 3.2: SQL for analysis: grouping, joins and window functions
Learn the SQL an analyst uses daily: filtering, grouping, joins that do not double count, and window functions for rankings and running totals.
By the end of this lesson you will be able to write the queries that make up most of an analyst's day: filter and sort rows, summarise them with GROUP BY, combine tables with joins without double counting, label rows with CASE, and use window functions for rankings, running totals and comparisons with the previous row. You will work through every query on two small tables, so you can predict each result by hand before you check it. You should have read Lessons 1.1 and 3.1, since SQL assumes you know what a row and a grain are, and the first job of any query is to protect the grain. No database is needed to follow along, although a free tool such as SQLite will let you run everything.
Most business data lives in databases, not spreadsheets. A loan system, a payments platform and a customer app each keep several linked tables, and the people who can ask questions of them directly, without waiting for someone else's extract, are far more effective. SQL, which stands for Structured Query Language, is the standard way to do it. It is declarative: you describe the result you want, and the database works out how to produce it. That makes it readable once you know the pattern.
Two small tables
Everything in this lesson runs on two tables. The first, customers, has five rows.
customerid · name · city1 · Asha · Pune
2 · Ben · Mumbai
3 · Chitra · Pune
4 · Dev · Nagpur
5 · Esha · Mumbai
The second, loans, has six rows. Each loan belongs to one customer, shown by customerid, which is a foreign key: a column that points to the key of another table.
loanid · customerid · amount · statusL1 · 1 · 300000 · Closed
L2 · 1 · 150000 · Active
L3 · 2 · 500000 · Active
L4 · 3 · 200000 · Defaulted
L5 · 3 · 120000 · Active
L6 · 5 · 700000 · Active
Notice that Dev has no loans, and that Asha and Chitra have two each. The grain of customers is one row per customer, and of loans one row per loan. Keep those two sentences in mind, because the commonest SQL errors come from forgetting them.
The shape of a query
A query is built from clauses written in a fixed order, although the database does its work in a different order.
sql
SELECT columns or calculations
FROM table
WHERE filter on individual rows
GROUP BY columns to summarise by
HAVING filter on groups
ORDER BY sort order
LIMIT number of rows to return;
The order of execution is FROM first, then WHERE, then GROUP BY, then HAVING, then SELECT, then ORDER BY. This explains rules that otherwise seem arbitrary. You cannot use a name you create in SELECT inside WHERE, because WHERE runs before SELECT. You cannot filter on a total in WHERE, because the totals do not exist until GROUP BY has run.
Predict first: what will this query return, and in what order?
sql
SELECT loanid, amount
FROM loans
WHERE status = 'Active' AND amount >= 150000
ORDER BY amount DESC;
The WHERE clause keeps the four active loans, L2, L3, L5 and L6, and then the amount filter removes L5 at 120,000. The remaining three sorted from largest to smallest are L6 at 700,000, L3 at 500,000 and L2 at 150,000. The ORDER BY ... DESC sorts descending, and without it the database makes no promise about order.
Summarising with GROUP BY
An aggregate function collapses many rows into one value: COUNT, SUM, AVG, MIN and MAX. With GROUP BY you get one result row per group. To count customers by city:
sql
SELECT city, COUNT() AS customers
FROM customers
GROUP BY city;
The result is Pune 2, Mumbai 2 and Nagpur 1. Every column in SELECT must either appear in GROUP BY or be inside an aggregate. This keeps the grain clear: after grouping, one row stands for one city.
To compute the total, average and largest loan for each status:
sql
SELECT status,
COUNT() AS loans,
SUM(amount) AS total,
AVG(amount) AS average
FROM loans
GROUP BY status;
For Active there are four loans, a total of 1,470,000 and an average of 367,500. For Closed there is one loan of 300,000, and for Defaulted one of 200,000. The six loans add up to 1,970,000, which is the check to make after any grouping: the group totals must equal the overall total.
WHERE or HAVING?
Both filter, but at different stages. WHERE filters rows before grouping. HAVING filters groups after the aggregates exist. To find customers with more than one loan:
sql
SELECT customerid, COUNT() AS loans, SUM(amount) AS total
FROM loans
GROUP BY customerid
HAVING COUNT() > 1;
The result is customer 1 with 2 loans totalling 450,000 and customer 3 with 2 loans totalling 320,000. If you had written the condition in WHERE, the database would have rejected it, because the count does not exist at that stage.
· WHERE · HAVINGFilters · Individual rows · Groups
Runs · Before GROUP BY · After GROUP BY
Can use aggregates · No · Yes
Example · status = 'Active' · COUNT() > 1
## Counting and NULL
Missing values appear in SQL as NULL, and NULL behaves differently from every other value. It means "unknown", so any comparison with it is itself unknown. amount = NULL is never true, even for a row whose amount is NULL. To test for it, write amount IS NULL or amount IS NOT NULL. Aggregates quietly skip NULL: COUNT(amount) counts only the non-NULL amounts, while COUNT() counts rows. If a column has blanks, the two numbers differ, and using the wrong one is a classic reporting error. COALESCE(amount, 0) replaces a NULL with a default, but remember Lesson 1.2: a blank is not a zero, so replace only when you know it is the right meaning.
Joins: combining tables
A join puts rows from two tables side by side, matching on a key. The type of join decides what happens to rows without a match.
An inner join keeps only rows that match in both tables. To attach each loan to its customer's name and city:
sql
SELECT l.loanid, c.name, c.city, l.amount
FROM loans AS l
INNER JOIN customers AS c ON c.customerid = l.customerid;
The result has six rows, one per loan, and Dev does not appear because he has no loans. A left join keeps every row of the left table and fills in NULL where the right table has no match. Starting from customers:
sql
SELECT c.name, l.loanid, l.amount
FROM customers AS c
LEFT JOIN loans AS l ON l.customerid = c.customerid;
Now there are seven rows: the six matched pairs, plus Dev with NULL for the loan columns. A small variation finds customers who have no loans, called an anti-join:
sql
SELECT c.name
FROM customers AS c
LEFT JOIN loans AS l ON l.customerid = c.customerid
WHERE l.loanid IS NULL;
It returns Dev alone. The pattern is useful constantly: customers who never bought, accounts with no payment, users who did not activate.
Join · Keeps · Use it whenINNER JOIN · Rows that match in both tables · You only care about matched pairs
LEFT JOIN · All rows from the left table, matches from the right · You must not lose the left-hand rows, such as all customers
FULL OUTER JOIN · All rows from both · You are reconciling two lists
CROSS JOIN · Every combination of rows · You want all pairs, for example every product with every month
One more trap with left joins. If you filter the right-hand table in the WHERE clause, you throw away the NULL rows and silently turn the left join back into an inner join. To keep the customers with no matching loan, put the condition in the ON clause instead, or test for NULL explicitly.
The danger of a join: fan-out
A join changes the grain. If you join a table with one row per loan to a table with many rows per loan, such as monthly payments, the result has one row per payment, and any loan-level column is repeated once for each payment. Adding up the loan amount now counts each loan many times. This is called fan-out, and it is the single most common cause of totals that are suddenly too big.
Suppose each loan has twelve payment rows. Join loans to payments and SUM(amount) returns twelve times the true total. With the six sample loans the true total is 1,970,000, and after the join it would be 23,640,000. The rule is: before you aggregate, know the grain of the joined result. Either aggregate each table to the grain you need first and then join, or count distinct keys.
A habit that saves hours: after every join, compare the row count with what you expected and check a handful of keys by hand. If you joined one row per loan to a table that should have one row per loan and the count went up, something repeats.
CASE: labelling and conditional totals
CASE WHEN is SQL's if statement. It creates a label or a conditional number, and it is how you build segments and flags.
sql
SELECT
COUNT() AS loans,
SUM(CASE WHEN status = 'Defaulted' THEN 1 ELSE 0 END) AS defaulted,
SUM(CASE WHEN status = 'Defaulted' THEN amount ELSE 0 END) AS defaultedamount,
SUM(amount) AS totalamount
FROM loans;
The result is 6 loans, 1 defaulted, 200,000 of defaulted amount and 1,970,000 in all. The default rate by count is 1 ÷ 6 = 16.7 per cent, and by amount 200,000 ÷ 1,970,000 = 10.2 per cent. The two differ because the defaulted loan is smaller than average, a reminder from Lesson 1.3 that a rate depends on what you count. Joined to customers, the same pattern gives default rates by city. Putting a CASE inside an aggregate is the standard way to compute several slices in one pass.
The query that produced that chart is a left join with a group, and it needs COALESCE to show Nagpur as zero.
sql
SELECT c.city, COALESCE(SUM(l.amount), 0) AS total
FROM customers AS c
LEFT JOIN loans AS l ON l.customerid = c.customerid
GROUP BY c.city
ORDER BY total DESC;
Subqueries and common table expressions
Sometimes a query needs the result of another query. A common table expression, or CTE, written with WITH, gives a name to an intermediate result so the main query reads top to bottom. It is the cleanest way to aggregate before joining and so avoid fan-out.
sql
WITH percustomer AS (
SELECT customerid, COUNT() AS loans, SUM(amount) AS total
FROM loans
GROUP BY customerid
)
SELECT c.name, c.city, COALESCE(p.loans, 0) AS loans, COALESCE(p.total, 0) AS total
FROM customers AS c
LEFT JOIN percustomer AS p ON p.customerid = c.customerid;
The CTE reduces loans to one row per customer first, so the join cannot multiply rows. The result has five rows, one per customer, with Dev showing zero loans and zero total. Readers can check the CTE alone, which makes debugging far easier than a single long query.
Window functions
A window function computes a value for each row using other, related rows, without collapsing them. GROUP BY turns many rows into one. A window function keeps every row and adds a calculated column. The related rows are defined by OVER (PARTITION BY ... ORDER BY ...): PARTITION BY splits the rows into groups, and ORDER BY orders them inside each group.
· GROUP BY · Window functionOutput rows · One per group · One per input row
Detail kept · No · Yes
Typical use · Totals and counts · Rankings, running totals, comparison with neighbours
### Running totals
To see how the portfolio builds up as loans are added in order:
sql
SELECT loanid, amount,
SUM(amount) OVER (ORDER BY loanid) AS runningtotal
FROM loans;
The running totals are 300,000, 450,000, 950,000, 1,150,000, 1,270,000 and 1,970,000. Each row shows its own amount and the sum up to and including it. The last value equals the overall total, which confirms the calculation.
Rankings
To rank loans by size within each city, join the tables and partition by city:
sql
SELECT c.city, l.loanid, l.amount,
RANK() OVER (PARTITION BY c.city ORDER BY l.amount DESC) AS rankincity
FROM loans AS l
JOIN customers AS c ON c.customerid = l.customerid;
In Pune the ranks are L1 at 300,000 as 1, L4 at 200,000 as 2, L2 at 150,000 as 3 and L5 at 120,000 as 4. In Mumbai, L6 at 700,000 is 1 and L3 at 500,000 is 2. Three ranking functions differ only in how they handle ties. ROWNUMBER() gives every row a unique number, RANK() gives tied rows the same rank and skips the next numbers, and DENSERANK() gives tied rows the same rank without skipping.
A very common use is "the latest row per key". To keep each customer's most recent loan, assuming a higher loan id is later:
sql
WITH ranked AS (
SELECT , ROWNUMBER() OVER (PARTITION BY customerid ORDER BY loanid DESC) AS rn
FROM loans
)
SELECT loanid, customerid, amount FROM ranked WHERE rn = 1;
It returns L2 for customer 1, L3 for customer 2, L5 for customer 3 and L6 for customer 5. The same pattern removes duplicates when a table has repeated records, which connects to Lesson 1.2.
Comparing with the previous row
LAG looks at an earlier row in the partition, and LEAD looks at a later one. To see how each customer's loan amount changed from their previous loan:
sql
SELECT customerid, loanid, amount,
amount - LAG(amount) OVER (PARTITION BY customerid ORDER BY loanid) AS change
FROM loans;
For customer 1, L1 has a change of NULL because there is no earlier loan, and L2 has a change of 150,000 − 300,000 = −150,000. For customer 3, L5 has a change of 120,000 − 200,000 = −80,000. Month-on-month growth, time since the last purchase and the gap between a customer's repayments are all LAG calculations.
Writing queries you can trust
A few habits turn SQL from something that merely runs into something you can rely on.
State the grain of the result before you write the query: "one row per customer", "one row per loan per month".
Select only the columns you need, and avoid SELECT in anything you keep, since tables change.
Check row counts after every join, and reconcile totals against a source you trust.
Test on a few keys you can verify by hand.
Use clear names and formatting: one clause per line, aliases that mean something, and comments for any non-obvious rule.
Watch dates and time zones. A timestamp near midnight can land in the wrong day if the database stores UTC and your business works in India Standard Time.
Avoid integer division. In many databases 1 / 6 is 0, so cast one side to a decimal before computing a rate.
You can run SQL from Python too, which is handy when the result feeds an analysis.
python
import sqlite3
import pandas as pd
con = sqlite3.connect("bank.db")
df = pd.readsqlquery(
"SELECT status, COUNT() AS loans, SUM(amount) AS total FROM loans GROUP BY status",
con,
)
Common mistakes
Joining without knowing the grain. A one-to-many join repeats rows, and sums are overstated. Aggregate first or count distinct keys.
Filtering a left join in WHERE. It turns the left join into an inner join and loses the unmatched rows.
Using = NULL. Use IS NULL. Remember that aggregates skip NULL.
Confusing COUNT() and COUNT(column). One counts rows, the other counts non-blank values.
Putting an aggregate in WHERE. Use HAVING for conditions on groups.
Integer division. Cast to a decimal before dividing to get a rate.
Trusting a result you did not reconcile. Compare totals with a known figure, and check row counts.
Over-long queries. Break them into CTEs so each step can be checked.
Key takeaways
A query runs FROM, WHERE, GROUP BY, HAVING, SELECT, ORDER BY, which explains where each condition belongs: WHERE filters rows and HAVING filters groups.
GROUP BY collapses rows into one per group. Always check that group totals add up to the overall total.
Joins combine tables. INNER keeps matches, LEFT keeps every left row, and an anti-join finds rows with no match.
A join can change the grain and multiply rows, which inflates sums. Know the grain, aggregate before joining and check row counts.
CASE WHEN creates labels and conditional totals, and COALESCE handles NULL when a default is the right meaning.
Window functions keep every row while computing running totals, ranks and comparisons with previous rows. Use ROWNUMBER for the latest row per key.
Check yourself
1. Which customers have no loans, and what pattern finds them?
Dev. Use a left join from customers to loans and keep rows where the loan id is NULL.
2. Loans are joined to monthly payments, 12 rows per loan, and the loan amounts are summed. The true total is ₹40 crore. What does the query return?
₹480 crore, twelve times too much, because each loan's amount is repeated once per payment row.
3. What is the difference between WHERE and HAVING?
WHERE filters rows before grouping and cannot use aggregates. HAVING filters groups after aggregation.
4. In the sample data, what are the running totals when loans are ordered by loanid?
300,000; 450,000; 950,000; 1,150,000; 1,270,000; 1,970,000.
5. How would you keep each customer's most recent loan?
Number each customer's loans with ROWNUMBER partitioned by customer and ordered by loan date descending, then keep rows where the number is 1.
Try this
You can use any SQL tool: SQLite in a browser, a database at work, or pandas with sqlite3. Set aside forty-five minutes.
1. Create the customers and loans tables from this lesson, or take two related tables you already have.
2. Write the query for total and count of loans by city, first with an inner join and then with a left join and COALESCE. Explain the difference in the row for Nagpur.
3. Find customers with more than one loan using HAVING, and customers with no loans using an anti-join.
4. Add a third table of payments with several rows per loan. Join it to loans, sum the loan amount, and observe the fan-out. Then fix it with a CTE that aggregates payments to one row per loan first.
5. Compute a running total of loan amounts and the rank of each loan within its city.
6. Write a LAG query for the change in amount between a customer's consecutive loans.
7. For each query, write one sentence stating the grain of the result, and reconcile a total against the source.
A good result is a short file of six or seven queries, each with a comment giving its grain and the check you made. In the next lesson you apply these queries to the questions product and credit teams ask most: how many people drop out at each step, how long they stay, and which groups behave alike.
Tags: Data Science SQL Joins Window functions Analytics