Count Apples and Oranges - Problem
Imagine you're working at a fruit distribution warehouse where fruits are stored in boxes, and some boxes contain chests for additional storage capacity.
You have two tables:
- Boxes table: Contains information about each box including its ID, optional chest ID, and direct fruit counts
- Chests table: Contains information about chests and their fruit counts
Your task is to calculate the total number of apples and oranges across all boxes. Remember: if a box contains a chest, you must add both the box's fruits and the chest's fruits to get the complete count.
Goal: Return a single row with the total apple count and total orange count from all boxes (including their chests if any).
Input & Output
example_1.sql — Basic Example
$
Input:
Boxes: [(1,100,1,4), (2,null,2,3)]
Chests: [(100,5,6)]
›
Output:
apple_count: 8, orange_count: 13
💡 Note:
Box 1 has chest 100: (1+5=6 apples, 4+6=10 oranges). Box 2 has no chest: (2 apples, 3 oranges). Total: 6+2=8 apples, 10+3=13 oranges
example_2.sql — No Chests
$
Input:
Boxes: [(1,null,3,2), (2,null,1,4)]
Chests: []
›
Output:
apple_count: 4, orange_count: 6
💡 Note:
No boxes have chests, so we just sum the box contents: 3+1=4 apples, 2+4=6 oranges
example_3.sql — All Have Chests
$
Input:
Boxes: [(1,200,0,1), (2,201,1,0)]
Chests: [(200,10,5), (201,2,3)]
›
Output:
apple_count: 13, orange_count: 9
💡 Note:
Box 1+chest 200: 0+10=10 apples, 1+5=6 oranges. Box 2+chest 201: 1+2=3 apples, 0+3=3 oranges. Total: 10+3=13 apples, 6+3=9 oranges
Visualization
Tap to expand
Understanding the Visualization
1
Identify Data Structure
Boxes table with optional chest references, Chests table with fruit counts
2
LEFT JOIN Operation
Connect each box with its chest (if any), keeping all boxes
3
Handle Missing Chests
Use COALESCE to treat NULL chest values as 0
4
Sum All Fruits
Add box fruits + chest fruits for each row, then sum totals
Key Takeaway
🎯 Key Insight: LEFT JOIN preserves all boxes while optionally including chest data, and COALESCE handles missing chests gracefully by treating them as 0 fruit contributions.
Time & Space Complexity
Time Complexity
O(n log m)
JOIN operation with potential index lookup where n is boxes and m is chests
⚡ Linearithmic
Space Complexity
O(1)
Only storing final aggregated results
✓ Linear Space
Constraints
- 1 ≤ box_id ≤ 103
- 1 ≤ chest_id ≤ 103
- 0 ≤ apple_count, orange_count ≤ 104
- Each box can contain at most one chest
- chest_id in Boxes can be NULL (indicating no chest)
💡
Explanation
AI Ready
💡 Suggestion
Tab
to accept
Esc
to dismiss
// Output will appear here after running code