Skip to content

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

ColumnDescription
company_idA unique ID for the company.
date_joinedThe date that the company became a unicorn.
year_foundedThe year that the company was founded.

funding

ColumnDescription
company_idA unique ID for the company.
valuationCompany value in US dollars.
fundingThe amount of funding raised in US dollars.
select_investorsA list of key investors in the company.

industries

ColumnDescription
company_idA unique ID for the company.
industryThe industry that the company operates in.

companies

ColumnDescription
company_idA unique ID for the company.
companyThe name of the company.
cityThe city where the company is headquartered.
countryThe country where the company is headquartered.
continentThe continent where the company is headquartered.

The output

Your query should return a table in the following format:

industryyearnum_unicornsaverage_valuation_billions
industry12021------
industry22020------
industry32019------
industry12021------
industry22020------
industry32019------
industry12021------
industry22020------
industry32019------

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

Spinner
DataFrameas
df
variable
WITH top_industries AS (
    SELECT i.industry, 
			COUNT(i.*) AS count
    FROM industries i
    INNER JOIN dates d ON i.company_id = d.company_id
    WHERE EXTRACT(YEAR FROM d.date_joined) IN (2019, 2020, 2021)
    GROUP BY i.industry
    ORDER BY count DESC
    LIMIT 3
), 
yearly_rankings AS (
    SELECT i.industry, 
			EXTRACT(YEAR FROM d.date_joined) AS year_joined, 
			COUNT(i.*) AS num_unicorns, AVG(f.valuation) AS avg_valuation
    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
    GROUP BY i.industry, year_joined
)
SELECT industry, 
		year_joined AS year, 
		num_unicorns, ROUND(AVG(avg_valuation) / 1000000000, 2) AS average_valuation_billions
FROM yearly_rankings
WHERE year_joined IN (2019, 2020, 2021)
    AND industry IN (SELECT industry FROM top_industries)
GROUP BY industry, num_unicorns, year_joined
ORDER BY year_joined DESC, num_unicorns DESC;
Current Type: Bar
Current X-axis: year
Current Y-axis: num_unicorns
Current Color: industry

Number of Unicorns in each sector per year

Current Type: Bar
Current X-axis: industry
Current Y-axis: average_valuation_billions
Current Color: None

Average Valuation in 2019 per industry

Current Type: Bar
Current X-axis: industry
Current Y-axis: average_valuation_billions
Current Color: None

Average Valuation in 2020 per industry

Current Type: Bar
Current X-axis: industry
Current Y-axis: average_valuation_billions
Current Color: None

Average Valuation in 2021 per industry

Spinner
DataFrameas
df1
variable
WITH fintech AS ( 
    SELECT 
        c.company AS company_name, 
        ROUND(f.valuation / 1000000000, 2) AS valuation, 
        EXTRACT(YEAR FROM date_joined) as year,
        RANK() OVER (PARTITION BY EXTRACT(YEAR FROM date_joined) ORDER BY ROUND(f.valuation / 1000000000, 2) DESC) AS ranking
    FROM funding f
    INNER JOIN dates d ON f.company_id = d.company_id
    INNER JOIN companies c ON f.company_id = c.company_id
    INNER JOIN industries i ON f.company_id = i.company_id
    WHERE i.industry = 'Fintech' AND EXTRACT(YEAR FROM date_joined) in (2019)
)

SELECT 
    company_name, valuation, year, ranking
FROM fintech
WHERE ranking <= 10
ORDER BY year DESC, ranking ASC;
Spinner
DataFrameas
df2
variable
WITH fintech AS ( 
    SELECT 
        c.company AS company_name, 
        ROUND(f.valuation / 1000000000, 2) AS valuation, 
        EXTRACT(YEAR FROM date_joined) as year,
        RANK() OVER (PARTITION BY EXTRACT(YEAR FROM date_joined) ORDER BY ROUND(f.valuation / 1000000000, 2) DESC) AS ranking
    FROM funding f
    INNER JOIN dates d ON f.company_id = d.company_id
    INNER JOIN companies c ON f.company_id = c.company_id
    INNER JOIN industries i ON f.company_id = i.company_id
    WHERE i.industry = 'Fintech' AND EXTRACT(YEAR FROM date_joined) in (2020)
)

SELECT 
    company_name, valuation, year, ranking
FROM fintech
WHERE ranking <= 10
ORDER BY year DESC, ranking ASC;
Spinner
DataFrameas
df3
variable
WITH fintech AS ( 
    SELECT 
        c.company AS company_name, 
        ROUND(f.valuation / 1000000000, 2) AS valuation, 
        EXTRACT(YEAR FROM date_joined) as year,
        RANK() OVER (PARTITION BY EXTRACT(YEAR FROM date_joined) ORDER BY ROUND(f.valuation / 1000000000, 2) DESC) AS ranking
    FROM funding f
    INNER JOIN dates d ON f.company_id = d.company_id
    INNER JOIN companies c ON f.company_id = c.company_id
    INNER JOIN industries i ON f.company_id = i.company_id
    WHERE i.industry = 'Fintech' AND EXTRACT(YEAR FROM date_joined) in (2021)
)

SELECT 
    company_name, valuation, year, ranking
FROM fintech
WHERE ranking <= 10
ORDER BY year DESC, ranking ASC;