Annual Product Spend Growth Ledger

Hard

A revenue analytics team maintains transaction-level sales data and needs a yearly growth ledger for each product. Table: user_transactions transaction_id: unique transaction identifier product_id: product identifier spend: transaction value transaction_date: transaction timestamp Definitions: curr_year_spend: total spend for a product in a calendar year. prev_year_spend: total spend for the same product in the previous calendar year. yoy_rate: percentage growth from previous year to current year. Formula: yoy_rate = ((curr_year_spend - prev_year_spend) / prev_year_spend) * 100 Null and safety rules: If no previous-year row exists for that product, prev_year_spend is NULL and yoy_rate is NULL. If prev_year_spend = 0, yoy_rate is NULL. Round yoy_rate to exactly 2 decimal places. Task: Return one row per (product_id, year) that appears in the data. Output columns (exact order): year product_id curr_year_spend prev_year_spend yoy_rate Final ordering: product_id ascending year ascending Supported submission environments: SQL (SQLite 3.27.2) Pandas (Python)

Example 1

Input

user_transactions:
| 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":2019,"product_id":123424,"curr_year_spend":1500.6,"prev_year_spend":null,"yoy_rate":null},
  {"year":2020,"product_id":123424,"curr_year_spend":1000.2,"prev_year_spend":1500.6,"yoy_rate":-33.35},
  {"year":2021,"product_id":123424,"curr_year_spend":1246.44,"prev_year_spend":1000.2,"yoy_rate":24.62},
  {"year":2022,"product_id":123424,"curr_year_spend":2145.32,"prev_year_spend":1246.44,"yoy_rate":72.12}
]

Explanation

Each row compares a product-year total against the immediate previous year for the same product.

Example 2

Input

user_transactions:
| transaction_id | product_id | spend  | transaction_date      |
|----------------|------------|--------|-----------------------|
| 1              | 9100       | 500.00 | 2020-01-10 09:00:00   |
| 2              | 9100       | 750.00 | 2022-05-04 11:30:00   |
| 3              | 9200       | 400.00 | 2021-03-03 15:20:00   |
| 4              | 9200       | 0.00   | 2022-08-21 08:10:00   |
| 5              | 9200       | 200.00 | 2023-02-14 13:00:00   |

Output

[
  {"year":2020,"product_id":9100,"curr_year_spend":500.0,"prev_year_spend":null,"yoy_rate":null},
  {"year":2022,"product_id":9100,"curr_year_spend":750.0,"prev_year_spend":null,"yoy_rate":null},
  {"year":2021,"product_id":9200,"curr_year_spend":400.0,"prev_year_spend":null,"yoy_rate":null},
  {"year":2022,"product_id":9200,"curr_year_spend":0.0,"prev_year_spend":400.0,"yoy_rate":-100.0},
  {"year":2023,"product_id":9200,"curr_year_spend":200.0,"prev_year_spend":0.0,"yoy_rate":null}
]

Explanation

A missing intermediate year does not backfill from older years, and division by zero is not allowed.

Example 3

Input

user_transactions:
| transaction_id | product_id | spend | transaction_date      |
|----------------|------------|-------|-----------------------|
| 100            | 8001       | 5.00  | 2021-01-01 10:00:00   |
| 101            | 8001       | 7.00  | 2021-06-01 09:00:00   |
| 102            | 8001       | 9.00  | 2022-06-15 12:00:00   |
| 103            | 8001       | 0.00  | 2022-11-20 08:00:00   |
| 104            | 8002       | 0.00  | 2020-05-05 07:00:00   |
| 105            | 8002       | 75.00 | 2021-05-05 07:00:00   |

Output

[
  {"year":2021,"product_id":8001,"curr_year_spend":12.0,"prev_year_spend":null,"yoy_rate":null},
  {"year":2022,"product_id":8001,"curr_year_spend":9.0,"prev_year_spend":12.0,"yoy_rate":-25.0},
  {"year":2020,"product_id":8002,"curr_year_spend":0.0,"prev_year_spend":null,"yoy_rate":null},
  {"year":2021,"product_id":8002,"curr_year_spend":75.0,"prev_year_spend":0.0,"yoy_rate":null}
]

Explanation

Spend is aggregated by year first, then growth is computed from yearly totals.

Constraints

1 <= number of rows in user_transactions <= 250000
transaction_id values are unique
1 <= transaction_id <= 1000000000
1 <= product_id <= 1000000000
0 <= spend <= 100000000 and spend has at most 2 decimal places
transaction_date is a valid datetime string in YYYY-MM-DD HH:MM:SS format
curr_year_spend is SUM(spend) grouped by product_id and calendar year
prev_year_spend references only year - 1 of the same product
If prev_year_spend is missing or equals zero, yoy_rate must be NULL
yoy_rate must be rounded to 2 decimal places
For SQL submissions, use SQLite 3.27.2 semantics
For Pandas submissions, return a DataFrame with columns [year, product_id, curr_year_spend, prev_year_spend, yoy_rate]

