Apples & Oranges - Problem

You have a Sales table that tracks daily fruit sales. Each row contains a sale_date, the fruit type (either 'apples' or 'oranges'), and the sold_num quantity sold.

Your task is to calculate the difference between apples and oranges sold each day. The result should show each date with the difference (apples sold - oranges sold).

Return the results ordered by sale_date.

Table Schema

Sales
Column Name Type Description
sale_date PK date Date of the sale
fruit PK enum Type of fruit: 'apples' or 'oranges'
sold_num int Number of fruits sold
Primary Key: (sale_date, fruit)
Note: Primary key is the combination of sale_date and fruit. Each day can have at most one record per fruit type.

Input & Output

Example 1 — Mixed Sales Days
Input Table:
sale_date fruit sold_num
2020-05-01 apples 10
2020-05-01 oranges 8
2020-05-02 apples 15
2020-05-03 oranges 15
2020-05-03 apples 16
Output:
sale_date diff
2020-05-01 2
2020-05-02 15
2020-05-03 1
💡 Note:

For each date, we calculate apples sold minus oranges sold:

  • 2020-05-01: 10 apples - 8 oranges = 2
  • 2020-05-02: 15 apples - 0 oranges = 15
  • 2020-05-03: 16 apples - 15 oranges = 1
Example 2 — Single Fruit Days
Input Table:
sale_date fruit sold_num
2020-05-01 apples 5
2020-05-02 oranges 7
Output:
sale_date diff
2020-05-01 5
2020-05-02 -7
💡 Note:

When only one fruit type is sold on a day:

  • 2020-05-01: 5 apples - 0 oranges = 5
  • 2020-05-02: 0 apples - 7 oranges = -7

Constraints

  • 1 ≤ sold_num ≤ 1000
  • fruit is either 'apples' or 'oranges'
  • sale_date is a valid date

Visualization

Tap to expand
Apples & Oranges: Daily Difference CalculationSales Tablesale_datefruitsold_num05-01apples1005-01oranges805-02apples15GROUP BYsale_dateCASE WHENSUM(...)Resultsale_datediff2020-05-0122020-05-0215Formula: Apples - Oranges per date05-01: 10 - 8 = 2 | 05-02: 15 - 0 = 15
Understanding the Visualization
1
Input
Sales table with date, fruit type, and quantity
2
Group & Pivot
Group by date and use CASE WHEN to separate fruits
3
Calculate
Subtract oranges from apples for each date
Key Takeaway
🎯 Key Insight: Use conditional aggregation with CASE WHEN to pivot categorical data into calculations

Related Problems

Asked in
Amazon 3 Microsoft 2 Apple 1
23.4K Views
Medium Frequency
~8 min Avg. Time
892 Likes
Ln 1, Col 1
Smart Actions
💡 Explanation
AI Ready
💡 Suggestion Tab to accept Esc to dismiss
// Output will appear here after running code
Code Editor Closed
Click the red button to reopen