LeetCode 1445. Apples and Oranges SQL Solution
Problem
LeetCode SQL Problem
- Apples and Oranges
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 |
Solution - PIVOT Data with 2 CASE Statements
In the CTE, we group data by sale_date and then use 2 CASE statements to pivot sold number of apples and orages into a separate column.
Now we have sold_num of apples and oranges for each sale_date same row, we can easily calculate the difference.
- MySQL
- TSQL
WITH fruit_sales_analysis
AS (
SELECT S.sale_date
,SUM(CASE S.fruit
WHEN 'apples'
THEN S.sold_num
ELSE 0
END) AS apples_sold_num
,SUM(CASE S.fruit
WHEN 'oranges'
THEN S.sold_num
ELSE 0
END) AS oranges_sold_num
FROM Sales AS S
GROUP BY S.sale_date
)
SELECT sale_date
,apples_sold_num - oranges_sold_num AS diff
FROM fruit_sales_analysis
Query Output
| sale_date | diff |
|---|---|
| 2020-05-01 | 2 |
| 2020-05-02 | 0 |
| 2020-05-03 | 20 |
| 2020-05-04 | -1 |
WITH fruit_sales_analysis
AS (
SELECT S.sale_date
,SUM(CASE S.fruit
WHEN 'apples'
THEN S.sold_num
ELSE 0
END) AS apples_sold_num
,SUM(CASE S.fruit
WHEN 'oranges'
THEN S.sold_num
ELSE 0
END) AS oranges_sold_num
FROM Sales AS S
GROUP BY S.sale_date
)
SELECT sale_date
,apples_sold_num - oranges_sold_num AS diff
FROM fruit_sales_analysis
Query Output
| sale_date | diff |
|---|---|
| 2020-05-01 | 2 |
| 2020-05-02 | 0 |
| 2020-05-03 | 20 |
| 2020-05-04 | -1 |
Solution - PIVOT Data with 1 CASE Statement
Looking closely at the solution above, we could eliminate the CTE and use only 1 CASE statement to calculate sold_num difference between apples and oranges each day.
The trick is to use negative sold_num for oranges.
- MySQL
- TSQL
SELECT S.sale_date
,SUM(CASE S.fruit
WHEN 'apples'
THEN S.sold_num
WHEN 'oranges'
THEN - S.sold_num
ELSE 0
END) AS diff
FROM Sales AS S
GROUP BY S.sale_date
Query Output
| sale_date | diff |
|---|---|
| 2020-05-01 | 2 |
| 2020-05-02 | 0 |
| 2020-05-03 | 20 |
| 2020-05-04 | -1 |
SELECT S.sale_date
,SUM(CASE S.fruit
WHEN 'apples'
THEN S.sold_num
WHEN 'oranges'
THEN - S.sold_num
ELSE 0
END) AS diff
FROM Sales AS S
GROUP BY S.sale_date
Query Output
| sale_date | diff |
|---|---|
| 2020-05-01 | 2 |
| 2020-05-02 | 0 |
| 2020-05-03 | 20 |
| 2020-05-04 | -1 |
Solution - PIVOT Data with Built-In PIVOT Operator
If you're using SQL Server, you could utilize the built-in PIVOT operator to pivot sold_num of apples and oranges into 2 separate columns.
Note that MySQL does not have built-in PIVOT operator.
- TSQL
SELECT sale_date
,apples - oranges AS diff
FROM Sales
PIVOT(SUM(sold_num) FOR fruit IN (
apples
,oranges
)) AS PivotTable
Query Output
| sale_date | diff |
|---|---|
| 2020-05-01 | 2 |
| 2020-05-02 | 0 |
| 2020-05-03 | 20 |
| 2020-05-04 | -1 |
Solution - Self Join
If you're comfortable with self join, you could use this technique to put sold_num of apples and oranges for each sale_date to calculate the difference.
- MySQL
- TSQL
SELECT A.sale_date
,A.sold_num - O.sold_num AS diff
FROM Sales AS A
INNER JOIN Sales AS O ON A.sale_date = O.sale_date
AND A.fruit = 'apples'
AND O.fruit = 'oranges'
SELECT A.sale_date
,A.sold_num - O.sold_num AS diff
FROM Sales AS A
INNER JOIN Sales AS O ON A.sale_date = O.sale_date
AND A.fruit = 'apples'
AND O.fruit = 'oranges'
Query Output
| sale_date | diff |
|---|---|
| 2020-05-01 | 2 |
| 2020-05-02 | 0 |
| 2020-05-03 | 20 |
| 2020-05-04 | -1 |