Skip to content
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
df
variable
WITH dump as
(SELECT DISTINCT u.company_id, EXTRACT(YEAR FROM u.date_joined) as unicorn_year, i.industry, f.valuation
FROM public.dates u
LEFT JOIN public.industries i ON i.company_id = u.company_id
LEFT JOIN public.funding f ON f.company_id = u.company_id),
top_industries as
(
SELECT b.rank, b.industry, b.unicorns_added
FROM
(SELECT a.*, RANK()OVER(ORDER BY a.unicorns_added DESC) AS rank
FROM
(SELECT d.industry, COUNT(DISTINCT d.company_id) as unicorns_added
FROM dump d
WHERE d.unicorn_year IN (2019,2020,2021)
GROUP BY 1
ORDER BY 2 DESC) a) b
WHERE b.rank <= 3
ORDER BY 1)
SELECT a.industry, a.year, a.num_unicorns, a.average_valuation_billions
FROM
(SELECT t.rank, t.industry, d.unicorn_year AS year,
COUNT(DISTINCT d.company_id) as num_unicorns,
ROUND(AVG(d.valuation/1000000000),2) as average_valuation_billions
FROM top_industries t
LEFT JOIN dump d ON t.industry = d.industry
WHERE d.unicorn_year IN (2019,2020,2021)
GROUP BY 1,2,3
ORDER BY 1,3 DESC) a
ORDER BY 1,2 DESC