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. |
with unicorn_companies as ( SELECT i.industry,
count(i.company_id)
from industries i
inner join dates d on i.company_id=d.company_id
WHERE date_joined BETWEEN DATE('2019-01-01') AND DATE('2021-12-31')
group by 1
order by count desc
limit 3),
final_data as (select i.industry,
date_part('year', d.date_joined) as date_joined,
count(i.company_id) as num_unicorns,
round(avg(f.valuation)/1000000000,2) as average_valuation_billions
from industries i
inner join dates d on i.company_id=d.company_id
inner join funding f on d.company_id = f.company_id
where date_joined BETWEEN DATE('2019-01-01') AND DATE('2021-12-31')
and i.industry in (select industry from unicorn_companies)
group by 1,2
order by industry, date_joined desc)
select industry,
date_joined as "year",
num_unicorns,
average_valuation_billions
from final_data
order by 1,2 descSELECT i.industry, count(i.company_id) from industries i inner join dates d on i.company_id=d.company_id WHERE date_joined BETWEEN DATE('2019-01-01') AND DATE('2021-12-31') group by 1 order by count desc limit 3
WITH unicorn_company AS ( SELECT * FROM dates WHERE date_joined BETWEEN DATE('2019-01-01') AND DATE('2021-12-31') ) SELECT DISTINCT u.company_id, u.date_joined AS "year", i.industry FROM industries i LEFT JOIN unicorn_company u ON u.company_id=i.company_id where u.company_id is not null
select distinct industry, date_part ('year',year_joined) as year_joined, count(industry) as num_unicorns, rank() over(partition by industry, num_unicorns) from industry_data group by 1,2 order by 3 desc
select distinct industry, "year", num_unicorns, average_valuation_billions from b
WITH unicorn_company AS ( SELECT * FROM dates WHERE date_joined BETWEEN DATE('2019-01-01') AND DATE('2021-12-31') ),
valuation as (select distinct f.company_id, f.valuation, i.industry from funding f left join industries i ON f.company_id=i.company_id),
industry_data as (SELECT DISTINCT u.company_id, date_part('year',u.date_joined) AS year_joined, v.industry as industry, v.valuation as valuation FROM valuation v LEFT JOIN unicorn_company u ON u.company_id=v.company_id where u.company_id is not null),
a as (select distinct industry, year_joined, count(industry) as num_unicorns, avg(valuation) as avg_valuation from industry_data group by 1,2 order by 3 desc ) ,
final_data as (select distinct industry, year_joined, num_unicorns, 1.00*(avg_valuation) as avg_valuation, rank() over(partition by year_joined order by year_joined, num_unicorns desc) as rank from a group by 1,2,3,4
order by rank, year_joined desc ),
b as (select distinct industry, year_joined as "year", num_unicorns, ROUND(1.00*((1.00*avg_valuation)/1000000000),2) as average_valuation_billions, rank from final_data where rank<4 order by rank, year_joined desc )