Aan de slagGa gratis aan de slag

Order growth rate

Bob needs one more chart to wrap up his pitch deck. He's covered Delivr's gain of new users, its growing MAUs, and its high retention rates. Something is missing, though. Throughout the pitch deck, there's not a single mention of the best indicator of user activity: the users' orders! The more orders users make, the more active they are on Delivr, and the more money Delivr generates.

Send Bob a table of MoM order growth rates.

(Recap: MoM means month-on-month.)

Deze oefening maakt deel uit van de cursus

Analyzing Business Data in SQL

Cursus bekijken

Oefeninstructies

  • Count the unique orders per month.
  • Fetch each month's previous and current orders.
  • Return a table of MoM order growth rates.

Praktische interactieve oefening

Probeer deze oefening eens door deze voorbeeldcode in te vullen.

WITH orders AS (
  SELECT
    DATE_TRUNC('month', order_date) :: DATE AS delivr_month,
    --  Count the unique order IDs
    ___ AS orders
  FROM orders
  GROUP BY delivr_month),

  orders_with_lag AS (
  SELECT
    delivr_month,
    -- Fetch each month's current and previous orders
    ___,
    COALESCE(
      ___,
    1) AS last_orders
  FROM orders)

SELECT
  delivr_month,
  -- Calculate the MoM order growth rate
  ROUND(
    ___,
  2) AS growth
FROM orders_with_lag
ORDER BY delivr_month ASC;
Code bewerken en uitvoeren