The Price of a Dozen Eggs

How it works — every step, readable

1. 01Average price, a dozen large eggs (US cities)

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

2. 02Assumptions — edit these

   SELECT * FROM (VALUES ('Dozens your household buys a month', 3, 'dozen')) AS t(assumption, value, unit)

3. 03Price per dozen, monthly

   filters to the relevant rows · sorts the output

   SELECT date, apu0000708111 AS price_per_dozen FROM input_1 WHERE apu0000708111 IS NOT NULL ORDER BY date

4. 04The punch lines

   buckets values by condition · appends result sets (e.g. a TOTAL row) · filters to the relevant rows

   WITH e AS (SELECT date, apu0000708111 AS p FROM input_1 WHERE apu0000708111 IS NOT NULL),
   b AS (SELECT arg_max(p, date) AS now_p, max(date) AS d1, max(p) AS hi, arg_max(date, p) AS hi_d, avg(p) FILTER (WHERE year(date) = 2019) AS p2019 FROM e),
   a AS (SELECT MAX(CASE WHEN assumption = 'Dozens your household buys a month' THEN CAST(value AS DOUBLE) END) AS dz FROM input_2)
   SELECT area, finding, next_step FROM (
     SELECT 1 AS o, 'VERDICT' AS area, 'A DOZEN EGGS: ' || printf('$%.2f', now_p) || ' — THE RECORD WAS ' || printf('$%.2f', hi) || ' IN ' || upper(strftime(hi_d, '%B %Y')) AS finding, '' AS next_step FROM b
     UNION ALL SELECT 2, 'Today', printf('$%.2f', now_p) || ' a dozen', strftime(d1, '%B %Y') || ' average' FROM b
     UNION ALL SELECT 3, 'The record', printf('$%.2f', hi) || ' in ' || strftime(hi_d, '%B %Y'), printf('%.0f%%', 100 * (1 - now_p / hi)) || ' below it now' FROM b
     UNION ALL SELECT 4, 'Before the pandemic', printf('$%.2f', p2019) || ' in 2019', printf('%+.0f%%', 100 * (now_p / p2019 - 1)) || ' since' FROM b
     UNION ALL SELECT 5, 'Your eggs this year', printf('$%.0f', now_p * dz * 12), CAST(CAST(dz AS INTEGER) AS VARCHAR) || ' dozen a month — ' || printf('$%.0f', (now_p - p2019) * dz * 12) || ' more than at 2019 prices' FROM b, a
   ) ORDER BY o