Lessons › SQL › Go further in SQL: Business questions

SQL lesson

Which stores went down from February to March?

Which stores went down from February to March? First you need each store's change, the last level's query. Then you label it. Labelling or filtering a number you've just worked out needs it worked out first: put the change in a CTE, then use it like a table.

Example

Work it out, then label it

WITH changes AS (
  SELECT store,
         SUM(CASE WHEN order_date >= '2026-03-01' THEN revenue ELSE 0 END)
           - SUM(CASE WHEN order_date < '2026-03-01' THEN revenue ELSE 0 END) AS change
  FROM orders
  GROUP BY store
)
SELECT store,
       change,
       CASE WHEN change < 0 THEN 'down' ELSE 'up' END AS direction
FROM changes

Result:

Step by step

1. Three columns.

   WITH changes AS (SELECT store, SUM(CASE WHEN order_date >= '2026-03-01' THEN revenue ELSE 0 END) - SUM(CASE WHEN order_date < '2026-03-01' THEN revenue ELSE 0 END) AS change FROM orders GROUP BY store) SELECT store, change, CASE WHEN change < 0 THEN 'down' ELSE 'up' END AS direction FROM changes

2. No join.

   WITH changes AS (SELECT store, SUM(CASE WHEN order_date >= '2026-03-01' THEN revenue ELSE 0 END) - SUM(CASE WHEN order_date < '2026-03-01' THEN revenue ELSE 0 END) AS change FROM orders GROUP BY store) SELECT store, change, CASE WHEN change < 0 THEN 'down' ELSE 'up' END AS direction FROM changes

3. Each month in its own SUM, as last level.

   WITH changes AS (SELECT store, SUM(CASE WHEN order_date >= '2026-03-01' THEN revenue ELSE 0 END) - SUM(CASE WHEN order_date < '2026-03-01' THEN revenue ELSE 0 END) AS change FROM orders GROUP BY store) SELECT store, change, CASE WHEN change < 0 THEN 'down' ELSE 'up' END AS direction FROM changes

4. The change goes in a CTE called changes.

   WITH changes AS (SELECT store, SUM(CASE WHEN order_date >= '2026-03-01' THEN revenue ELSE 0 END) - SUM(CASE WHEN order_date < '2026-03-01' THEN revenue ELSE 0 END) AS change FROM orders GROUP BY store) SELECT store, change, CASE WHEN change < 0 THEN 'down' ELSE 'up' END AS direction FROM changes

Practice

Try each one in your head first, then open the answer.

WITH changes AS (SELECT store, SUM(CASE WHEN order_date >= '2026-03-01' THEN revenue ELSE 0 END) - SUM(CASE WHEN order_date < '2026-03-01' THEN revenue ELSE 0 END) AS change FROM orders GROUP BY store) SELECT store, change, CASE WHEN change < 0 THEN 'down' ELSE 'up' END AS direction FROM changes

WITH changes AS (SELECT store, SUM(CASE WHEN order_date >= '2026-03-01' THEN revenue ELSE 0 END) - SUM(CASE WHEN order_date < '2026-03-01' THEN revenue ELSE 0 END) AS change FROM orders GROUP BY store) SELECT store, change FROM changes WHERE change < 0

SELECT store FROM orders GROUP BY store HAVING SUM(CASE WHEN order_date >= '2026-03-01' THEN revenue ELSE 0 END) < SUM(CASE WHEN order_date < '2026-03-01' THEN revenue ELSE 0 END)

WITH changes AS (SELECT product, SUM(CASE WHEN order_date >= '2026-03-01' THEN revenue ELSE 0 END) - SUM(CASE WHEN order_date < '2026-03-01' THEN revenue ELSE 0 END) AS change FROM orders GROUP BY product) SELECT product, change, CASE WHEN change < 0 THEN 'down' ELSE 'up' END AS direction FROM changes

WITH changes AS (SELECT product, SUM(CASE WHEN order_date >= '2026-03-01' THEN revenue ELSE 0 END) - SUM(CASE WHEN order_date < '2026-03-01' THEN revenue ELSE 0 END) AS change FROM orders GROUP BY product) SELECT product, change FROM changes WHERE change < 0