Bucket Daily Rainfall

Problem

A weather station records the total rainfall for each day.

Table: rain_log

| Column Name | Type |
|-------------|------|
| reading_id | int |
| rain_mm | int |

reading_id is the primary key for this table. Each row holds one day's total rainfall in millimetres.

Classify each day into one of three bands:
- 'Dry': rainfall strictly less than 10 mm.
- 'Moderate': rainfall in the inclusive range [10, 50] mm.
- 'Heavy': rainfall strictly greater than 50 mm.

Return one row per band with band and the number of days in it (call it day_count). The result must contain all three bands; a band with no days reports 0. Order does not matter.

Tables

Example rows — the live problem includes the full dataset.

Expected output

Your answer should return 3 rows with the columns band, day_count.

Starter code (SQL)

SELECT *
FROM rain_log;

Related SQL questions

• 3-Day Tweet Rolling AverageMedium
• 7-Day vs 28-Day Rolling Avg DivergenceMedium
• A/B Test Sample Ratio Mismatch (SRM) CheckMedium
• Accounts Streaming From Two Devices at OnceMedium
• Ad Campaign Funnel AnalysisMedium
• Agent Resolution RateMedium