Chapter 6 · 6 levels

CASE, dates and strings

Bucket values with CASE, build rates with conditional aggregation, group by month, measure time between dates and clean messy text.

Start

Raw columns rarely match the categories people think in. Finance wants price bands, not prices. Sales wants months, not timestamps. Marketing wants “CA”, not ' ca'. This chapter is about reshaping values inside a query.

You will bucket rows with CASE, compute several rates in one pass with conditional aggregation, roll timestamps up to months with DATE_TRUNC, measure the days between two dates, and clean inconsistent text with TRIM, UPPER, LOWER and COALESCE. Date and string functions are where SQL dialects differ most, so each lesson notes the Postgres, MySQL and BigQuery spelling. The boss level is a customer-base profile for marketing.

Levels

  1. Products by price band

    from Jessica Turner, Merchandising Manager

    case when, bucketing, group by

    Code

  2. Cancellation rate by channel

    from Megan Collins, Head of Sales

    conditional aggregation, case when, rates, filter clause

    Code

  3. Monthly revenue for 2025

    from Brian Mitchell, Finance Analyst

    date trunc, time series, group by, revenue

    Code

  4. How long until items come back?

    from Rachel Adams, Customer Support Lead

    date arithmetic, date diff, cast, multi join

    Code

  5. Customers by state, cleaned up

    from Ryan Cooper, Regional Sales Manager

    trim, upper, lower, data cleaning, group by

    Code

  6. Boss: customer base profile

    from Jordan Reyes, Head of Marketing

    date trunc, conditional aggregation, split part, lower, coalesce

    Boss · 3 steps