Project: Analyzing Unicorn Companies

-- Explore the data in the table
WITH industry_performance AS (
    -- Step 1: Combine all necessary tables
    SELECT
        i.industry,
        EXTRACT(YEAR FROM d.date_joined) AS year,
        COUNT(DISTINCT d.company_id) AS num_unicorns,
        AVG(f.valuation / 1000000000) AS average_valuation_billions -- Converting valuation to billions
    FROM
        industries i
        JOIN dates d ON i.company_id = d.company_id
        JOIN funding f ON i.company_id = f.company_id
    GROUP BY
        i.industry,
        EXTRACT(YEAR FROM d.date_joined)
),

-- Step 2: Rank industries by their average valuation per year
ranked_industries AS (
    SELECT
        industry,
        year,
        num_unicorns,
        average_valuation_billions,
        ROW_NUMBER() OVER (PARTITION BY year ORDER BY average_valuation_billions DESC) AS rank
    FROM
        industry_performance
)

-- Step 3: Select top 3 industries for each year
SELECT
    industry,
    year,
    num_unicorns,
    average_valuation_billions
FROM
    ranked_industries
WHERE
    rank <= 3
ORDER BY
    year DESC,
    average_valuation_billions DESC;