SQL Interview Questions – Apples and Oranges

Problem Description –

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

Return the result table ordered by sale_date.

The query result format is in the following example.

Difficulty Level – Medium

Problem Link – Apples and Oranges

Solution –

SELECT
    sale_date,
    total_apples_sold - total_oranges_sold as diff
FROM (
SELECT
    sale_date,
    SUM(CASE WHEN fruit = 'apples' THEN sold_num ELSE 0 END) as total_apples_sold,
    SUM(CASE WHEN fruit = 'oranges' THEN sold_num ELSE 0 END) as total_oranges_sold
FROM Sales
GROUP BY 1
    ) x
ORDER BY 1