A Query Inside a Query in Go

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.

var 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.

Each piece of the Go string ends with a space and a +, except the last one. 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 outside in.

Hint
AVG(price) gives the average price. For lampQuery, the innermost query gives the order ids in order_items whose product_id is 3; around it go the customer_id of those orders, then the names from customers, each time with IN ( … ).