The route

Three sources and an aggregate

Download the exercise files

Orders say who bought what, but not where the customer lives or what the product costs. This level joins all three files in one SQL node and turns 3,000 order lines into a revenue table by country and product category — the kind of summary someone will actually ask you for.

Goal

A pipeline that writes revenue_by_country_category.csv: one row per country and category, with the number of paid orders and their revenue.

You'll need

  • Level 3.2's files: customers.csv, orders.csv, products.csv.
  • About 25 minutes.

Steps

1. Three inputs

Create a pipeline w3-3-revenue with three CSV Source nodes and connect them to one SQL node named revenue_by_country_category in this order:

  1. customers → input1
  2. orders → input2
  3. products → input3

Three sources feeding one SQL node, and a CSV Write

Check Inputs in the SQL node's Properties to confirm the numbering before writing the query.

2. Join, filter, group

SELECT c.country, p.category,
       COUNT(*) AS orders,
       SUM(o.quantity * p.list_price) AS revenue
FROM input2 AS o              -- orders
JOIN input1 AS c ON c.customer_id = o.customer_id   -- customers
JOIN input3 AS p ON p.sku = o.sku                   -- products
WHERE o.status = 'paid'
GROUP BY c.country, p.category
ORDER BY revenue DESC

Read it from the FROM:

  • Start from the thing you're counting. One row per order line, so FROM the orders.
  • Join what describes it. Each order has exactly one customer and one product, so these joins never multiply rows — 2,682 paid orders stay 2,682 until the GROUP BY.
  • Filter before you aggregate. WHERE o.status = 'paid' drops the 318 refunded and pending orders before anything is summed.
  • Every column in SELECT is either grouped or aggregated. country and category are in the GROUP BY; orders and revenue are aggregates.

Preview Output on the SQL node:

Revenue by country and category, largest first

3. Write it out

Add a CSV Write named revenue_csv after the SQL node — Output Folder wherever you keep the course output, Filename revenue_by_country_category.csv — and Execute.

Expected result

  • 60 rows — 15 countries × 4 categories.
  • Top three: PL · services · 24 orders · 28454, MX · seats · 168 · 27175, PL · seats · 140 · 25648.
  • The revenue column adds up to 467,003 — every paid order, counted once.

Common pitfalls

Revenue is much bigger than it should be. A join multiplied rows. It happens when the right-hand side has several rows per key — for example joining on country instead of customer_id. Compare COUNT(*) before and after each join: they should match.

Column in SELECT must be in GROUP BY or an aggregate function. You selected a column that is neither grouped nor summed, like c.first_name. Add it to the GROUP BY, or drop it — one row per country and category can't carry a single first name.

Refunded orders are counted. The WHERE is missing, or it's after the GROUP BY as a HAVING. Filter rows with WHERE; use HAVING only for conditions on the aggregates (HAVING SUM(...) > 10000).

Revenue comes out with long tails of decimals. list_price is a floating-point number, and sums of values like 89.90 can pick up tiny rounding noise. Wrap it in ROUND(SUM(...), 2) whenever the next reader is a person — or an accountant.

What you built

The shape of most reporting SQL: start from the facts, join the descriptions, filter, group. Next level keeps every row and ranks them instead of collapsing them.

Check yourself

One question, drawn from a larger bank. Get it right to pass.

  1. `Column in SELECT must be in GROUP BY or an aggregate function`. What happened?