Lessons › SQL › Go further in SQL: Business questions

SQL lesson

What was each store's month-on-month growth?

What was each store's month-on-month growth, as a decimal? Growth is this month minus last month, divided by last month. That needs each store's monthly totals first, then last month's total on the same row (LAG), then the safe division from Chapter 8.

Example

This month against last, as a share of last

WITH m AS (
  SELECT store,
         TO_CHAR(order_date, 'YYYY-MM') AS month,
         SUM(revenue) AS revenue
  FROM orders
  GROUP BY store, TO_CHAR(order_date, 'YYYY-MM')
)
SELECT store,
       month,
       1.0 * (revenue - LAG(revenue) OVER (PARTITION BY store ORDER BY month))
         / NULLIF(LAG(revenue) OVER (PARTITION BY store ORDER BY month), 0) AS growth
FROM m

Result:

Step by step

1. February has no month before it, so its growth is empty.

   WITH m AS (SELECT store, TO_CHAR(order_date, 'YYYY-MM') AS month, SUM(revenue) AS revenue FROM orders GROUP BY store, TO_CHAR(order_date, 'YYYY-MM')) SELECT store, month, 1.0 * (revenue - LAG(revenue) OVER (PARTITION BY store ORDER BY month)) / NULLIF(LAG(revenue) OVER (PARTITION BY store ORDER BY month), 0) AS growth FROM m

2. The month comes from order_date with TO_CHAR.

   WITH m AS (SELECT store, TO_CHAR(order_date, 'YYYY-MM') AS month, SUM(revenue) AS revenue FROM orders GROUP BY store, TO_CHAR(order_date, 'YYYY-MM')) SELECT store, month, 1.0 * (revenue - LAG(revenue) OVER (PARTITION BY store ORDER BY month)) / NULLIF(LAG(revenue) OVER (PARTITION BY store ORDER BY month), 0) AS growth FROM m

3. Every order counts.

   WITH m AS (SELECT store, TO_CHAR(order_date, 'YYYY-MM') AS month, SUM(revenue) AS revenue FROM orders GROUP BY store, TO_CHAR(order_date, 'YYYY-MM')) SELECT store, month, 1.0 * (revenue - LAG(revenue) OVER (PARTITION BY store ORDER BY month)) / NULLIF(LAG(revenue) OVER (PARTITION BY store ORDER BY month), 0) AS growth FROM m

4. A CTE for the totals, then LAG(revenue) OVER (PARTITION BY store ORDER BY month).

   WITH m AS (SELECT store, TO_CHAR(order_date, 'YYYY-MM') AS month, SUM(revenue) AS revenue FROM orders GROUP BY store, TO_CHAR(order_date, 'YYYY-MM')) SELECT store, month, 1.0 * (revenue - LAG(revenue) OVER (PARTITION BY store ORDER BY month)) / NULLIF(LAG(revenue) OVER (PARTITION BY store ORDER BY month), 0) AS growth FROM m

Practice

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

WITH m AS (SELECT store, TO_CHAR(order_date, 'YYYY-MM') AS month, SUM(revenue) AS revenue FROM orders GROUP BY store, TO_CHAR(order_date, 'YYYY-MM')) SELECT store, month, 1.0 * (revenue - LAG(revenue) OVER (PARTITION BY store ORDER BY month)) / NULLIF(LAG(revenue) OVER (PARTITION BY store ORDER BY month), 0) AS growth FROM m

WITH m AS (SELECT store, TO_CHAR(order_date, 'YYYY-MM') AS month, COUNT(*) AS orders FROM orders GROUP BY store, TO_CHAR(order_date, 'YYYY-MM')) SELECT store, month, 1.0 * (orders - LAG(orders) OVER (PARTITION BY store ORDER BY month)) / NULLIF(LAG(orders) OVER (PARTITION BY store ORDER BY month), 0) AS order_growth FROM m

WITH m AS (SELECT product, TO_CHAR(order_date, 'YYYY-MM') AS month, SUM(revenue) AS revenue FROM orders GROUP BY product, TO_CHAR(order_date, 'YYYY-MM')) SELECT product, month, 1.0 * (revenue - LAG(revenue) OVER (PARTITION BY product ORDER BY month)) / NULLIF(LAG(revenue) OVER (PARTITION BY product ORDER BY month), 0) AS growth FROM m

WITH m AS (SELECT TO_CHAR(order_date, 'YYYY-MM') AS month, SUM(revenue) AS revenue FROM orders GROUP BY TO_CHAR(order_date, 'YYYY-MM')) SELECT month, 1.0 * (revenue - LAG(revenue) OVER (ORDER BY month)) / NULLIF(LAG(revenue) OVER (ORDER BY month), 0) AS growth FROM m

WITH m AS (SELECT store, TO_CHAR(order_date, 'YYYY-MM') AS month, SUM(revenue) AS revenue FROM orders GROUP BY store, TO_CHAR(order_date, 'YYYY-MM')), g AS (SELECT store, month, 1.0 * (revenue - LAG(revenue) OVER (PARTITION BY store ORDER BY month)) / NULLIF(LAG(revenue) OVER (PARTITION BY store ORDER BY month), 0) AS growth FROM m) SELECT store, growth FROM g WHERE growth > 0