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:
customers→input1orders→input2products→input3

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
FROMthe 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
SELECTis either grouped or aggregated.countryandcategoryare in theGROUP BY;ordersandrevenueare aggregates.
Preview Output on the SQL node:

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
revenuecolumn 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.
`Column in SELECT must be in GROUP BY or an aggregate function`. What happened?