Monthly Sales

The offers will go out next week, and the shop wants to know how its sales are doing now, so that it can compare them later: the revenue of each month.

Revenue is the money the shop got: for each item of each paid order, the quantity times the price. Take the price from order_items, where it's what the customer really paid. On Black Friday, order 28 got its games cheaper than games.price says.

To group orders by month, you need the month of each order, and strftime() makes it from the date: strftime('%Y-%m', ordered_at) gives the year and the month, such as '2025-11', and strftime('%Y', ordered_at) just the year:

SELECT id, ordered_at,
    strftime('%Y-%m', ordered_at) AS month,
    strftime('%Y', ordered_at) AS year
FROM orders
WHERE id IN (4, 17, 28);

Give such a column an alias, and you can use it in GROUP BY and ORDER BY. Months written as 'YYYY-MM' sort in the order of time, just like whole dates.

Typical mistake: counting orders with COUNT(*). After the join with order_items, each order is there once for every game in it, so COUNT(*) counts items, not orders. COUNT(DISTINCT o.id) counts each order once.

And one more trap: strftime() gives text, so compare its result with text, strftime('%Y', ordered_at) = '2025'. With the number 2025 the condition is never true, because SQLite doesn't take a number and a text for equal.