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, bestQuery), 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 bestQuery, 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 bestQuery, the best customer, one clause per line.

Hint
Each clause is its own string in the array, and .join(" ") glues them together with a space between. In SQL, "not equal" is !=, not JavaScript's !==. 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.