Annual Product Sales by Calendar Year

Hard

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: product_id product_name report_year 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 1

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 2

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 3

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: 31*2 + 15*4 = 122.

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

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

Annual Product Sales by Calendar Year

Hard

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: product_id product_name report_year 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 1

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 2

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 3

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: 31*2 + 15*4 = 122.

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