Problem

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.

Examples

Example 1:

 1
 2
 3
 4
 5
 6
 7
 8
 9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
28
29
30
31
32
33
34
35
36
37
38
39

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

Solution

Method 1 – SQL Join and Aggregation 1

Intuition

To count the total apples and oranges, we need to sum the apples and oranges in each box, and if a box contains a chest, also add the apples and oranges from the chest. We can do this by joining the Boxes and Chests tables and using COALESCE to handle boxes without a chest.

Approach

  1. LEFT JOIN the Boxes table with the Chests table on chest_id.
  2. For each box, sum its apple_count and orange_count with the corresponding chest’s apple_count and orange_count (if any).
  3. Aggregate the sums over all boxes.
  4. Return the total apple_count and orange_count.

Code

1
2
3
4
5

SELECT
  SUM(b.apple_count + COALESCE(c.apple_count, 0)) AS apple_count,
  SUM(b.orange_count + COALESCE(c.orange_count, 0)) AS orange_count
FROM Boxes b
LEFT JOIN Chests c ON b.chest_id = c.chest_id;
1
2
3
4
5

SELECT
  SUM(b.apple_count + COALESCE(c.apple_count, 0)) AS apple_count,
  SUM(b.orange_count + COALESCE(c.orange_count, 0)) AS orange_count
FROM Boxes b
LEFT JOIN Chests c ON b.chest_id = c.chest_id;
1
2
3
4
5
6
7
8

def count_apples_and_oranges(boxes: pd.DataFrame, chests: pd.DataFrame) -> pd.DataFrame:
    merged = boxes.merge(chests, how='left', left_on='chest_id', right_on='chest_id', suffixes=('', '_chest'))
    merged['apple_count_total'] = merged['apple_count'] + merged['apple_count_chest'].fillna(0)
    merged['orange_count_total'] = merged['orange_count'] + merged['orange_count_chest'].fillna(0)
    return pd.DataFrame({
        'apple_count': [merged['apple_count_total'].sum()],
        'orange_count': [merged['orange_count_total'].sum()]
    })

Complexity

  • ⏰ Time complexity: O(n + m), where n is the number of boxes and m is the number of chests, as each row is processed once in the join and aggregation.
  • 🧺 Space complexity: O(1), as only a constant amount of space is needed for the result.