(SQL) Project: Analyzing Unicorn Companies

Did you know that the average return from investing in stocks is 10% per year (not accounting for inflation)? 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.

The output

Your query should return a table in the following format:

industry	year	num_unicorns	average_valuation_billions
industry1	2021	---	---
industry2	2020	---	---
industry3	2019	---	---
industry1	2021	---	---
industry2	2020	---	---
industry3	2019	---	---
industry1	2021	---	---
industry2	2020	---	---
industry3	2019	---	---

Where industry1, industry2, and industry3 are the three top-performing industries.

DataFrameas

df6

variable

-- CTE combination of all datasets
WITH all_data AS
(SELECT i.company_id, EXTRACT(year FROM date_joined) AS year_unicorn, year_founded, valuation/1000000000 AS valuation_in_billions, funding, select_investors, industry, company, city, country, continent
FROM industries AS i
LEFT JOIN funding AS f
ON i.company_id = f.company_id
LEFT JOIN dates AS d
ON d.company_id = i.company_id
LEFT JOIN companies AS c
ON c.company_id = i.company_id)

-- find the list of companies with the highest amount of valuation
SELECT company, country, MAX(valuation_in_billions) AS max_val_in_billions_USD
FROM all_data
GROUP BY company, country
ORDER BY max_val_in_billions_USD DESC;

DataFrameas

df5

variable

-- Company on the highest valuation of all records alongside its detail 
WITH all_data AS
	(SELECT i.company_id, EXTRACT(year FROM date_joined) AS year_unicorn, year_founded, valuation, funding, select_investors, industry, company, city, country, continent
FROM industries AS i
LEFT JOIN funding AS f
ON i.company_id = f.company_id
LEFT JOIN dates AS d
ON d.company_id = i.company_id
LEFT JOIN companies AS c
ON c.company_id = i.company_id)

SELECT * FROM all_data
WHERE valuation = (
			SELECT MAX(valuation) FROM all_data)

DataFrameas

df_1

variable

-- Exploring the "dates" table dataset
SELECT * FROM dates

DataFrameas

df1

variable

-- Exploring the "funding" table dataset
SELECT * FROM funding

DataFrameas

df2

variable

-- Exploring the "industries" table dataset
SELECT * FROM industries

DataFrameas

df14

variable

-- List of most industries from the dataset
SELECT industry, COUNT(*) AS num_ind FROM industries
GROUP BY industry
ORDER BY num_ind DESC

DataFrameas

df9

variable

-- Most common industries in the dataset
WITH all_data AS
(SELECT i.company_id, EXTRACT(year FROM date_joined) AS year_unicorn, year_founded, valuation, funding, select_investors, industry, company, city, country, continent
FROM industries AS i
LEFT JOIN funding AS f
ON i.company_id = f.company_id
LEFT JOIN dates AS d
ON d.company_id = i.company_id
LEFT JOIN companies AS c
ON c.company_id = i.company_id)

SELECT industry, COUNT(industry) AS num_ind
FROM all_data
GROUP BY industry
ORDER BY num_ind DESC;

DataFrameas

df3

variable

-- Exploring the "companies" table dataset
SELECT * FROM companies

DataFrameas

df15

variable

-- Companies spread all over the world
SELECT industry, COUNT(industry) AS num_ind, continent, country, city
FROM industries AS i
LEFT JOIN companies AS c
ON i.company_id = c.company_id
GROUP BY industry, continent, country, city
ORDER BY num_ind DESC

DataFrameas

df16

variable

-- list of most industries that belong to unicorn categori among countries and cities
SELECT industry, COUNT(industry) AS num_ind, country, city
FROM industries AS i
LEFT JOIN companies AS c
ON i.company_id = c.company_id
WHERE continent = 'Asia'
GROUP BY industry, country, city
ORDER BY num_ind DESC

DataFrameas

df17

variable

-- Unicorn Analysis in Indonesia
SELECT company, industry, city, EXTRACT(year FROM date_joined) AS date_joined_unicorn, 
	valuation/1000000000 AS valuation_in_billions_USD
FROM industries AS i
LEFT JOIN companies AS c
ON i.company_id = c.company_id
LEFT JOIN dates AS d
ON d.company_id = i.company_id
LEFT JOIN funding AS f
ON i.company_id = f.company_id
WHERE continent = 'Asia' AND country = 'Indonesia'
GROUP BY date_joined_unicorn, company, industry, city, valuation
ORDER BY date_joined_unicorn DESC

DataFrameas

df20

variable

-- -- Unicorn Analysis in Indonesia
SELECT industry, COUNT(industry) AS num_ind, city, EXTRACT(year FROM date_joined) AS date_joined_unicorn, 
	SUM(valuation/1000000000) AS total_valuation_in_billions_USD
FROM industries AS i
LEFT JOIN companies AS c
ON i.company_id = c.company_id
LEFT JOIN dates AS d
ON d.company_id = i.company_id
LEFT JOIN funding AS f
ON i.company_id = f.company_id
WHERE continent = 'Asia' AND country = 'Indonesia'
GROUP BY industry, date_joined_unicorn, city
ORDER BY num_ind DESC

DataFrameas

df18

variable

-- Unicorn Analysis in Sweden
SELECT company, industry, city, EXTRACT(year FROM date_joined) AS date_joined_unicorn, 
	valuation/1000000000 AS valuation_in_billions_USD
FROM industries AS i
LEFT JOIN companies AS c
ON i.company_id = c.company_id
LEFT JOIN dates AS d
ON d.company_id = i.company_id
LEFT JOIN funding AS f
ON i.company_id = f.company_id
WHERE continent = 'Europe' AND country = 'Sweden'
GROUP BY date_joined_unicorn, company, industry, city, valuation
ORDER BY valuation_in_billions_USD DESC

‌
‌
‌

(SQL) Project: Analyzing Unicorn Companies

.mfe-app-workspace-kj242g{position:absolute;top:-8px;}.mfe-app-workspace-11ezf91{display:inline-block;}.mfe-app-workspace-11ezf91:hover .Anchor__copyLink{visibility:visible;}dates

funding

industries

companies

The output

dates