A Query Inside a Query

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 parentheses where the value goes. This subquery runs first, and its answer takes its place.

const query = [
  "SELECT name, price",
  "FROM products",
  "WHERE price = (SELECT MIN(price) FROM products)",
].join(" ");

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, lampQuery), 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 lampQuery, the names of the customers who ordered the lamp (product id 3).

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

Hint
AVG(price) gives the average price. For lampQuery, 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 ( … ).