Project: Analyzing Unicorn Companies

-- Creating a temporary data table(CTE), named 'data', that contains all the desired columns, filtered for the period 2019-2021
WITH data as (
  	SELECT 
    industry, 
	count(date_joined) as num_unicorns, 
	EXTRACT(YEAR FROM date_joined) AS year,
	ROUND(AVG(valuation/1000000000),2) AS average_valuation_billions
    FROM dates AS d
    LEFT JOIN funding AS f
        ON d.company_id = f.company_id
    LEFT JOIN industries AS i
        ON f.company_id = i.company_id
    LEFT JOIN companies AS c
        ON i.company_id = c.company_id
    WHERE EXTRACT(YEAR FROM d.date_joined) BETWEEN 2019 AND 2021
	GROUP BY industry, year
	),
-- Creating a second CTE named 'top_industry' to find the top 3 industries based on the total number of unicorns during the period 2019-2021
top_industry AS (
    	SELECT 
        industry, 
        count(*) as n
    	FROM industries as i
		INNER JOIN dates as d
		ON i.company_id = d.company_id
		WHERE EXTRACT(YEAR FROM d.date_joined) BETWEEN 2019 AND 2021
		GROUP BY industry
		ORDER BY n DESC
		LIMIT 3
		)
-- Finally we combine the 2 above created CTE's to query for tbe columns of interest
SELECT industry, year, num_unicorns, average_valuation_billions
FROM data
WHERE industry IN (SELECT industry FROM top_industry)
ORDER BY year DESC, num_unicorns DESC;