big_one.sql

WITH price_stats AS (
  SELECT
    year,
    model,

    COUNT(*) FILTER (WHERE source = 'craigslist') AS cnt_craigslist,
    ROUND(AVG(price) FILTER (WHERE source = 'craigslist')::numeric, 2) AS avg_craigslist,

    COUNT(*) FILTER (WHERE source = 'facebook') AS cnt_facebook,
    ROUND(AVG(price) FILTER (WHERE source = 'facebook')::numeric, 2) AS avg_facebook,

    COUNT(*) FILTER (WHERE source = 'bicyclebluebook') AS cnt_bb,
    ROUND(AVG(price) FILTER (WHERE source = 'bicyclebluebook')::numeric, 2) AS avg_bb

  FROM bike_listings_new
  WHERE model IS NOT NULL AND year IS NOT NULL
  GROUP BY year, model
  HAVING COUNT(DISTINCT source) > 1
)

SELECT
  year,
  model,
  avg_craigslist,
  avg_facebook,
  avg_bb,

  -- absolute price spread
  GREATEST(
    COALESCE(avg_craigslist, 0),
    COALESCE(avg_facebook, 0),
    COALESCE(avg_bb, 0)
  ) - LEAST(
    COALESCE(NULLIF(avg_craigslist, 0), NULL),
    COALESCE(NULLIF(avg_facebook, 0), NULL),
    COALESCE(NULLIF(avg_bb, 0), NULL)
  ) AS abs_spread,

  -- relative price spread ratio
  ROUND(
    GREATEST(
      COALESCE(avg_craigslist, 0),
      COALESCE(avg_facebook, 0),
      COALESCE(avg_bb, 0)
    ) /
    LEAST(
      COALESCE(NULLIF(avg_craigslist, 0), NULL),
      COALESCE(NULLIF(avg_facebook, 0), NULL),
      COALESCE(NULLIF(avg_bb, 0), NULL)
    ),
    2
  ) AS max_min_ratio

FROM price_stats
ORDER BY max_min_ratio DESC
LIMIT 15;