Beginner Open Free

The Revenue Number That Tripled Overnight

An analyst reports total revenue per country using the schema from the subject (users, events, orders):

SELECT u.country, SUM(o.amount) AS revenue, COUNT(e.event_id) AS events
FROM users  u
JOIN orders o ON o.user_id = u.user_id
JOIN events e ON e.user_id = u.user_id
WHERE o.status = 'paid'
GROUP BY u.country;

Finance says the total revenue is roughly 4× the number in the payments dashboard.

Explain precisely why the revenue is inflated, using a small concrete example (one user, a few orders, a few events).
Rewrite the query so that both revenue and events are correct, and so that countries whose users have orders but no events still appear.
What quick sanity check would have caught this before the report was sent?

Solution

1. Why revenue is inflated — join fan-out

orders and events are both one-to-many children of users and are unrelated to each other. Joining both onto users produces the Cartesian product of a user's orders and events. Take user 7 with two paid orders (40 and 60, true revenue 100) and four events. The joined result has 2 × 4 = 8 rows: each order row appears four times, so SUM(o.amount) = 4 × 100 = 400, and each event appears twice, so COUNT(e.event_id) = 8 instead of 4. If the average user has ~4 events, revenue is inflated ~4× overall — matching Finance's observation. Nothing errors; the query is syntactically fine and semantically wrong.

2. Correct rewrite — aggregate each child to user grain, then join

WITH order_agg AS (
  SELECT user_id, SUM(amount) AS revenue
  FROM orders WHERE status = 'paid'
  GROUP BY user_id
), event_agg AS (
  SELECT user_id, COUNT(*) AS events
  FROM events
  GROUP BY user_id
)
SELECT u.country,
       COALESCE(SUM(oa.revenue), 0) AS revenue,
       COALESCE(SUM(ea.events), 0)  AS events
FROM users u
LEFT JOIN order_agg oa ON oa.user_id = u.user_id
LEFT JOIN event_agg ea ON ea.user_id = u.user_id
GROUP BY u.country;

Each CTE has exactly one row per user, so joining them to users cannot multiply rows. LEFT JOIN keeps users with orders but no events (and vice versa); COALESCE turns the resulting NULL sums into 0. Note the status = 'paid' filter moved into the order CTE — leaving it in the outer WHERE after a LEFT JOIN would silently drop users with no paid orders from the country totals.

3. Sanity check

Compare row counts before and after the join: SELECT COUNT(*) FROM orders WHERE status = 'paid' versus the row count of the joined set before grouping. If the join multiplies rows and you are about to SUM, it is wrong. Equivalently, reconcile one metric against a known source (SUM(amount) straight from orders) — the totals must match exactly. Asking "what is one row in this result?" for every join is the habit that prevents the bug.

Share this question

Share on X Share on LinkedIn

← Back to SQL for Data Analysis practice