In December and January, plants shipped to cold states arrive two days later.

Winter plant delivery by climate

WITH plant_orders AS (
  SELECT DISTINCT oi.order_id
  FROM order_items oi JOIN products p ON p.id = oi.product_id
  WHERE p.category = 'plant'
)
SELECT CASE WHEN c.state IN ('NY','PA','IL','OH','MI','NJ','WA','MA','IN','MO','WI','CO','MN',
                             'KY','OR','CT','UT','IA','KS','NE','WV','ID','NH','ME','RI','MT',
                             'SD','ND','AK','VT','WY') THEN 'cold' ELSE 'warm' END AS climate,
       CASE WHEN strftime('%m', s.shipped_at) IN ('12', '01') THEN 'dec-jan' ELSE 'other' END AS season,
       COUNT(*)                                                             AS shipments,
       ROUND(AVG(julianday(s.delivered_at) - julianday(s.shipped_at)), 1)   AS days_to_deliver
FROM shipments s
JOIN orders    o ON o.id = s.order_id
JOIN customers c ON c.id = o.customer_id
JOIN plant_orders po ON po.order_id = o.id
WHERE s.delivered_at IS NOT NULL
GROUP BY climate, season
ORDER BY climate, season;

holds on the current window