Preparing your editor. You can read the problem while it loads.

Annual Product Spend Growth Ledger

Hard

A revenue analytics team maintains transaction-level sales data and needs a yearly growth ledger for each product. Table: user_transactions transaction_id: unique transaction identifier product_id: product identifier spend: transaction value transaction_date: transaction timestamp Definitions: curr_year_spend: total spend for a product in a calendar year. prev_year_spend: total spend for the same product in the previous calendar year. yoy_rate: percentage growth from previous year to current year. Formula: yoy_rate = ((curr_year_spend - prev_year_spend) / prev_year_spend) * 100 Null and safety rules: If no previous-year row exists for that product, prev_year_spend is NULL and yoy_rate is NULL. If prev_year_spend = 0, yoy_rate is NULL. Round yoy_rate to exactly 2 decimal places. Task: Return one row per (product_id, year) that appears in the data. Output columns (exact order): year product_id curr_year_spend prev_year_spend yoy_rate Final ordering: product_id ascending year ascending Supported submission environments: SQL (SQLite 3.27.2) Pandas (Python)

Example 1

Input

user_transactions:
| 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":2019,"product_id":123424,"curr_year_spend":1500.6,"prev_year_spend":null,"yoy_rate":null},
  {"year":2020,"product_id":123424,"curr_year_spend":1000.2,"prev_year_spend":1500.6,"yoy_rate":-33.35},
  {"year":2021,"product_id":123424,"curr_year_spend":1246.44,"prev_year_spend":1000.2,"yoy_rate":24.62},
  {"year":2022,"product_id":123424,"curr_year_spend":2145.32,"prev_year_spend":1246.44,"yoy_rate":72.12}
]

Explanation

Each row compares a product-year total against the immediate previous year for the same product.

Example 2

Input

user_transactions:
| transaction_id | product_id | spend  | transaction_date      |
|----------------|------------|--------|-----------------------|
| 1              | 9100       | 500.00 | 2020-01-10 09:00:00   |
| 2              | 9100       | 750.00 | 2022-05-04 11:30:00   |
| 3              | 9200       | 400.00 | 2021-03-03 15:20:00   |
| 4              | 9200       | 0.00   | 2022-08-21 08:10:00   |
| 5              | 9200       | 200.00 | 2023-02-14 13:00:00   |

Output

[
  {"year":2020,"product_id":9100,"curr_year_spend":500.0,"prev_year_spend":null,"yoy_rate":null},
  {"year":2022,"product_id":9100,"curr_year_spend":750.0,"prev_year_spend":null,"yoy_rate":null},
  {"year":2021,"product_id":9200,"curr_year_spend":400.0,"prev_year_spend":null,"yoy_rate":null},
  {"year":2022,"product_id":9200,"curr_year_spend":0.0,"prev_year_spend":400.0,"yoy_rate":-100.0},
  {"year":2023,"product_id":9200,"curr_year_spend":200.0,"prev_year_spend":0.0,"yoy_rate":null}
]

Explanation

A missing intermediate year does not backfill from older years, and division by zero is not allowed.

Example 3

Input

user_transactions:
| transaction_id | product_id | spend | transaction_date      |
|----------------|------------|-------|-----------------------|
| 100            | 8001       | 5.00  | 2021-01-01 10:00:00   |
| 101            | 8001       | 7.00  | 2021-06-01 09:00:00   |
| 102            | 8001       | 9.00  | 2022-06-15 12:00:00   |
| 103            | 8001       | 0.00  | 2022-11-20 08:00:00   |
| 104            | 8002       | 0.00  | 2020-05-05 07:00:00   |
| 105            | 8002       | 75.00 | 2021-05-05 07:00:00   |

Output

[
  {"year":2021,"product_id":8001,"curr_year_spend":12.0,"prev_year_spend":null,"yoy_rate":null},
  {"year":2022,"product_id":8001,"curr_year_spend":9.0,"prev_year_spend":12.0,"yoy_rate":-25.0},
  {"year":2020,"product_id":8002,"curr_year_spend":0.0,"prev_year_spend":null,"yoy_rate":null},
  {"year":2021,"product_id":8002,"curr_year_spend":75.0,"prev_year_spend":0.0,"yoy_rate":null}
]

Explanation

Spend is aggregated by year first, then growth is computed from yearly totals.

Constraints

1 <= number of rows in user_transactions <= 250000
transaction_id values are unique
1 <= transaction_id <= 1000000000
1 <= product_id <= 1000000000
0 <= spend <= 100000000 and spend has at most 2 decimal places
transaction_date is a valid datetime string in YYYY-MM-DD HH:MM:SS format
curr_year_spend is SUM(spend) grouped by product_id and calendar year
prev_year_spend references only year - 1 of the same product
If prev_year_spend is missing or equals zero, yoy_rate must be NULL
yoy_rate must be rounded to 2 decimal places
For SQL submissions, use SQLite 3.27.2 semantics
For Pandas submissions, return a DataFrame with columns [year, product_id, curr_year_spend, prev_year_spend, yoy_rate]