Count Apples and Oranges - Practice Coding Problems

Count Apples and Oranges - Problem

Database Medium

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

Understanding the Visualization

Input Tables

Boxes and optional Chests data

LEFT JOIN

Combine boxes with their chests

Aggregate

Sum all fruit counts

Key Takeaway

🎯 Key Insight: Use LEFT JOIN to preserve all boxes, even those without chests, then aggregate with COALESCE for NULL handling

Asked in

A Amazon 12 G Google 8 M Microsoft 6

Use LEFT JOIN to combine boxes with optional chests, then SUM() with COALESCE() to handle NULL chest values and aggregate all fruit counts.

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

Common Approaches

Approach	Time	Space	Notes
✓ LEFT JOIN with Aggregation	O(n + m)	O(1)	Join boxes with chests and sum all fruit counts using COALESCE for NULL handling

LEFT JOIN with Aggregation — Algorithm Steps

Step 1: LEFT JOIN Boxes with Chests on chest_id
Step 2: Use COALESCE to handle NULL chest counts
Step 3: Sum all apple and orange counts across joined results

Visualization

Tap to expand

Step-by-Step Walkthrough

Left Join

Match boxes with chests

COALESCE

Handle NULL chest values

SUM

Aggregate all fruit counts

Code -

solution.c — C

Time & Space Complexity

Time Complexity

⏱️

O(n + m)

Linear scan of boxes (n) and chests (m) with hash join

✓ Linear Growth

Space Complexity

O(1)

Only stores aggregate results

✓ Linear Space

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

Table Schema

Input & Output

Constraints

Visualization

Related Problems

Table Schema

Common Approaches

LEFT JOIN with Aggregation — Algorithm Steps

Visualization

Code -

Time & Space Complexity

Select Compiler