A Query Inside a Query in Python

Sometimes a query needs a value you don't know yet, like the average price. Instead of looking it up and typing it in, put a whole query in round brackets where the value goes. This subquery runs first, and its answer takes its place.

query = (
    "SELECT name, price "
    "FROM products "
    "WHERE price = (SELECT MIN(price) FROM products) "
)

Here the subquery answers 2, the lowest price, so the query finds the pen. A subquery can also give a list: WHERE id IN (SELECT …) keeps the rows whose id is in that list, and the query inside can have its own subquery. Read such a query from the inside out.

The check runs show(db, query) and show(db, lamp_query), which must print:

name | price
Lamp | 29
Teapot | 22
Gift card | 50
(3 rows)
name
Ben Ortiz
Dan Weber
(2 rows)

Your task: write query, the products that cost more than the average price, with the average worked out by a subquery. Then write lamp_query, the names of the customers who ordered the lamp (product id 3).

Assignment
Follow the Coding Tutorial to write query, then build lamp_query from the inside out.

Hint
AVG(price) gives the average price. For lamp_query, start with the order ids in order_items whose product_id is 3; wrap them with the customer_id of those orders, then with the names from customers, each time with IN ( … ).