Level 6 · Boss: performance snapshot

This boss combines the whole chapter: aggregates over expressions, grouping, sorting grouped results, and filtering groups.

Step 1: top products by revenue

Compute revenue per line, sum per product, then rank:

SELECT product_id,
       SUM(quantity) AS units,
       ROUND(SUM(quantity * unit_price * (1 - discount_pct / 100.0)), 2) AS net_revenue
FROM order_items
WHERE quantity > 0
GROUP BY product_id
ORDER BY net_revenue DESC, product_id
LIMIT 10;

This includes every order status because the order status lives in a different table. In chapter 5 you will join orders and count only completed orders, which is what finance would usually want. When you hand this to Megan, say which version it is.

Step 2: returns by reason

Three aggregates side by side. A useful pattern when presenting: count, total and average together tell you whether a reason is expensive because it is frequent or because each return is large. “Damaged” returns might be fewer than “changed_mind” but cost more each, which points to a packaging problem rather than a product problem.

Step 3: conversion rate by source

converted is a boolean. To count the true values, turn it into 1 or 0 and sum:

SUM(CAST(converted AS INTEGER)) AS conversions

This works in Postgres, DuckDB and MySQL. BigQuery spells it CAST(converted AS INT64) or COUNTIF(converted). Postgres and DuckDB also support the standard COUNT(*) FILTER (WHERE converted), and every database supports SUM(CASE WHEN converted THEN 1 ELSE 0 END), which you will meet in chapter 6.

The conversion rate is conversions over sessions. Multiply by 100.0 before dividing, so integer division in Postgres cannot turn every rate into 0. Then HAVING COUNT(*) >= 5000 drops small sources, whose rates are too noisy to compare.

Small groups lie

Why filter out small groups? A source with 40 sessions and 4 conversions shows a 10% rate, which may look better than a source with 20,000 sessions at 3%. The small one is mostly noise. Analysts routinely set a minimum sample size with HAVING before ranking groups by a rate. Mention the threshold in your notes so nobody wonders where a source went.