Project: Monthly Sales Report

The shop's owner wants a monthly report: for each month, how many orders came in and how much money they brought. You already know almost every piece: JOIN, WHERE, GROUP BY, ORDER BY, SUM and COUNT. One new tool turns a date into a month: strftime.

strftime takes a format and a date. It replaces each % code in the format with a part of the date: %Y is the year, %m the month, %d the day. And COUNT(DISTINCT …) counts each different value only once.

SELECT strftime('%Y', '2026-03-15');          -- '2026'
SELECT strftime('%d/%m', '2026-03-15');       -- '15/03'
SELECT COUNT(DISTINCT city) FROM customers;   -- each city once

Watch for two traps. When you join orders to their lines in order_items, an order with two lines shows up in two rows, so count orders with DISTINCT. And a cancelled order is not a sale, so leave it out. The check runs show(db, query) and show(db, best_query), which must print:

month | order_count | revenue
2026-03 | 2 | 63
2026-04 | 2 | 68
2026-05 | 2 | 135
(3 rows)
name | spent
Chloe Ng | 89
(1 row)

Your task: in query, build the report: the month as 'YYYY-MM' named month, the number of orders named order_count and the money named revenue. Leave out cancelled orders and give one row per month, from March to May. In best_query, find the customer who spent the most: their name and their total, named spent.

Assignment
Follow the Coding Tutorial to build query, the monthly report, and then best_query, the best customer, one clause per line.

Hint
Each clause is its own string inside the parentheses. Python glues them together, so end each one with a space. Group and sort the report by month, the name you gave the strftime column. For the best customer, go from customers to orders to order_items. Then group, sort with the biggest first and keep one row.