Intermediate · Lesson 8 of 14
CASE and COALESCE
Computed columns and friendly NULLs.
CASE builds new columns
CASE is a giant if-else that produces a value per row. Use it to bucket, label, or count conditionally, like COUNT(CASE WHEN x THEN 1 END).
Conditional counting
Counting inside CASE is your main tool for rates: clicks per impressions, cancellations per trips. Multiply by 100.0 to keep decimals.
COALESCE for NULLs
COALESCE(a, b, c) returns the first non-NULL value. It stops NULLs from silently dropping rows or producing totals of NULL.
Examples
Label with CASE
A computed label on each row.
SELECT trip_id, fare, CASE WHEN fare >= 15 THEN 'Premium ride' ELSE 'Standard ride' END AS class FROM trips LIMIT 10;Conditional count
Only cancelled rows contribute to the count.
SELECT COUNT(CASE WHEN status <> 'completed' THEN 1 END) AS cancelled FROM trips;COALESCE fills gaps
NULL referral codes fall back to a friendly label.
SELECT city, COALESCE(referral_code, 'no code') AS referral FROM riders LIMIT 5;