city.md

sql

WITH city_avgs AS (
    SELECT
        brand,
        model,
        year,
        city,
        COUNT(*)        AS listings,
        AVG(price)::numeric(10,2) AS avg_price_city
    FROM   bike_listings_new
    WHERE  price IS NOT NULL
      AND  brand IS NOT NULL
      AND  model IS NOT NULL
      AND  year  IS NOT NULL
    GROUP  BY brand, model, year, city
    HAVING COUNT(*) >= 3                -- keep only well-represented combos
),

benchmarks AS (                        -- compute national stats per combo
    SELECT
        brand, model, year,
        AVG(avg_price_city)            AS avg_price_all_cities,
        MAX(avg_price_city)            AS max_city_price,
        MIN(avg_price_city)            AS min_city_price
    FROM   city_avgs
    GROUP  BY brand, model, year
)

SELECT
    c.brand,
    c.model,
    c.year,
    c.city,
    c.listings,
    c.avg_price_city,
    b.avg_price_all_cities,
    ROUND(c.avg_price_city - b.avg_price_all_cities, 2)      AS delta_from_avg,
    ROUND(c.avg_price_city / b.avg_price_all_cities * 100,2) AS pct_of_avg,
    b.max_city_price,
    b.min_city_price
FROM   city_avgs   c
JOIN   benchmarks  b  USING (brand, model, year)
ORDER  BY brand, model, year, delta_from_avg DESC;

result

  brand    |      model      | year |    city     | listings | avg_price_city | avg_price_all_cities  | delta_from_avg | pct_of_avg | max_city_price | min_city_price
-------------+-----------------+------+-------------+----------+----------------+-----------------------+----------------+------------+----------------+----------------
 Mountain    | unknown         | 2025 | Bentonville |        3 |        1433.33 | 1433.3300000000000000 |           0.00 |     100.00 |        1433.33 |        1433.33
 Norco       | unknown         | 2025 | seattle     |        5 |         400.00 |  400.0000000000000000 |           0.00 |     100.00 |         400.00 |         400.00
 Specialized | Hardrock        | 2000 | seattle     |        4 |         248.75 |  248.7500000000000000 |           0.00 |     100.00 |         248.75 |         248.75
 Specialized | Rockhopper      | 2023 | portland    |        3 |         261.33 |  261.3300000000000000 |           0.00 |     100.00 |         261.33 |         261.33
 Specialized | Stumpjumper Evo | 2023 | Bentonville |        3 |        3300.00 | 3300.0000000000000000 |           0.00 |     100.00 |        3300.00 |        3300.00
 Specialized | unknown         | 2025 | seattle     |        4 |         750.00 |  495.0000000000000000 |         255.00 |     151.52 |         750.00 |         240.00
 Specialized | unknown         | 2025 | chicago     |        3 |         240.00 |  495.0000000000000000 |        -255.00 |      48.48 |         750.00 |         240.00
 Trek        | 820             | 2023 | newyork     |        3 |         290.00 |  290.0000000000000000 |           0.00 |     100.00 |         290.00 |         290.00
 Trek        | unknown         | 2023 | denver      |        5 |        1348.00 |  890.6650000000000000 |         457.34 |     151.35 |        1348.00 |         433.33
 Trek        | unknown         | 2023 | losangeles  |        3 |         433.33 |  890.6650000000000000 |        -457.34 |      48.65 |        1348.00 |         433.33
 Yeti        | SB150           | 2020 | Bentonville |        3 |        3600.00 | 3600.0000000000000000 |           0.00 |     100.00 |        3600.00 |        3600.00