Lesson 10 of 20
In progress
Reporting patterns that hold up
Percentages, conditional counts, month buckets and empty results: the four patterns behind most real reports.
A report is a query someone makes a decision with. That raises the bar: totals must reconcile, empty results must be explicit, and percentages must not silently divide by zero.
What you will learn
- Build conditional counts and percentages safely.
- Bucket dates into months.
- Make empty results visible instead of absent.
The dataset
Editable. Runs in your browser.
INSERT INTO payments (id, customer_id, method, amount_paise, status, paid_on) VALUES
(1, 1, 'card', 125000, 'captured', '2025-01-04'),
(2, 2, 'upi', 89000, 'captured', '2025-01-19'),
(3, 1, 'card', 240000, 'failed', '2025-02-02'),
(4, 3, 'upi', 45000, 'captured', '2025-02-02'),
(5, 1, 'card', 310000, 'captured', '2025-02-21'),
(6, 4, 'netbanking', 99000, 'captured', '2025-03-05'),
(7, 2, 'upi', 150000, 'failed', '2025-03-05');
Press Run to see the output.
Conditional counts
Editable. Runs in your browser.
Press Run to see the output.
Each SUM(CASE ...) is a count restricted to a condition. One pass over the table produces every column, and the numbers always add up to the total because they come from the same rows.
This is the alternative to running one query per status and hoping the totals match.
Percentages without dividing by zero
Editable. Runs in your browser.
Press Run to see the output.
Three details make this correct:
100.0forces decimal arithmetic;100with integer operands truncates.COUNT(*)is never zero inside a group, because a group exists only if it has rows.- Rounding happens once, at the end, on the final percentage.
If the denominator can be zero, wrap it: ... / NULLIF(COUNT(*), 0). NULL is a better answer than an error or a silent zero.
Month buckets
Editable. Runs in your browser.
Press Run to see the output.
ISO dates stored as text make SUBSTR(paid_on, 1, 7) a month key that sorts correctly. With a real date type, DATE_TRUNC('month', paid_on) or strftime('%Y-%m', paid_on) does the same job. The principle is to group by the bucket expression itself, not by the full date.
Filling gaps
A month with no payments is missing from the previous result. For a chart, gaps are usually wrong: the line should drop to zero.
There are two honest approaches:
- Generate the calendar in SQL with a recursive CTE and
LEFT JOINthe data onto it. - Fill the gaps in the application, where the full list of months is already known.
Filling in the application is simpler and usually right. The key is to state which you did, because "no data" and "not queried" look identical on a chart.
Totals and detail in one result
Editable. Runs in your browser.
Press Run to see the output.
UNION ALL stacks the detail and the total. It keeps duplicates, which matters here: the total row must survive. A plain UNION would deduplicate and, occasionally, remove a real row.
ORDER BY at the end applies to the combined result, not to each part.
Reconciling a report
A report is trustworthy when its parts add up. Two checks catch most mistakes:
- Sum the detail rows and compare with the grand total query.
- Run the same filters with
COUNT(*)and confirm the row count matches the sum of group counts.
Editable. Runs in your browser.
Press Run to see the output.
Both numbers should be equal. When they differ, a WHERE is in the wrong place or a join is multiplying rows.
Naming the columns
A report column name is part of the report. sum and count are meaningless three weeks later; captured_rupees and failed_attempts explain themselves in a CSV, a chart legend and a review comment. Include the unit in the name when it is not obvious, and avoid abbreviations that only the author understands. Spend the extra minute, because the name outlives the query.
Consistency matters as much as clarity: if one query calls the field revenue and another calls it amount, reconciling two reports becomes a guessing game. Good names also prevent a real bug, because a column called captured_rupees makes it obvious when someone has summed paise into it.
Writing SUM(amount) / COUNT(*) for an average. That is the average over all rows including failures and zeros, which is rarely "average payment". Use AVG(amount) over the filtered set, and say which set in the label.
Produce a per-month table of attempts and captured revenue in rupees, with a success percentage, for months with at least two attempts.
Show solution
Editable. Runs in your browser.
Press Run to see the output.
January has two attempts and both captured, so 100 percent. February has three attempts with two captured. March has two attempts, both failed, so zero percent but a real row — which is exactly why the percentage needs the decimal divisor and the report needs the row.
Recap
- Conditional aggregation counts subsets in one pass and keeps totals consistent.
- Use
100.0andNULLIFso percentages never truncate or divide by zero. - Group by a bucket expression, and be explicit about gaps and totals.
Module 3 moves from reading to joining tables and changing rows.
Should totals come from SQL or the application?
Is UNION ALL always faster than UNION?
Progress is saved in this browser only. No account, no email.