Project: the report in steps

SQL · Unit 10: WITH and sets

Full plan
SQL
Project
3 exercises

The owner wants to know which products carry the store. Last unit the customers were measured against the average, and now it's the products. How much has each one brought in, what's the average, and which ones beat it? This time each question gets its own place and its own name.

The first is this step. It counts everything ordered, the Tablet on the order dated the 20th included. You get seven products. The Speaker isn't there, because nobody has ordered it and a plain JOIN leaves it out.

WITH per_product AS (
  SELECT p.name,
    SUM(p.price * o.quantity)
      AS sold
  FROM orders o
  JOIN products p
    ON o.product_id = p.id
  GROUP BY p.id, p.name
)
SELECT name, sold
FROM per_product
ORDER BY sold DESC;

Returns

name	sold
Laptop	899
Monitor	567
Tablet	320
Headphones	179.7
Keyboard	136.5
Mouse	119.4
Cable	68.25

Exercises in this lesson

You do them in the app, which checks them on the spot and explains why.

1. Complete the code
The second step, average, holds in a single row the average of what was sold per product. Fill in the blanks with what it averages and where it reads that from.
2. Write the code
The third question. Both steps are already there. Below them, write the query that returns two columns, in this order: the name and the amount sold of the products that have brought in more than the average. Biggest first. The average isn't typed in by hand. It's in the value column of the average step.
3. Write the code
The owner has one more question. How much of what was sold comes from those products? Add a third step, standouts, with the name and the amount sold of the products that beat the average, and below it a query that returns a single row with two columns, in this order: how many products are in standouts and how much they add up to.

Do this lesson

It opens in your browser. This lesson is part of the full plan; the first unit of each course is free.

See all lessons →