Що таке аналітика цін на автомобільному ринку

-- Топ-15 автомобілів за останні 3 місяці (MVS + Automoto)
SELECT 
    make,
    model,
    COUNT(*) AS total_listings,
    ROUND(AVG(price_usd)) AS avg_price_usd,
    ROUND(AVG(year)) AS avg_year
FROM (
    SELECT make, model, price_usd, year 
    FROM mvs.listings 
    WHERE created_at >= CURRENT_DATE - INTERVAL '3' MONTH
    UNION ALL
    SELECT make, model, price_usd, year 
    FROM automoto.listings 
    WHERE created_at >= CURRENT_DATE - INTERVAL '3' MONTH
) combined
GROUP BY make, model
ORDER BY total_listings DESC
LIMIT 15

-- Середній вік авто по марках (MVS)
SELECT 
    make,
    COUNT(*) AS total_cars,
    ROUND(AVG(EXTRACT(YEAR FROM CURRENT_DATE) - year), 1) AS avg_age_years,
    MIN(EXTRACT(YEAR FROM CURRENT_DATE) - year) AS min_age,
    MAX(EXTRACT(YEAR FROM CURRENT_DATE) - year) AS max_age,
    ROUND(AVG(price_usd)) AS avg_price
FROM mvs.listings
WHERE year IS NOT NULL 
  AND year > 1990
  AND created_at >= CURRENT_DATE - INTERVAL '3' MONTH
GROUP BY make
HAVING COUNT(*) >= 100
ORDER BY avg_age_years ASC
LIMIT 20

-- Кількість унікальних авто з VIN (Automoto)
SELECT 
    make,
    COUNT(DISTINCT vin) AS unique_cars,
    COUNT(*) AS total_listings,
    ROUND(COUNT(*) * 1.0 / NULLIF(COUNT(DISTINCT vin), 0), 2) AS avg_reposts,
    ROUND(AVG(price_usd)) AS avg_price
FROM automoto.listings
WHERE vin IS NOT NULL 
  AND LENGTH(vin) = 17
  AND created_at >= CURRENT_DATE - INTERVAL '3' MONTH
GROUP BY make
HAVING COUNT(DISTINCT vin) >= 50
ORDER BY unique_cars DESC
LIMIT 15

-- Динаміка середніх цін по місяцях
SELECT 
    DATE_TRUNC('month', created_at) AS month,
    COUNT(*) AS listings_count,
    ROUND(AVG(price_usd)) AS avg_price,
    ROUND(PERCENTILE_CONT(0.5) WITHIN GROUP (ORDER BY price_usd)) AS median_price,
    ROUND(AVG(year)) AS avg_year
FROM mvs.listings
WHERE created_at >= CURRENT_DATE - INTERVAL '6' MONTH
  AND price_usd BETWEEN 1000 AND 100000
GROUP BY DATE_TRUNC('month', created_at)
ORDER BY month DESC

-- Розподіл авто по цінових сегментах
SELECT 
    CASE
        WHEN price_usd < 5000 THEN 'Budget (< $5k)'
        WHEN price_usd < 10000 THEN 'Economy ($5-10k)'
        WHEN price_usd < 20000 THEN 'Mid-range ($10-20k)'
        WHEN price_usd < 35000 THEN 'Upper-mid ($20-35k)'
        WHEN price_usd < 50000 THEN 'Premium ($35-50k)'
        ELSE 'Luxury ($50k+)'
    END AS price_segment,
    COUNT(*) AS cars_count,
    ROUND(COUNT(*) * 100.0 / SUM(COUNT(*)) OVER(), 1) AS percentage,
    ROUND(AVG(year)) AS avg_year
FROM mvs.listings
WHERE created_at >= CURRENT_DATE - INTERVAL '3' MONTH
  AND price_usd > 0
GROUP BY 
    CASE
        WHEN price_usd < 5000 THEN 'Budget (< $5k)'
        WHEN price_usd < 10000 THEN 'Economy ($5-10k)'
        WHEN price_usd < 20000 THEN 'Mid-range ($10-20k)'
        WHEN price_usd < 35000 THEN 'Upper-mid ($20-35k)'
        WHEN price_usd < 50000 THEN 'Premium ($35-50k)'
        ELSE 'Luxury ($50k+)'
    END
ORDER BY 
    CASE price_segment
        WHEN 'Budget (< $5k)' THEN 1
        WHEN 'Economy ($5-10k)' THEN 2
        WHEN 'Mid-range ($10-20k)' THEN 3
        WHEN 'Upper-mid ($20-35k)' THEN 4
        WHEN 'Premium ($35-50k)' THEN 5
        ELSE 6
    END