Soil Moisture Classification per Greenhouse
Problem
Table: `Greenhouse`
```text
+---------------+---------+
| Column | Type |
+---------------+---------+
| house_id | int |
| house_name | varchar |
+---------------+---------+
house_id is the primary key. Each row names one greenhouse.
```
Table: `MoistureLog`
```text
+---------------+---------+
| Column | Type |
+---------------+---------+
| house_id | int |
| moisture_pct | int |
| reading_day | date |
+---------------+---------+
(house_id, reading_day) is the primary key. moisture_pct is the soil
moisture percentage recorded in that greenhouse on that day.
```
For every greenhouse, compute the **average moisture** of its readings taken during **March 2021** (from `2021-03-01` to `2021-03-31` inclusive) and classify it under the column `moisture_label`:
- `'Dry'` when the average moisture is **30 or below**,
- `'Soaked'` when the average moisture is **60 or above**,
- `'Balanced'` for everything in between.
Return `house_name` and `moisture_label`. Rows may be in any order. Only greenhouses with at least one March reading appear.
**Example**
```text
Greenhouse:
+----------+------------+
| house_id | house_name |
+----------+------------+
| 1 | Fern Wing |
| 2 | Orchid Bay |
| 3 | Cactus Row |
+----------+------------+
MoistureLog:
+----------+--------------+-------------+
| house_id | moisture_pct | reading_day |
+----------+--------------+-------------+
| 1 | 20 | 2021-03-04 |
| 1 | 28 | 2021-03-18 |
| 2 | 45 | 2021-03-09 |
| 2 | 55 | 2021-03-22 |
| 3 | 70 | 2021-03-11 |
+----------+--------------+-------------+
Output:
+------------+----------------+
| house_name | moisture_label |
+------------+----------------+
| Fern Wing | Dry |
| Orchid Bay | Balanced |
| Cactus Row | Soaked |
+------------+----------------+
```
Fern Wing averages (20+28)/2 = 24 → Dry. Orchid Bay averages 50 → Balanced. Cactus Row averages 70 → Soaked.
Tables
Example rows — the live problem includes the full dataset.
| house_id | house_name |
|---|---|
| 1 | Fern Wing |
| 2 | Orchid Bay |
| 3 | Cactus Row |
| house_id | moisture_pct | reading_day |
|---|---|---|
| 1 | 20 | 2021-03-04 |
| 1 | 28 | 2021-03-18 |
| 2 | 45 | 2021-03-09 |
Expected output
Your answer should return 3 rows with the columns house_name, moisture_label.
Starter code (SQL)
SELECT *
FROM Greenhouse;Solve this SQL question free
Write SQL and run it instantly in your browser — even on your phone. No signup needed to try.
Solution & explanation
Create a free account to unlock the optimal solution, a step-by-step explanation, and the hidden test cases that grade your answer.
Sign up free to unlock