Nested Fruit Container Inventory Totals

Medium

A warehouse analytics team tracks fruit inventory at two container levels. Table: boxes box_id: unique identifier of each top-level shipping box chest_id: optional reference to a nested chest placed inside the box apple_count: apples directly stored in the box body orange_count: oranges directly stored in the box body Table: chests chest_id: unique identifier for each chest apple_count: apples stored in that chest orange_count: oranges stored in that chest Task: Compute the total number of apples and oranges across all boxes. Counting rules: Every box contributes its own apple_count and orange_count. If a box has chest_id, include that chest's apple_count and orange_count for that box. Chests that are not referenced by any box must not contribute. Output requirements: Return exactly one row. Return columns in this exact order: apple_count orange_count Supported submission environments: SQL (SQLite 3.27.2) Pandas (Python)

Example 1

Input

boxes:
| box_id | chest_id | apple_count | orange_count |
|--------|----------|-------------|--------------|
| 1      | null     | 5           | 2            |
| 2      | 10       | 4           | 3            |
| 3      | 10       | 1           | 7            |

chests:
| chest_id | apple_count | orange_count |
|----------|-------------|--------------|
| 10       | 6           | 8            |

Output

[
  {"apple_count":22,"orange_count":28}
]

Explanation

Box 1 contributes only its own fruit. Boxes 2 and 3 each add chest 10 values on top of their own counts.

Example 2

Input

boxes:
| box_id | chest_id | apple_count | orange_count |
|--------|----------|-------------|--------------|
| 11     | 4        | 0           | 9            |
| 12     | null     | 10          | 0            |
| 13     | 8        | 3           | 1            |

chests:
| chest_id | apple_count | orange_count |
|----------|-------------|--------------|
| 4        | 2           | 2            |
| 8        | 5           | 4            |
| 99       | 100         | 100          |

Output

[
  {"apple_count":20,"orange_count":16}
]

Explanation

Chest 99 is unreferenced and must be ignored. Only chest 4 and chest 8 are added through linked boxes.

Example 3

Input

boxes:
| box_id | chest_id | apple_count | orange_count |
|--------|----------|-------------|--------------|
| 101    | null     | 7           | 3            |
| 102    | null     | 0           | 5            |
| 103    | null     | 4           | 0            |

chests:
| chest_id | apple_count | orange_count |
|----------|-------------|--------------|
| 50       | 9           | 9            |

Output

[
  {"apple_count":11,"orange_count":8}
]

Explanation

With all chest_id values null, totals come entirely from the boxes table.

Constraints

1 <= number of rows in boxes <= 200000
0 <= number of rows in chests <= 120000
boxes.box_id is unique
chests.chest_id is unique
boxes.chest_id is either null or references chests.chest_id
0 <= apple_count, orange_count <= 1000000000 in both tables
Return exactly one row and exactly two columns: apple_count, orange_count
For SQL submissions, use SQLite 3.27.2 behavior
For Pandas submissions, return a DataFrame with columns: apple_count, orange_count

boxes: | box_id | chest_id | apple_count | orange_count | |--------|----------|-------------|--------------| | 1 | null | 5 | 2 | | 2 | 10 | 4 | 3 | | 3 | 10 | 1 | 7 | chests: | chest_id | apple_count | orange_count | |----------|-------------|--------------| | 10 | 6 | 8 |

boxes: | box_id | chest_id | apple_count | orange_count | |--------|----------|-------------|--------------| | 11 | 4 | 0 | 9 | | 12 | null | 10 | 0 | | 13 | 8 | 3 | 1 | chests: | chest_id | apple_count | orange_count | |----------|-------------|--------------| | 4 | 2 | 2 | | 8 | 5 | 4 | | 99 | 100 | 100 |

boxes: | box_id | chest_id | apple_count | orange_count | |--------|----------|-------------|--------------| | 101 | null | 7 | 3 | | 102 | null | 0 | 5 | | 103 | null | 4 | 0 | chests: | chest_id | apple_count | orange_count | |----------|-------------|--------------| | 50 | 9 | 9 |