Table: user_transactions
+------------------+----------+ | Column Name | Type | +------------------+----------+ | transaction_id | integer | | product_id | integer | | spend | decimal | | transaction_date | datetime | +------------------+----------+ The transaction_id column uniquely identifies each row in this table. Each row of this table contains the transaction ID, product ID, the spend amount, and the transaction date.
Write a solution to calculate the year-on-year growth rate for the total spend for each product.
The result table should include the following columns:
year: The year of the transaction.product_id: The ID of the product.curr_year_spend: The total spend for the current year.prev_year_spend: The total spend for the previous year.yoy_rate: The year-on-year growth rate percentage, rounded to 2 decimal places.Return the result table ordered by product_id,year in ascending order.
The result format is in the following example.
Example:
Input:
user_transactions table:
+----------------+------------+---------+---------------------+ | transaction_id | product_id | spend | transaction_date | +----------------+------------+---------+---------------------+ | 1341 | 123424 | 1500.60 | 2019-12-31 12:00:00 | | 1423 | 123424 | 1000.20 | 2020-12-31 12:00:00 | | 1623 | 123424 | 1246.44 | 2021-12-31 12:00:00 | | 1322 | 123424 | 2145.32 | 2022-12-31 12:00:00 | +----------------+------------+---------+---------------------+
Output:
+------+------------+----------------+----------------+----------+ | year | product_id | curr_year_spend| prev_year_spend| yoy_rate | +------+------------+----------------+----------------+----------+ | 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:
Note: Output table is ordered by product_id and year in ascending order.
Loading editor...
{"headers":{"user_transactions":["transaction_id","product_id","spend","transaction_date"]},"rows":{"user_transactions":[[1341,123424,1500.60,"2019-12-31 12:00:00"],[1423,123424,1000.20,"2020-12-31 12:00:00"],[1623,123424,1246.44,"2021-12-31 12:00:00"],[1322,123424,2145.32,"2022-12-31 12:00:00"]]}}