lesson 21 Joins & sets · intermediate

Revenue across tables

Calculate revenue while joining orders and related records.

the idea

Revenue lives at the line grain: quantity × unit_price. Join lines to their orders for date and type, and to products and categories for labels, then aggregate at the grain the question asks for. Cancellation lines carry negative quantities, so summing all lines gives net sales.

Dataset: Retail

The tables the challenges use, with their first 6 rows. Under each column name: the kind of value it holds, and which table it links to.

worked example

Run these steps in order to see the idea before you write your own query. The step marked “the pattern” is what the challenges ask for. Running the example does not count toward progress.

1. 1

   Calculate each line’s amount. Run this. quantity × unit_price is line revenue; cancellations use negative quantity.

   step-1.sql Run

   sql›

   SELECT
     i.order_item_id,
     o.order_type,
     i.quantity,
     i.unit_price,
     i.quantity * i.unit_price AS amount
   FROM orders o
   JOIN order_items i ON i.order_id = o.order_id
   WHERE o.ordered_at >= '2011-12-09';

2. 2

   Sum amounts by order type. Run this. GROUP BY order_type totals line amounts separately for sales and cancellations.the pattern

   example.sql Run

   sql›

   SELECT o.order_type, ROUND(SUM(i.quantity * i.unit_price), 2) AS amount
   FROM orders o
   JOIN order_items i ON i.order_id = o.order_id
   GROUP BY o.order_type;

common mistake

Joining before aggregating is safe only when each line matches at most one row in every joined table. Joining a one-to-many table (such as payments) would repeat line amounts.