Project: Analyzing Unicorn Companies

Did you know that the average return from investing in stocks is 10% per year! But who wants to be average?!

You have been asked to support an investment firm by analyzing trends in high-growth companies. They are interested in understanding which industries are producing the highest valuations and the rate at which new high-value companies are emerging. Providing them with this information gives them a competitive insight as to industry trends and how they should structure their portfolio looking forward.

You have been given access to their unicorns database, which contains the following tables:

dates

Column	Description
company_id	A unique ID for the company.
date_joined	The date that the company became a unicorn.
year_founded	The year that the company was founded.

funding

Column	Description
company_id	A unique ID for the company.
valuation	Company value in US dollars.
funding	The amount of funding raised in US dollars.
select_investors	A list of key investors in the company.

industries

Column	Description
company_id	A unique ID for the company.
industry	The industry that the company operates in.

companies

Column	Description
company_id	A unique ID for the company.
company	The name of the company.
city	The city where the company is headquartered.
country	The country where the company is headquartered.
continent	The continent where the company is headquartered.

DataFrameas

df4

variable

--RETURN CUMMULATIVE TOTAL UNICORNS PER INDUSTRY TO IDENTIFY THE TOP 3 OVER 3YRS
SELECT 	i.industry,
		COUNT(d.company_id) AS num_unicorns,
		COUNT(DISTINCT EXTRACT(YEAR FROM date_joined)) AS total_num_years,
		ROUND(AVG(f.valuation/1000000000),2) AS average_valuation_billions

		FROM industries AS i
			JOIN dates AS d
				USING(company_id)
					 JOIN funding AS f
						USING(company_id)

		WHERE d.date_joined IN 
					(SELECT
						CASE 
							WHEN EXTRACT(YEAR FROM date_joined) IN (2021, 2020, 2019)
							THEN date_joined END
					 FROM dates
					)

		GROUP BY i.industry
		ORDER BY num_unicorns DESC
		LIMIT 3

DataFrameas

df6

variable

--confirming top industry query runs as a CTE
WITH
top_industries 
AS (
		SELECT 	
			i.industry,
			COUNT(d.company_id) AS num_unicorns,
			COUNT(DISTINCT EXTRACT(YEAR FROM date_joined)) AS total_num_years,
			ROUND(AVG(f.valuation/1000000000),2) AS average_valuation_billions

		FROM industries AS i
			JOIN dates AS d
				USING(company_id)
					JOIN funding AS f
						USING(company_id)
					 

		WHERE d.date_joined IN 
					(SELECT
						CASE 
							WHEN EXTRACT(YEAR FROM date_joined) IN (2021, 2020, 2019)
							THEN date_joined END
					 FROM dates
					)

		GROUP BY i.industry
		ORDER BY num_unicorns DESC
		LIMIT 3
	)

SELECT * FROM top_industries

DataFrameas

df5

variable

--HOW MANY COMPANIES WITHIN THE TOP 3 INDUSTRIES (FINTECH, SAAS, ECOM) BECAME UNICORNS IN EACH YEAR

WITH
top_industries 
AS (
		SELECT 	
			i.industry,
			COUNT(d.company_id) AS num_unicorns,
			--EXTRACT(YEAR FROM date_joined)AS year,
			ROUND(AVG(f.valuation/1000000000),2) AS average_valuation_billions

		FROM industries AS i
			JOIN dates AS d
				USING(company_id)
					JOIN funding AS f
						USING(company_id)		
					 

		WHERE d.date_joined IN 
					(SELECT
						CASE 
							WHEN EXTRACT(YEAR FROM date_joined) IN (2021, 2020, 2019)
							THEN date_joined END
					 FROM dates
					)

		GROUP BY i.industry
		ORDER BY num_unicorns DESC
		LIMIT 3
	) 
--END OF CTE--END OF CTE--END OF CTE--END OF CTE--END OF CTE--END OF CTE--END OF CTE--

SELECT 	i.industry,
		EXTRACT (YEAR FROM d.date_joined) AS year,
		COUNT(d.date_joined) AS num_unicorns, 
		ROUND(AVG(f.valuation/1000000000),2) AS average_valuation_billions
		--DENSE_RANK() OVER(ORDER BY num_unicorns DESC)AS rank
		 

FROM industries AS i
		JOIN top_industries as ti
			USING(industry)
				JOIN dates AS d
					USING(company_id)
						JOIN funding AS f
							USING(company_id)

WHERE i.industry IN 
		(SELECT industry FROM top_industries)
	AND d.date_joined IN
		(SELECT
			CASE 
				WHEN EXTRACT(YEAR FROM date_joined) IN (2021, 2020, 2019)
				THEN date_joined 
		 	END
		 FROM dates
		)
				
			
GROUP BY i.industry, year, num_unicorns --,f.valuation --ti.average_valuation_billions
ORDER BY i.industry, year DESC

--ORDER BY rank ASC, year DESC --i.industry DESC, year DESC