Terrain Rainwater Retention Analysis

A hydrology analytics team stores landscape elevation bars and needs to estimate how much rainwater the profile can hold.

Table: Heights

• id: unique position index along the terrain
• height: elevation of the bar at that position

Interpretation:

• Each bar has width = 1 unit.
• Water can accumulate in valleys if taller bars exist on both sides.

Task: Compute the total units of rainwater retained by the full terrain profile.

Per-position principle:

• Let left_max be the tallest bar from the start up to the current id.
• Let right_max be the tallest bar from the current id to the end.
• Water level at the position is min(left_max, right_max).
• Trapped water contribution is max(water_level - height, 0).

Output requirements:

• Return one row with one column in this exact order: total_trapped_water

Supported submission environments:

• SQL (SQLite 3.27.2)
• Pandas (Python)

Heights:
| id | height |
|----|--------|
| 1  | 0      |
| 2  | 1      |
| 3  | 0      |
| 4  | 2      |
| 5  | 1      |
| 6  | 0      |
| 7  | 1      |
| 8  | 3      |
| 9  | 2      |
| 10 | 1      |
| 11 | 2      |
| 12 | 1      |

[
  {"total_trapped_water":6}
]

Heights:
| id | height |
|----|--------|
| 1  | 4      |
| 2  | 2      |
| 3  | 0      |
| 4  | 3      |
| 5  | 2      |
| 6  | 5      |

[
  {"total_trapped_water":9}
]

Heights:
| id | height |
|----|--------|
| 1  | 3      |
| 2  | 3      |
| 3  | 3      |
| 4  | 3      |

[
  {"total_trapped_water":0}
]