Five of the aggregation recipes cover most of the reporting work. You bucket by the time, you build a leaderboard, you split a total with the conditional aggregation, you adjust for the missing data, and you express a share of the whole. Each one of them is a GROUP BY with a particular shape, and once you recognise the shapes you can stop searching for the syntax and start composing the queries.
The previous lesson in this module covered "How Do Multi-Column Groups Answer Layered Questions?", and this lesson is the last one in the SQL GROUP BY and Aggregate Functions module.
Which aggregation recipes solve the reporting tasks?
The rental book is one flat table, and every report in this lesson comes from it with no joins at all. That is the point of the recipes. They are the patterns and not the one-off queries, and each pattern will reappear in every dataset that you meet.
Recipe one: bucket the revenue by the time
Any question about "this month" starts by extracting a period key from a date and grouping on it. The date functions from module 03 do the extraction, and the GROUP BY does the collapsing.
SELECT strftime('%Y-%m', returned_on) AS month,
COUNT(*) AS rentals,
ROUND(SUM(rental_days * daily_rate), 2) AS revenue
FROM rentals
WHERE returned_on IS NOT NULL
GROUP BY month
ORDER BY month;month rentals revenue
------- ------- --------
2025-04 10 1506.0
2025-05 9 1700.5The WHERE clause is doing quiet work. The rentals that were cancelled or that are still out have no return date, and their NULL would land in a NULL month bucket. Five of the rows are excluded by that filter, and the monthly totals cover only the nineteen completed hires.
A quarter or a week is the same recipe with a different format string, and a rolling window needs a different tool entirely. That is module 11.
Recipe two: rank the entities on a measure
A leaderboard is a grouped measure that is sorted descending and capped. The cap is what makes it a leaderboard rather than a report.
SELECT customer, COUNT(*) AS rentals,
ROUND(SUM(rental_days * daily_rate), 2) AS revenue
FROM rentals
WHERE status = 'completed'
GROUP BY customer
ORDER BY revenue DESC
LIMIT 3;customer rentals revenue
------------ ------- --------
Yusuf Kaya 2 540.0
Callum Reid 2 360.0
Aisha Rahman 2 293.0Yusuf Kaya took the two e-bikes in Cardiff and he paid the 540 pounds, and all three of the leaders rented twice. That last detail is why the count column belongs next to the revenue column. Without it, a customer who made the one expensive hire looks identical to a loyal one.
For a ranked list that shows the "top N" per group rather than globally, the technique changes. That is what the window functions are for, in module 11.
Recipe three: split one total into several
The conditional aggregation uses a CASE expression inside an aggregate to route the rows into the different buckets. The trick is that the conditional column is not a separate column in the table. It is computed on the fly.
SELECT city,
ROUND(SUM(rental_days * daily_rate), 2) AS gross,
ROUND(SUM(CASE WHEN status = 'cancelled' THEN 0 ELSE rental_days * daily_rate END), 2) AS net,
SUM(CASE WHEN status = 'cancelled' THEN 1 ELSE 0 END) AS cancelled
FROM rentals
GROUP BY city
ORDER BY city;city gross net cancelled
------- ------ ------ ---------
Bristol 1566.5 1566.5 0
Cardiff 1400.0 1233.0 2
Glasgow 1130.0 1130.0 0The gross figure of Cardiff includes the 167 pounds of the cancelled hires. Setting them to the zero inside the SUM produces the net figure without a second scan of the table.
The cancelled count uses the same shape with a different CASE, and that is the general pattern. The SUM(CASE WHEN condition THEN 1 ELSE 0 END) counts the matching rows, and the AVG(CASE WHEN condition THEN 1.0 ELSE 0.0 END) gives their share.
Recipe four: correct for the missing values
There are two rentals with no discount recorded, and there are five with no return date. Whether that is "no discount" or "unknown discount" changes the answer, so you state the assumption explicitly with the COALESCE rather than letting the default NULL behaviour decide it for you.
SELECT city,
ROUND(SUM(rental_days * daily_rate), 2) AS gross,
ROUND(SUM(rental_days * daily_rate * (1 - COALESCE(discount_pct, 0) / 100.0)), 2) AS net_of_discount,
ROUND(AVG(COALESCE(discount_pct, 0)), 2) AS avg_discount
FROM rentals
GROUP BY city
ORDER BY city;city gross net_of_discount avg_discount
------- ------ --------------- -------------
Bristol 1566.5 1482.8 4.44
Cardiff 1400.0 1333.55 4.38
Glasgow 1130.0 1011.75 8.57Glasgow gives away nearly twice as much discount as Cardiff does, which a gross-only report would have hidden completely. The same distinction shows up in the average.
SELECT COUNT(discount_pct) AS rows_with_discount,
ROUND(AVG(discount_pct), 2) AS avg_discount_recorded,
ROUND(SUM(COALESCE(discount_pct, 0)) * 1.0 / COUNT(*), 2) AS avg_discount_all_rows
FROM rentals;rows_with_discount avg_discount_recorded avg_discount_all_rows
------------------ --------------------- -----------------------
22 6.14 5.63The AVG skips the two NULL rows and it reports the 6.14 percent across the twenty-two rows that recorded one. The second column treats the missing rows as the zero discounts, and it reports the 5.63 across all twenty-four. Neither of them is wrong. They answer the different questions. But a report must say which one of them it is using.
Recipe five: express a share of the whole
A share needs a numerator and a denominator, and the denominator is the total across all of the groups. A scalar subquery supplies it, and that is the shape that module 06 formalises.
SELECT city,
COUNT(DISTINCT customer) AS customers,
ROUND(100.0 * COUNT(DISTINCT customer) / (SELECT COUNT(DISTINCT customer) FROM rentals), 1) AS pct_of_customers
FROM rentals
GROUP BY city
ORDER BY customers DESC, city;city customers pct_of_customers
------- --------- -----------------
Bristol 8 44.4
Cardiff 7 38.9
Glasgow 6 33.3Bristol accounts for the 44.4 percent of the eighteen distinct customers, and the three percentages add to the 116.6. That is because the customers appear in more than one city. It is not a bug. A share-of-customers metric counts the memberships and not the people.
When the two of them must reconcile, you count the distinct customers across the whole book for the denominator instead.
Quick reference
- The time buckets extract a period key, filter out the NULL dates, and group on the key.
- The leaderboards group by the entity, order by the measure, and LIMIT to the count that you want shown.
- The conditional aggregation, which is the SUM(CASE WHEN ... THEN value ELSE 0 END), splits one total into several.
- The missing values need you to state the assumption with the COALESCE, because the AVG and the SUM ignore the NULLs silently.
- The shares need a whole-table denominator, and a scalar subquery supplies it.
Recipe drill: using the rentals table, you write three of these recipes. They are the revenue per month, the top-five leaderboard by the revenue, and the net revenue per city after you exclude the cancellations. Then you extend the third one with a column for the share of each city's net revenue.