Level 7 · From question to checked answer

Every analysis follows roughly the same path, whatever the tool. Writing it down as stages makes the SQL easier to build, easier to review and much easier to defend when someone questions the number.

The stages

1. Understand the request. What decision does it feed? Here: staffing for returns. What exactly is counted? A return rate per category, as a share of order lines, for 2025.
2. Explore keys and grain. order_items has one row per line; returns has at most one row per order_item_id; products gives the category. The grain of the answer is one row per category.
3. Extract the rows in scope: lines of completed orders placed in 2025.
4. Clean: drop the lines with quantity of zero or below.
5. Aggregate to the requested grain.
6. Structure the steps as named CTEs so each one can be run and checked alone.
7. Analyze further if needed (ranks, shares, trends with window functions).
8. Validate: do the parts add up to the whole? Are row counts plausible at each step?
9. Present: a sorted table with a sentence of interpretation.

The query, stage by stage

The starter is stage 3. Running it alone returns 10,810 lines, the first sanity check. The cleaning CTE removes 10 lines with a bad quantity, leaving 10,800. The aggregate joins products and LEFT JOINs returns: a LEFT JOIN, because lines without a return must stay in the denominator, and COUNT(r.return_id) counts only the matched ones.

The validation is a column, not a separate memo:

SUM(lines) OVER () = (SELECT COUNT(*) FROM clean_lines) AS total_check

SUM(lines) OVER () adds the six category counts; the subquery counts the clean lines directly. If a product were missing a category, or a join duplicated lines, the two would differ and the column would say false.

The result: Textiles 4.2% (92 of 2,195 lines), Lighting 3.9%, Outdoor 3.7%, Furniture 3.6%, Decor 2.9% and Kitchen 2.0% (51 of 2,548). Furniture has the fewest lines (586), so its rate moves the most with a handful of returns; say so when you present it.

Validate before you present

Useful checks for almost any analysis:

• Row counts after every CTE, compared with what you expect from the data documentation.
• Parts versus whole, as above.
• One entity traced end to end: pick one order and confirm its lines and returns by hand.
• A comparison with a number someone already trusts, such as last month’s report.

Common mistakes

• Skipping stage 1 and answering a different question: returns per order instead of per line, or refund dollars instead of counts.
• INNER JOIN to returns, which makes every rate 100%.
• Including refunded orders, whose lines are all returned by definition, without saying so.
• Presenting a rate without its denominator.

In SQL Server

The CTE chain, LEFT JOIN and COUNT(column) are identical in T-SQL. Two differences: a boolean expression cannot be a column, so write the check as CASE WHEN SUM(lines) OVER () = (SELECT COUNT(*) FROM clean_lines) THEN 1 ELSE 0 END, and 100.0 * returned_lines / lines needs the 100.0, because / on two integers truncates in SQL Server.