Problem

Table: Sales

1
2
3
4
5
6
7
8
9

+---------------+---------+
| Column Name   | Type    |
+---------------+---------+
| sale_date     | date    |
| fruit         | enum    | 
| sold_num      | int     | 
+---------------+---------+
(sale_date, fruit) is the primary key (combination of columns with unique values) of this table.
This table contains the sales of "apples" and "oranges" sold each day.

Write a solution to report the difference between the number of apples and oranges sold each day.

Return the result table ordered by sale_date.

The result format is in the following example.

Examples

Example 1:

 1
 2
 3
 4
 5
 6
 7
 8
 9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
28

Input: 
Sales table:
+------------+------------+-------------+
| sale_date  | fruit      | sold_num    |
+------------+------------+-------------+
| 2020-05-01 | apples     | 10          |
| 2020-05-01 | oranges    | 8           |
| 2020-05-02 | apples     | 15          |
| 2020-05-02 | oranges    | 15          |
| 2020-05-03 | apples     | 20          |
| 2020-05-03 | oranges    | 0           |
| 2020-05-04 | apples     | 15          |
| 2020-05-04 | oranges    | 16          |
+------------+------------+-------------+
Output: 
+------------+--------------+
| sale_date  | diff         |
+------------+--------------+
| 2020-05-01 | 2            |
| 2020-05-02 | 0            |
| 2020-05-03 | 20           |
| 2020-05-04 | -1           |
+------------+--------------+
Explanation: 
Day 2020-05-01, 10 apples and 8 oranges were sold (Difference  10 - 8 = 2).
Day 2020-05-02, 15 apples and 15 oranges were sold (Difference 15 - 15 = 0).
Day 2020-05-03, 20 apples and 0 oranges were sold (Difference 20 - 0 = 20).
Day 2020-05-04, 15 apples and 16 oranges were sold (Difference 15 - 16 = -1).

Solution

Method 1 – Aggregate and Pivot

Intuition: We need to find, for each day, the difference between apples and oranges sold. Since the data is in rows, we can use conditional aggregation to get the sum for each fruit per day, then subtract.

Approach:

  1. Use GROUP BY sale_date to aggregate sales per day.
  2. Use SUM(CASE WHEN fruit = 'apples' THEN sold_num ELSE 0 END) to get apples sold per day.
  3. Use SUM(CASE WHEN fruit = 'oranges' THEN sold_num ELSE 0 END) to get oranges sold per day.
  4. Subtract oranges from apples for the difference.
  5. Order the result by sale_date.

Code

1
2
3
4
5
6
7

SELECT
  sale_date,
  SUM(CASE WHEN fruit = 'apples' THEN sold_num ELSE 0 END) -
  SUM(CASE WHEN fruit = 'oranges' THEN sold_num ELSE 0 END) AS diff
FROM Sales
GROUP BY sale_date
ORDER BY sale_date;

Complexity

  • ⏰ Time complexity: O(N), where N is the number of rows in the Sales table.
  • 🧺 Space complexity: O(D), where D is the number of distinct sale_date values.