Daily Fruit Sales Delta Report

Medium

A commerce analytics team records daily fruit sales in a single event table. Table: sales sale_date: calendar date of a sale snapshot fruit: fruit label, one of "apples" or "oranges" sold_num: quantity sold for that fruit on that date (sale_date, fruit) is unique Task: For each date, compute the net difference between apple and orange volume: diff = apples_sold - oranges_sold Output requirements: Return exactly two columns in this order: sale_date diff Order the result by sale_date ascending. Supported submission environments: SQL (SQLite 3.27.2) Pandas (Python)

Example 1

Input

sales:
| sale_date  | fruit    | sold_num |
|------------|----------|----------|
| 2024-04-01 | apples   | 34       |
| 2024-04-01 | oranges  | 20       |
| 2024-04-02 | apples   | 18       |
| 2024-04-02 | oranges  | 25       |
| 2024-04-03 | apples   | 12       |
| 2024-04-03 | oranges  | 12       |

Output

[
  {"sale_date":"2024-04-01","diff":14},
  {"sale_date":"2024-04-02","diff":-7},
  {"sale_date":"2024-04-03","diff":0}
]

Explanation

The report computes apple volume minus orange volume for each date independently.

Example 2

Input

sales:
| sale_date  | fruit    | sold_num |
|------------|----------|----------|
| 2024-06-10 | apples   | 0        |
| 2024-06-10 | oranges  | 7        |
| 2024-06-11 | apples   | 100      |
| 2024-06-11 | oranges  | 0        |

Output

[
  {"sale_date":"2024-06-10","diff":-7},
  {"sale_date":"2024-06-11","diff":100}
]

Explanation

Zero values are valid; signs of diff correctly reflect which fruit dominated that day.

Example 3

Input

sales:
| sale_date  | fruit    | sold_num |
|------------|----------|----------|
| 2023-12-31 | apples   | 5        |
| 2023-12-31 | oranges  | 10       |
| 2024-01-01 | apples   | 9        |
| 2024-01-01 | oranges  | 4        |
| 2024-01-02 | apples   | 2        |
| 2024-01-02 | oranges  | 2        |

Output

[
  {"sale_date":"2023-12-31","diff":-5},
  {"sale_date":"2024-01-01","diff":5},
  {"sale_date":"2024-01-02","diff":0}
]

Explanation

Dates are sorted chronologically and each row is computed as apples minus oranges.

Constraints

1 <= number of rows in sales <= 500000
(sale_date, fruit) is unique
fruit is either 'apples' or 'oranges'
0 <= sold_num <= 1000000000
sale_date is a valid YYYY-MM-DD date
Return exactly two columns: sale_date, diff
Compute diff as apples - oranges per date
Order result by sale_date ascending
Use SQLite 3.27.2 semantics for SQL submissions
For Pandas submissions, return a DataFrame with exact columns: sale_date, diff

sales: | sale_date | fruit | sold_num | |------------|----------|----------| | 2024-04-01 | apples | 34 | | 2024-04-01 | oranges | 20 | | 2024-04-02 | apples | 18 | | 2024-04-02 | oranges | 25 | | 2024-04-03 | apples | 12 | | 2024-04-03 | oranges | 12 |

sales: | sale_date | fruit | sold_num | |------------|----------|----------| | 2024-06-10 | apples | 0 | | 2024-06-10 | oranges | 7 | | 2024-06-11 | apples | 100 | | 2024-06-11 | oranges | 0 |

sales: | sale_date | fruit | sold_num | |------------|----------|----------| | 2023-12-31 | apples | 5 | | 2023-12-31 | oranges | 10 | | 2024-01-01 | apples | 9 | | 2024-01-01 | oranges | 4 | | 2024-01-02 | apples | 2 | | 2024-01-02 | oranges | 2 |