AI Hints
Homeโ€บSQL Tutorialโ€บPractice Gym: Windows and CTEs
SQL ยท Lesson 122 of 126

Gym: Trends and Sizes

Two final challenges.

โฑ 2 min readโ–ถ Video at 2:54:41๐ŸŽ“ Free
Watch this lesson in the SQL courseWatch on YouTube โ†—Full SQL course

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

Gym: Trends and Sizes
From the video: Gym: Trends and Sizes
  • 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.

โœ๏ธ Exercise: The biggest drop

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
solution.sqlsql
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;
Output
โ”Œโ”€โ”€โ”€โ”€โ”€โ”€โ”€โ”€โ”€โ”ฌโ”€โ”€โ”€โ”€โ”€โ”€โ”€โ”€โ”ฌโ”€โ”€โ”€โ”€โ”€โ”€โ”€โ”€โ”
โ”‚  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.

โœ๏ธ Exercise: Big orders

Orders with 5 or more copies in total

Show hint

CTE: SUM(quantity) per order, then COUNT WHERE copies >= 5

Show solution
solution.sqlsql
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;
Output
โ”Œโ”€โ”€โ”€โ”€โ”€โ”€โ”€โ”€โ”€โ”€โ”€โ”€โ”
โ”‚ 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.

Which groups?PARTITION BY
What order?ORDER BY
Filter the result?wrap it in a CTE
Why?WHERE runs first

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
Next up: Gym Reflection โ€” what to practise next