Lesson 6 of 10

Naming intermediate results

14 min
5 checks
3 hints
Go to task

Goal

Naming each step with WITH

Explanation

Once a subquery grows and its parts start nesting inside each other, it becomes harder to read than it was to write. That's where WITH helps. It gives the intermediate result a name at the top of the query, and you then read from it as if it were a table: WITH sales AS (SELECT ...) SELECT ... FROM sales.

You can use that name more than once. The question "Which products bring in more than the average revenue?" needs the revenue figures twice, once to show them and once to compute their average. Without WITH you'd write the same aggregation twice, then fix a mistake in one copy and forget it in the other.

WITH and the query after it are a single statement, so a semicolon between them cuts the statement off and you get an error. The name also stops being available once that statement ends.

Your task

In WITH sales, compute each product's revenue, which is the sum of qty * price over all its line items. Then select product and revenue from it for the products whose revenue is above the average revenue, sorted from the highest revenue down.

Checks

5 checks

Columns are product and revenue
Each product's revenue is correct
Every listed product is above the average revenue
No matching product is missing
Sorted by revenue, highest first

Tables in this lesson

CREATE TABLE customers (
  id   INTEGER PRIMARY KEY,
  name TEXT NOT NULL,
  city TEXT NOT NULL
);

CREATE TABLE orders (
  id          INTEGER PRIMARY KEY,
  customer_id INTEGER REFERENCES customers(id),
  total       INTEGER NOT NULL,
  placed_on   TEXT    NOT NULL
);

INSERT INTO customers (id, name, city) VALUES
  (1, 'Layan',  'Riyadh'),
  (2, 'Khalid', 'Jeddah'),
  (3, 'Reem',   'Dammam'),
  (4, 'Fahd',   'Riyadh');

INSERT INTO orders (customer_id, total, placed_on) VALUES
  (1, 120, '2026-09-01'),
  (1,  80, '2026-09-12'),
  (2, 300, '2026-09-03'),
  (3,  45, '2026-09-20'),
  (2,  60, '2026-09-25');

INSERT INTO customers (id, name, city) VALUES (5, 'Sara', 'Jeddah');

CREATE TABLE products (
  id    INTEGER PRIMARY KEY,
  name  TEXT    NOT NULL,
  price INTEGER NOT NULL
);

CREATE TABLE order_items (
  id         INTEGER PRIMARY KEY,
  order_id   INTEGER REFERENCES orders(id),
  product_id INTEGER REFERENCES products(id),
  qty        INTEGER NOT NULL
);

INSERT INTO products (id, name, price) VALUES
  (1, 'Notebook',    15),
  (2, 'Pen',          5),
  (3, 'Bag',         90),
  (4, 'Calculator',  60),
  (5, 'Lamp',       100);

INSERT INTO order_items (order_id, product_id, qty) VALUES
  (1, 3, 1), (1, 1, 2),
  (2, 4, 1), (2, 2, 4),
  (3, 3, 2), (3, 4, 2),
  (4, 1, 3),
  (5, 1, 2), (5, 2, 6);

Naming intermediate results

Explanation

Your task

Checks

Hints