00:00:00

Description

Editorial

Annual Product Spend Growth Ledger

HARD30 pts

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

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

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

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.

Accepted0/0·0% Acceptance

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]

Code

Visualizer

Solutions

14px

Test Cases3

Results

Submissions

user_transactions =

[{"transaction_id":1,"product_id":123424,"spend":1500.6,"transaction_date":"2019-08-01 03:04:04"},{"transaction_id":2,"product_id":123424,"spend":1000.2,"transaction_date":"2020-09-01 04:04:04"},{"transaction_id":3,"product_id":123424,"spend":1246.44,"transaction_date":"2021-10-01 05:04:04"},{"transaction_id":4,"product_id":123424,"spend":2145.32,"transaction_date":"2022-11-01 06:04:04"}]

Loading problem...

101

00:00:00

Description

Editorial

Annual Product Spend Growth Ledger

HARD30 pts

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

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

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

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.

Accepted0/0·0% Acceptance

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]

Code

Visualizer

Solutions

14px

Test Cases3

Results

Submissions

user_transactions =

Annual Product Spend Growth Ledger

Hints

Annual Product Spend Growth Ledger

Hints