LeetCode 1445. Apples & Oranges

Description

https://leetcode.com/problems/apples-oranges/

SQL Schema

Table: Sales

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

Write an SQL query to report the difference between number of apples and oranges sold each day.

Return the result table ordered by sale_date in format (‘YYYY-MM-DD’).

The query result format is in the following example:

Sales

Explanation

JOIN on table on the date column and then calculate the sales difference.

SQL Solution

# Write your MySQL query statement below
select a.sale_date as SALE_DATE, a.sold_num - o.sold_num as DIFF from 
((select * from Sales where fruit = 'apples') a
join 
(select * from Sales where fruit = 'oranges') o 
on a.sale_date = o.sale_date)