Lookup Reservoir Levels

Problem

A water authority stores the recorded level of each reservoir at the end of selected years. Analysts submit lookup requests for a reservoir in a given year; if no level was recorded for that reservoir-year, the answer is `0`.

Table: `readings`

| Column | Type |
|---------------|------|
| reservoir_id | int |
| year | int |
| level_cm | int |

`(reservoir_id, year)` is the primary key. Each row is the recorded end-of-year level (in cm) for a reservoir.

Table: `lookups`

| Column | Type |
|---------------|------|
| reservoir_id | int |
| year | int |

`(reservoir_id, year)` is the primary key. Each row is one request.

Write a query that, for every lookup, reports `reservoir_id`, `year`, and `level_cm`. If the reservoir-year is missing from `readings`, report its level as `0`. Return the rows in any order.

Tables

Example rows — the live problem includes the full dataset.

Expected output

Your answer should return 7 rows with the columns reservoir_id, year, level_cm.

Starter code (SQL)

SELECT *
FROM readings;

Related SQL questions

• Above-Average CampusesEasy
• Adjacent Open Parking SpotsEasy
• App Ad Click-Through RateEasy
• Asset Labels With a Three-Digit CodeEasy
• Average Listening Sessions per ListenerEasy
• Average Nightly Rate Earned per RoomEasy