US Car Sales: Back to Normal Yet?

How it works — every step, readable

1. 01Light-vehicle sales, monthly (annual rate, millions)

   SELECT * FROM read_csv_auto('FRED_ALTSALES.csv', header=true)

2. 02Sales and the 12-month average

   computes running / windowed totals · sorts the output

   SELECT date, altsales_m AS sales_annual_rate_m,
     ROUND(AVG(altsales_m) OVER (ORDER BY date ROWS BETWEEN 11 PRECEDING AND CURRENT ROW), 2) AS avg_12_month_m
   FROM input_1 ORDER BY date

3. 03The punch lines

   buckets values by condition · computes running / windowed totals · appends result sets (e.g. a TOTAL row)

   WITH s AS (SELECT date, altsales_m AS v, LAG(altsales_m, 12) OVER (ORDER BY date) AS v12 FROM input_1),
   b AS (SELECT arg_max(v, date) AS now_v, arg_max(v12, date) AS last_yr, max(date) AS d1,
     avg(v) FILTER (WHERE year(date) = 2019) AS n2019, min(v) AS low, arg_min(date, v) AS low_d,
     max(v) FILTER (WHERE date >= DATE '2020-06-01') AS hi, arg_max(date, v) FILTER (WHERE date >= DATE '2020-06-01') AS hi_d FROM s)
   SELECT area, finding, next_step FROM (
     SELECT 1 AS o, 'VERDICT' AS area, CASE WHEN now_v >= n2019 THEN '▲ BACK TO NORMAL — ' ELSE '▼ STILL BELOW NORMAL — ' END || printf('%.1f', now_v) || 'M a year vs ' || printf('%.1f', n2019) || 'M before the pandemic' AS finding, '' AS next_step FROM b
     UNION ALL SELECT 2, 'Selling now', printf('%.1f', now_v) || ' million a year', 'Annual rate for ' || strftime(d1, '%B %Y') FROM b
     UNION ALL SELECT 3, 'Pre-pandemic normal', printf('%.1f', n2019) || ' million', '2019 average — today is ' || printf('%.0f%%', abs(100 * (now_v / n2019 - 1))) || CASE WHEN now_v >= n2019 THEN ' above it' ELSE ' below it' END FROM b
     UNION ALL SELECT 4, 'The collapse', printf('%.1f', low) || ' million in ' || strftime(low_d, '%B %Y'), 'Half the market vanished in weeks' FROM b
     UNION ALL SELECT 5, 'Best month since', printf('%.1f', hi) || ' million in ' || strftime(hi_d, '%B %Y'), 'Since June 2020' FROM b
     UNION ALL SELECT 6, 'Versus a year ago', printf('%+.1f%%', 100 * (now_v / last_yr - 1)), 'Same month last year: ' || printf('%.1f', last_yr) || 'M' FROM b
   ) ORDER BY o