Count Apples and Oranges - Problem

You are given two tables: Boxes and Chests. Each box may contain a chest, and both boxes and chests can contain apples and oranges.

Write a SQL query to count the total number of apples and oranges across all boxes. If a box contains a chest, you must include the fruits from both the box and its chest in the total count.

The result should return two columns:

  • apple_count - Total number of apples
  • orange_count - Total number of oranges

Table Schema

Boxes
Column Name Type Description
box_id PK int Unique identifier for each box
chest_id int Foreign key reference to Chests table (nullable)
apple_count int Number of apples in the box
orange_count int Number of oranges in the box
Primary Key: box_id
Chests
Column Name Type Description
chest_id PK int Unique identifier for each chest
apple_count int Number of apples in the chest
orange_count int Number of oranges in the chest
Primary Key: chest_id

Input & Output

Example 1 — Boxes with and without chests
Input Tables:
Boxes
box_id chest_id apple_count orange_count
1 1 1 4
2 4 4
Chests
chest_id apple_count orange_count
1 3 1
Output:
apple_count orange_count
8 9
💡 Note:

Box 1 contains 1 apple + 4 oranges, plus chest 1 with 3 apples + 1 orange = 4 apples + 5 oranges from box 1. Box 2 contains 4 apples + 4 oranges with no chest. Total: 8 apples + 9 oranges.
Example 2 — All boxes have chests
Input Tables:
Boxes
box_id chest_id apple_count orange_count
1 1 1 1
2 2 2 1
Chests
chest_id apple_count orange_count
1 3 1
2 2 4
Output:
apple_count orange_count
8 7
💡 Note:

Box 1: (1+3) apples + (1+1) oranges = 4 apples + 2 oranges. Box 2: (2+2) apples + (1+4) oranges = 4 apples + 5 oranges. Total: 8 apples + 7 oranges.
Example 3 — No chests
Input Tables:
Boxes
box_id chest_id apple_count orange_count
1 2 2
2 3 3
Chests
chest_id apple_count orange_count
Output:
apple_count orange_count
5 5
💡 Note:

No boxes contain chests, so we only count fruits directly in boxes: 2+3 = 5 apples, 2+3 = 5 oranges.

Constraints

  • 1 ≤ box_id ≤ 1000
  • 0 ≤ apple_count, orange_count ≤ 1000
  • chest_id can be NULL if box contains no chest
  • Each chest_id in Boxes table exists in Chests table (when not NULL)

Visualization

Tap to expand
Count Apples and Oranges INPUT Box 1 x3 x2 Box 2 x1 x4 Chest A:2 O:1 Box 3 x4 x3 boxes = [ {apples:3, oranges:2}, {apples:1, oranges:4, chest}, {apples:4, oranges:3}] ALGORITHM STEPS 1 Initialize Counters totalApples = 0 totalOranges = 0 2 Loop Through Boxes for each box in boxes: add box fruits 3 Check for Chest if box has chest: add chest fruits 4 Return Total return totalApples + totalOranges Running Total: Box1: 3+2 = 5 Box2: 1+4+2+1 = 8 Box3: 4+3 = 7 FINAL RESULT Total Fruits 10 Apples 10 Oranges OUTPUT: 20 Breakdown: Apples: 3+1+2+4 = 10 Oranges: 2+4+1+3 = 10 Total: 10 + 10 = 20 OK Key Insight: When counting fruits, always check if a box contains a chest. If it does, recursively include the chest's contents. This ensures we capture all nested fruits in our total count. Time Complexity: O(n) where n = total boxes + chests | Space Complexity: O(1) TutorialsPoint - Count Apples and Oranges | Optimal Solution

Related Problems

Asked in
Amazon 12 Google 8 Microsoft 6
23.5K Views
Medium Frequency
~8 min Avg. Time
892 Likes
Ln 1, Col 1
Smart Actions
💡 Explanation
AI Ready
💡 Suggestion Tab to accept Esc to dismiss
// Output will appear here after running code
Code Editor Closed
Click the red button to reopen