Cacher is the code snippet organizer for pro developers

--Map value according to conditions
-- applies value of the first TRUE condition (from top to bottom of CASE statement)
---

SELECT report_code, year, month, day, wind_spped,

CASE
    WHEN wind_speed >= 40 THEN 'HIGH'
    WHEN wind_speed >= 30 AND wind_speed < 40 THEN 'MODERATE'
    ELSE 'LOW'
END as wind_severity,
FROM station_data


-- Grouping by mapped data
SELECT year,
CASE
    WHEN wind_speed >= 40 THEN 'HIGH'
    WHEN wind_speed >= 30 THEN 'MODERATE'
    ELSE 'LOW'
END as wind_severity,
COUNT(*) as record_count
FROM station_data
GROUP BY 1, 2


-- Zero/Null Trick
SELECT year, month,
SUM(CASE WHEN tornado = 1 THEN precipitation ELSE 0 END) as tornado_precipitation,
SUM(CASE WHEN tornado = 0 THEN precipitation ELSE 0 END) as non_tornado_precipitation
FROM data
GROUP BY year, month


SELECT month,
AVG(CASE WHEN rain OR hail THEN temp ELSE NULL END) AS avg_pre_temp,
AVG(CASE WHEN NOT (rain OR hail) THEN temp ELSE null END) as avg_non_pre_temp,
FROM data
WHERE year > 2000
GROUP BY month