Gym: Trends and Sizes
Two final challenges.
Two final challenges. One looks at trends over time, and one counts big orders. Then you're done with the gym.

- A trend challenge
- A size challenge
- Then you're done
The biggest drop
In twenty twenty five, which month had the biggest drop in orders, compared with the month before? Pause and try. Here's a solution. Lag gives the change, and we skip January, which has no previous month. Then sort from the most negative. September dropped by twenty seven orders, after a record August.
2025 month with the biggest drop vs the month before
Show hint
LAG(orders) for the change, WHERE change IS NOT NULL, ORDER BY change
Show solution
WITH monthly AS (
SELECT strftime('%Y-%m', order_date) AS month, COUNT(*) AS orders
FROM orders
WHERE order_date >= '2025-01-01'
GROUP BY month
),
changes AS (
SELECT month, orders,
orders - LAG(orders) OVER (ORDER BY month) AS change
FROM monthly
)
SELECT month, orders, change
FROM changes
WHERE change IS NOT NULL
ORDER BY change
LIMIT 1;โโโโโโโโโโโฌโโโโโโโโโฌโโโโโโโโโ โ month โ orders โ change โ โโโโโโโโโโโผโโโโโโโโโผโโโโโโโโโค โ 2025-09 โ 93 โ -27 โ โโโโโโโโโโโดโโโโโโโโโดโโโโโโโโโ
Big orders
How many orders contained five or more copies in total? Pause and try. Here's a solution. ACTE adds up the copies per order, then count the ones with at least five. Thirty nine big orders.
Orders with 5 or more copies in total
Show hint
CTE: SUM(quantity) per order, then COUNT WHERE copies >= 5
Show solution
WITH order_size AS (
SELECT order_id, SUM(quantity) AS copies
FROM order_items
GROUP BY order_id
)
SELECT COUNT(*) AS big_orders
FROM order_size
WHERE copies >= 5;โโโโโโโโโโโโโโ โ big_orders โ โโโโโโโโโโโโโโค โ 39 โ โโโโโโโโโโโโโโ
The window function checklist
Whenever you write a window function, ask yourself three questions. Over what groups? That's partition by. In what order? That's order by. And do I need to filter on the result? If yes, wrap it in a CTE first, because where runs before window functions.
Summary
The gym is done. If you solved most of these on your own, you're ready to use SQL for real.
Key takeaways- LAG for change
- Skip the NULL row
- CTE then COUNT