Count Apples and Oranges - Solution & Explanation

MediumPremiumFree on FleetCodeDatabase5 min read

Problem Statement

Table: Boxes

+--------------+------+
| Column Name  | Type |
+--------------+------+
| box_id       | int  |
| chest_id     | int  |
| apple_count  | int  |
| orange_count | int  |
+--------------+------+
box_id is the column with unique values for this table.
chest_id is a foreign key (reference column) of the chests table.
This table contains information about the boxes and the number of oranges and apples they have. Each box may include a chest, which also can contain oranges and apples.

Table: Chests

+--------------+------+
| Column Name  | Type |
+--------------+------+
| chest_id     | int  |
| apple_count  | int  |
| orange_count | int  |
+--------------+------+
chest_id is the column with unique values for this table.
This table contains information about the chests and the corresponding number of oranges and apples they have.

Write a solution to count the number of apples and oranges in all the boxes. If a box contains a chest, you should also include the number of apples and oranges it has.

The result format is in the following example.

Example 1:

Input: 
Boxes table:
+--------+----------+-------------+--------------+
| box_id | chest_id | apple_count | orange_count |
+--------+----------+-------------+--------------+
| 2      | null     | 6           | 15           |
| 18     | 14       | 4           | 15           |
| 19     | 3        | 8           | 4            |
| 12     | 2        | 19          | 20           |
| 20     | 6        | 12          | 9            |
| 8      | 6        | 9           | 9            |
| 3      | 14       | 16          | 7            |
+--------+----------+-------------+--------------+
Chests table:
+----------+-------------+--------------+
| chest_id | apple_count | orange_count |
+----------+-------------+--------------+
| 6        | 5           | 6            |
| 14       | 20          | 10           |
| 2        | 8           | 8            |
| 3        | 19          | 4            |
| 16       | 19          | 19           |
+----------+-------------+--------------+
Output: 
+-------------+--------------+
| apple_count | orange_count |
+-------------+--------------+
| 151         | 123          |
+-------------+--------------+
Explanation: 
box 2 has 6 apples and 15 oranges.
box 18 has 4 + 20 (from the chest) = 24 apples and 15 + 10 (from the chest) = 25 oranges.
box 19 has 8 + 19 (from the chest) = 27 apples and 4 + 4 (from the chest) = 8 oranges.
box 12 has 19 + 8 (from the chest) = 27 apples and 20 + 8 (from the chest) = 28 oranges.
box 20 has 12 + 5 (from the chest) = 17 apples and 9 + 6 (from the chest) = 15 oranges.
box 8 has 9 + 5 (from the chest) = 14 apples and 9 + 6 (from the chest) = 15 oranges.
box 3 has 16 + 20 (from the chest) = 36 apples and 7 + 10 (from the chest) = 17 oranges.
Total number of apples = 6 + 24 + 27 + 27 + 17 + 14 + 36 = 151
Total number of oranges = 15 + 25 + 8 + 28 + 15 + 15 + 17 = 123

Approach Overview

Problem Overview: You have two tables: Boxes and Chests. Each record stores how many apples and oranges it contains. Some boxes are placed inside a chest via chest_id. The task is to return the total number of apples and oranges across both tables.

Approach 1: Left Join + Summation (O(n + m) time, O(1) space)

Join the Boxes table with the Chests table using a LEFT JOIN on chest_id. The join lets you access fruit counts stored in both tables in a single result set. After the join, aggregate the counts using SUM() to compute the total apples and oranges. If a box is not inside a chest, the chest columns will be NULL, so functions like COALESCE() or IFNULL() convert them to zero before summing.

The key insight is that SQL aggregation can combine values from multiple related tables in a single pass. Instead of iterating separately over boxes and chests, the database engine performs the join and aggregation efficiently during query execution. This pattern appears frequently in database interview questions where related entities must be aggregated together.

The same idea applies in Pandas using merge(). Perform a left merge on chest_id, then compute totals by summing the relevant columns. Vectorized operations handle the aggregation efficiently without explicit loops. This mirrors SQL joins and aggregation operations often used in analytics pipelines and data processing workflows involving SQL or data analysis.

Recommended for interviews: The LEFT JOIN + SUM approach is the expected solution. Interviewers want to see that you understand relational joins and aggregation together. A naive approach that queries each table separately demonstrates basic understanding, but combining them with a join shows stronger SQL fluency and the ability to write concise analytical queries.

Solution

We can perform a left join on the Boxes table and the Chests table based on chest_id, and then calculate the total number of apples and oranges respectively. Note that if a box does not contain any small boxes, then the corresponding chest_id will be null. In this case, we need to consider the number of apples and oranges in the small boxes within that box to be 0.

Code

MySQL Pandas

MySQL

Pandas

Try this approach in the editor →

Detailed Complexity Analysis

Approach	Time	Space	When to Use
Separate Aggregation Queries	O(n + m)	O(1)	Simple scenario where totals from each table are computed independently
Left Join + Summation	O(n + m)	O(1)	Preferred SQL approach when related records must be aggregated together
Pandas Merge + Column Sum	O(n + m)	O(n)	When solving the problem in Python data pipelines using Pandas

Video Solution

LeetCode Medium 1715 Interview SQL Question with Detailed Explanation • Everyday Data Science • 3,689 views views

Watch 1 more video solutions →

Frequently Asked Questions

Is Count Apples and Oranges easy or hard?

The problem is usually classified as Medium because it tests understanding of SQL joins and aggregation together. Developers familiar with relational queries typically solve it quickly once they recognize that a LEFT JOIN combined with SUM() produces the totals.

Count Apples and Oranges Python/Java solution

In SQL (commonly MySQL), use a LEFT JOIN between Boxes and Chests and compute totals with SUM(). In Python, the equivalent approach uses Pandas: merge the two DataFrames on chest_id and sum the apple_count and orange_count columns after filling missing values.

How to solve Count Apples and Oranges in O(n)?

Use a LEFT JOIN to combine Boxes with Chests on chest_id and then aggregate the fruit columns using SUM(). Functions like COALESCE() or IFNULL() handle missing chest values. The database processes the join and aggregation in linear time relative to the table sizes.

What is the best approach for Count Apples and Oranges?

The most common solution uses a LEFT JOIN between the Boxes and Chests tables followed by aggregation with SUM(). This allows you to access fruit counts from both tables in a single query and compute totals efficiently. The database engine performs the join and aggregation in O(n + m) time.

Is Count Apples and Oranges asked at Google/Amazon/Meta?

This type of SQL aggregation and join problem is common in database interview rounds at companies like Amazon, Google, and Meta. Variants often test understanding of joins, NULL handling, and aggregate functions such as SUM() and COUNT().

What data structure is used in Count Apples and Oranges?

The problem uses relational database tables rather than traditional data structures. The key concepts are SQL joins, relational mapping through foreign keys like chest_id, and aggregate functions such as SUM().

What is the time complexity of Count Apples and Oranges?

The SQL solution runs in O(n + m) time where n is the number of rows in Boxes and m is the number of rows in Chests. The database scans both tables once during the join and aggregation. Space complexity is O(1) because only aggregate values are returned.

Ready to solve this problem?

Practice Count Apples and Oranges with our built-in code editor and test cases.

Practice on FleetCode

Combine Two Tables

Second Highest Salary

Problem Info

DifficultyMedium

Acceptance75.6%

Approaches1

Reading time5 min

Practice this problem

Open in Editor