WITH: Name a Query in Python

A subquery works, but it sits inside another query, so you read it from the middle out. A CTE (common table expression) moves that inner query to the top and gives it a name: WITH name AS ( … ). The main query below it reads that name like a table, so the whole query reads top to bottom.

For example, this names the cheap products, then lists them by price:

WITH cheap AS (
    SELECT name, price
    FROM products
    WHERE price < 10
)
SELECT name
FROM cheap
ORDER BY price

The named query has exactly the columns its SELECT gives it, here name and price. A column made with SUM(…) AS total is a plain column called total in the main query, so a plain WHERE can test it.

In this lesson, order_totals adds up the lines of each order in order_items (quantity times unit price), and the main query keeps the orders worth 40 or more. The check runs show(db, query) and prints:

order_id | total
3 | 41
5 | 73
7 | 62
(3 rows)

Your task: in query, write a CTE named order_totals with each order_id and its total, then a main query that selects order_id, total from it and keeps only the totals of 40 or more. As in the earlier SQL lessons, each clause is one string on its own line.

Assignment
Follow the Coding Tutorial to name the order totals query with WITH and filter it in a main query.

Hint
Inside the brackets: SELECT, FROM order_items, GROUP BY order_id. After the closing bracket: SELECT, FROM order_totals, WHERE.