Puzzleshot #014 - CASE-ing a Duck

raw data→duckdb→result

In Puzzleshot 013 we normalized region values using awk before passing the data to DuckDB. This week — let’s try without awk, but entirely inside DuckDB.

Same data as before:

order_id,customer_id,dealer_id,region,order_total,order_date,product_id,quantity,status,sales_rep
5001,8801,1001,N,124500.00,2024-01-01,X9100,2,complete,Jones
5002,8802,1003,south,87250.00,2024-01-02,CH95,1,pending,Smith
5003,8803,1001,EAST,203000.00,2024-01-03,X9100,3,complete,Davis
5004,8804,1002,W,156750.00,2024-01-04,CH95,1,complete,Jones
5005,8805,1001,North,98500.00,2024-01-05,X9100,2,complete,Brown
5006,8806,1002,SW,445000.00,2024-01-06,CH95,1,complete,Wilson

Valid region values are N, S, E, and W. Everything else needs to be normalized to a single character abbreviation or excluded from results.

The challenge:

Using only DuckDB, normalize the region column and produce total order value per region — and exclude any rows that can’t be normalized to a valid region code.

Things to consider:

How do you handle case-insensitive matching in DuckDB SQL?
How does CASE WHEN handle values that don’t match any condition?
How do you exclude unmatched rows from your aggregation?
How does this approach compare to the awk solution from #013?

Reveal solution

This puzzle’s challenge is the same normalization problem as #013 — but solved entirely within DuckDB using CASE WHEN instead of awk preprocessing.

A solution:

duckdb -c "
  SELECT
    CASE upper(substr(region, 1, 1))
      WHEN 'N' THEN 'N'
      WHEN 'S' THEN 'S'
      WHEN 'E' THEN 'E'
      WHEN 'W' THEN 'W'
      ELSE NULL
    END as region_normalized,
    sum(order_total) as total_value
  FROM read_csv('sales.csv.gz')
  WHERE CASE upper(substr(region, 1, 1))
      WHEN 'N' THEN 'N'
      WHEN 'S' THEN 'S'
      WHEN 'E' THEN 'E'
      WHEN 'W' THEN 'W'
      ELSE NULL
    END IS NOT NULL
  GROUP BY region_normalized
  ORDER BY total_value DESC
"

Or more cleanly using a subquery to avoid repeating the CASE expression:

duckdb -c "
  SELECT
    region_normalized,
    sum(order_total) as total_value
  FROM (
    SELECT
      order_total,
      CASE upper(substr(region, 1, 1))
        WHEN 'N' THEN 'N'
        WHEN 'S' THEN 'S'
        WHEN 'E' THEN 'E'
        WHEN 'W' THEN 'W'
        ELSE NULL
      END as region_normalized
    FROM read_csv('sales.csv.gz')
  )
  WHERE region_normalized IS NOT NULL
  GROUP BY region_normalized
  ORDER BY total_value DESC
"

Details:

upper(substr(region, 1, 1)) extracts and uppercases the first character of the region field — the same logic as #013’s awk solution, just in SQL syntax.
CASE WHEN maps each valid first character to its normalized abbreviation. Any value that doesn’t match falls through to ELSE NULL.
WHERE region_normalized IS NOT NULL filters out the unmatched rows — equivalent to awk’s silent row dropping from #013. The subquery version is cleaner since it avoids repeating the CASE expression in both SELECT and WHERE.

The SW gotcha:

Same as #013 — SW maps to S since we’re only looking at the first character. This also extends in other areas if there are entries such as “None”, “Null”, “Empty” in that field. The same caveat applies — know your data before choosing this approach.

Comparing #013 and #014:

The awk approach filters and normalizes before DuckDB sees the data — useful when you want to keep SQL clean or when preprocessing is part of a larger pipeline. The CASE WHEN approach keeps everything in SQL — more readable for SQL-native engineers and easier to maintain in a single place. Neither is universally better. The right choice depends on your environment and what the rest of your pipeline looks like.