Annual Product Sales by Calendar Year

HARD30 pts

A revenue analytics platform stores product metadata and long sales periods.

Table: product

product_id: unique product identifier
product_name: display name for the product

Table: sales

product_id: product identifier
period_start: first calendar day in the selling period (inclusive)
period_end: last calendar day in the selling period (inclusive)
average_daily_sales: average amount sold per day during the period

Data guarantees:

Every row in sales references an existing product_id in product.
period_start <= period_end for every sales row.
Dates are between 2018-01-01 and 2020-12-31.
For the same product_id, sales periods do not overlap.

Task: For every sales period, split its contribution by calendar year. For each (product_id, report_year), compute:

overlap_days_in_that_year * average_daily_sales Then sum contributions from all periods of that product that touch that year.

Output requirements:

Return exactly these columns in this order:
1. product_id
2. product_name
3. report_year
4. total_amount
report_year is the numeric year.
Order by product_id ascending, then report_year ascending.

Supported submission environments:

SQL (SQLite 3.27.2)
Pandas (Python)

Example

Input

product:
| product_id | product_name    |
|------------|-----------------|
| 1          | Nova Phone      |
| 2          | Aero Tee        |
| 3          | Orbit Keyring   |

sales:
| product_id | period_start | period_end  | average_daily_sales |
|------------|--------------|-------------|---------------------|
| 1          | 2019-01-25   | 2019-02-28  | 100                 |
| 2          | 2018-12-01   | 2020-01-01  | 10                  |
| 3          | 2019-12-01   | 2020-01-31  | 1                   |

Output

[
  {"product_id":1,"product_name":"Nova Phone","report_year":2019,"total_amount":3500},
  {"product_id":2,"product_name":"Aero Tee","report_year":2018,"total_amount":310},
  {"product_id":2,"product_name":"Aero Tee","report_year":2019,"total_amount":3650},
  {"product_id":2,"product_name":"Aero Tee","report_year":2020,"total_amount":10},
  {"product_id":3,"product_name":"Orbit Keyring","report_year":2019,"total_amount":31},
  {"product_id":3,"product_name":"Orbit Keyring","report_year":2020,"total_amount":31}
]

Explanation

Each period is sliced by year boundaries before multiplying by average_daily_sales. For example, product 2 contributes 31 days in 2018, 365 days in 2019, and 1 day in 2020.

Example

Input

product:
| product_id | product_name  |
|------------|---------------|
| 10         | Data Tablet   |
| 11         | Smart Lamp    |
| 12         | Field Sensor  |

sales:
| product_id | period_start | period_end  | average_daily_sales |
|------------|--------------|-------------|---------------------|
| 10         | 2018-01-01   | 2018-12-31  | 5                   |
| 10         | 2019-01-01   | 2019-01-10  | 7                   |
| 11         | 2019-12-31   | 2020-01-02  | 4                   |
| 12         | 2020-02-28   | 2020-03-01  | 3                   |

Output

[
  {"product_id":10,"product_name":"Data Tablet","report_year":2018,"total_amount":1825},
  {"product_id":10,"product_name":"Data Tablet","report_year":2019,"total_amount":70},
  {"product_id":11,"product_name":"Smart Lamp","report_year":2019,"total_amount":4},
  {"product_id":11,"product_name":"Smart Lamp","report_year":2020,"total_amount":8},
  {"product_id":12,"product_name":"Field Sensor","report_year":2020,"total_amount":9}
]

Explanation

Leap-day handling is required: 2020-02-28 to 2020-03-01 has 3 days total, so product 12 contributes 3 * 3 = 9 in 2020.

Example

Input

product:
| product_id | product_name  |
|------------|---------------|
| 20         | Cloud VM      |
| 21         | Backup Vault  |
| 22         | Edge Router   |

sales:
| product_id | period_start | period_end  | average_daily_sales |
|------------|--------------|-------------|---------------------|
| 20         | 2019-03-01   | 2019-03-31  | 2                   |
| 20         | 2019-07-01   | 2019-07-15  | 4                   |
| 20         | 2020-01-01   | 2020-01-31  | 3                   |
| 21         | 2018-11-15   | 2019-01-15  | 1                   |
| 22         | 2020-12-30   | 2020-12-31  | 50                  |

Output

[
  {"product_id":20,"product_name":"Cloud VM","report_year":2019,"total_amount":122},
  {"product_id":20,"product_name":"Cloud VM","report_year":2020,"total_amount":93},
  {"product_id":21,"product_name":"Backup Vault","report_year":2018,"total_amount":47},
  {"product_id":21,"product_name":"Backup Vault","report_year":2019,"total_amount":15},
  {"product_id":22,"product_name":"Edge Router","report_year":2020,"total_amount":100}
]

Explanation

Multiple periods in the same year for the same product must be summed. Product 20 has two 2019 segments: 312 + 154 = 122.

Accepted0/0·0% Acceptance

Constraints

1 <= number of rows in product <= 200000
0 <= number of rows in sales <= 300000
1 <= product_id <= 1000000000
1 <= average_daily_sales <= 1000000
product_name is a non-empty string
period_start and period_end are valid YYYY-MM-DD dates
2018-01-01 <= period_start <= period_end <= 2020-12-31
For each product_id, sales periods do not overlap
Every sales.product_id exists in product.product_id
Use SQLite 3.27.2 semantics for SQL submissions
For Pandas submissions, return a DataFrame with exact columns: product_id, product_name, report_year, total_amount

Code

Visualizer

Solutions

14px

Test Cases3

Results

Submissions

sales =

[{"product_id":1,"period_start":"2019-01-25","period_end":"2019-02-28","average_daily_sales":100},{"product_id":2,"period_start":"2018-12-01","period_end":"2020-01-01","average_daily_sales":10},{"product_id":3,"period_start":"2019-12-01","period_end":"2020-01-31","average_daily_sales":1}]

product =

[{"product_id":1,"product_name":"Nova Phone"},{"product_id":2,"product_name":"Aero Tee"},{"product_id":3,"product_name":"Orbit Keyring"}]

Annual Product Sales by Calendar Year

Hints

Annual Product Sales by Calendar Year

Hints