Is Apples & Oranges easy or hard?

Apples & Oranges is generally considered a medium-level SQL problem. The challenge is recognizing that conditional aggregation can compute both fruit totals in a single grouped query instead of using multiple joins or subqueries.

Apples & Oranges Python/Java solution

This problem is a database query challenge rather than an algorithm implemented in Python or Java. The correct solution is a SQL query (commonly MySQL) using GROUP BY and conditional SUM to compute apples minus oranges for each date.

How to solve Apples & Oranges in O(n)?

Group rows by sale_date and use conditional SUM expressions to compute totals for apples and oranges in the same query. For example, SUM(CASE WHEN fruit = 'apples' THEN sold_num ELSE 0 END) minus SUM(CASE WHEN fruit = 'oranges' THEN sold_num ELSE 0 END). The table is scanned once, giving O(n) performance.

What is the best approach for Apples & Oranges?

The best approach uses conditional aggregation with GROUP BY. Compute SUM(CASE WHEN fruit = 'apples') and SUM(CASE WHEN fruit = 'oranges') for each sale_date, then subtract them. This processes the table in one pass with O(n) time complexity and minimal overhead.

Is Apples & Oranges asked at Google/Amazon/Meta?

SQL aggregation problems similar to Apples & Oranges appear frequently in data and backend interview rounds at companies like Amazon, Google, and Meta. Candidates are often expected to use GROUP BY, conditional aggregation, or joins to compute category differences.

What data structure is used in Apples & Oranges?

The solution relies on relational database aggregation rather than traditional data structures. SQL uses GROUP BY to logically group rows by date and SUM with conditional expressions to compute totals for each fruit category.

What is the time complexity of Apples & Oranges?

The optimal SQL solution runs in O(n) time because the database scans the table once and aggregates rows by sale_date. Space complexity is effectively O(1) beyond the grouped output since no additional structures are required.

1445. Apples & Oranges - Practice Online

Table: Sales

+---------------+---------+
| 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.

Example 1:

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).