Welcome to Subscribe On Youtube

3214. Year on Year Growth Rate 🔒

Description

Table: user_transactions

++-+
\| transaction_id   \| integer  \|
\| product_id       \| integer  \|
\| spend            \| decimal  \|
\| transaction_date \| datetime \|
++-+++
\| transaction_id \| product_id \| spend   \| transaction_date    \|
++-+++

Output:

+++-+-+-+
\| 2019 \| 123424     \| 1500.60        \| NULL           \| NULL     \|
\| 2020 \| 123424     \| 1000.20        \| 1500.60        \| -33.35   \|
\| 2021 \| 123424     \| 1246.44        \| 1000.20        \| 24.62    \|
\| 2022 \| 123424     \| 2145.32        \| 1246.44        \| 72.12    \|
+++-+----+

Explanation:

  • For product ID 123424:
    • In 2019:
      • Current year's spend is 1500.60
      • No previous year's spend recorded
      • YoY growth rate: NULL
    • In 2020:
      • Current year's spend is 1000.20
      • Previous year's spend is 1500.60
      • YoY growth rate: ((1000.20 - 1500.60) / 1500.60) * 100 = -33.35%
    • In 2021:
      • Current year's spend is 1246.44
      • Previous year's spend is 1000.20
      • YoY growth rate: ((1246.44 - 1000.20) / 1000.20) * 100 = 24.62%
    • In 2022:
      • Current year's spend is 2145.32
      • Previous year's spend is 1246.44
      • YoY growth rate: ((2145.32 - 1246.44) / 1246.44) * 100 = 72.12%

Note: Output table is ordered by product_id and year in ascending order.

</div>

Solutions

Solution 1: Grouping Statistics + Left Join

We can first group by product_id and year(transaction_date) to perform the statistics, then use a left join to associate the statistics of the current year with those of the previous year, and finally calculate the year-on-year growth rate.

  • # Write your MySQL query statement below
WITH
    T AS (
        SELECT product_id, YEAR(transaction_date) year, SUM(spend) curr_year_spend
        FROM user_transactions
        GROUP BY 1, 2
    ),
    S AS (
        SELECT t1.year, t1.product_id, t1.curr_year_spend, t2.curr_year_spend prev_year_spend
        FROM
            T t1
            LEFT JOIN T t2 ON t1.product_id = t2.product_id AND t1.year = t2.year + 1
    )
SELECT
    *,
    ROUND((curr_year_spend - prev_year_spend) / prev_year_spend * 100, 2) yoy_rate
FROM S
ORDER BY 2, 1;

All Problems

All Solutions