The problem

It is the end of the month at the online tea shop, and the owner wants numbers: how many orders, how much money, which customers come back. A spreadsheet export would take an hour to clean. You answer each question with one query in psql, on PostgreSQL.

The table orders (id int PRIMARY KEY, customer_id int, status text, total numeric(8,2), ordered_at date) holds 9 orders. status is paid, shipped or cancelled. Customer 1 has 3 orders, customers 2 and 3 have 2 each (one of them cancelled), customer 4 has 1, and order 107 was a guest checkout, so its customer_id is NULL. The table order_items (order_id int, product_id int, quantity int) holds one row per product in an order.