Project: Analyzing Unicorn Companies

Case Study

(Available on datacamp.com)

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.

Solution

To identify the three best-performing industries based on the number of new unicorns created in 2019, 2020, and 2021 combined, we would need to rank all the industries based on the total count of new unicorns created within the required period.

NOTE: We are taking into account the top-ranked performing companies across the entire period, not for each year

DataFrameas

df3

variable

SELECT i.industry, 
    COUNT(i.*)
FROM industries AS i
INNER JOIN dates AS d
    ON i.company_id = d.company_id
WHERE EXTRACT(year FROM d.date_joined) in ('2019', '2020', '2021')
GROUP BY industry
ORDER BY count DESC
LIMIT 3

The below query selects our top industries (i.e. the three best-performing industries based on the number of new unicorns created in 2019, 2020, and 2021 combined) ranked by year and their evaluation across all years available in the dataset.

We can use this to see the history of these industries' performance across the entire period of our datasets.

DataFrameas

df8

variable

WITH top_industries AS (
    SELECT industry, 
    COUNT(i.company_id) AS count
    FROM industries i
    JOIN dates d
        ON i.company_id = d.company_id
    WHERE EXTRACT(year FROM d.date_joined) in ('2019', '2020', '2021')
    GROUP BY industry
    ORDER BY count DESC
    LIMIT 3
)


SELECT
	industry,
	EXTRACT(YEAR FROM date_joined) AS year,
	COUNT(i.company_id) AS num_unicorns,
	ROUND(AVG(valuation/1000000000), 2) AS average_valuation_billions
FROM industries i
JOIN dates d ON i.company_id = d.company_id
JOIN funding f ON i.company_id = f.company_id
WHERE industry IN
	(SELECT industry
	 FROM top_industries)
GROUP BY industry, EXTRACT(YEAR FROM date_joined)
ORDER BY year DESC, num_unicorns;

The below query selects all the top companies ranked by year and their evaluation across the desired period. This could be useful if we wanted to change the period of our search. We would simply update the main query altering the filter condition.

DataFrameas

df

variable

-- Create a CTE for the top-ranked industries for 2019-2021:
WITH top_industries AS (
    SELECT industry, 
    	   COUNT(i.company_id) AS num_unicorns
    FROM industries i
    JOIN dates d
        ON i.company_id = d.company_id
    WHERE EXTRACT(year FROM d.date_joined) in ('2019', '2020', '2021')
    GROUP BY industry
    ORDER BY num_unicorns DESC
    LIMIT 3
),

-- Create a CTE for the top_industries ranked by year and their average valuation
ranked_by_year AS (
    SELECT
        industry,
        EXTRACT(YEAR FROM date_joined) AS year,
        COUNT(i.company_id) AS num_unicorns,
        ROUND(AVG(valuation/1000000000), 2) AS average_valuation_billions
    FROM industries i
    JOIN dates d ON i.company_id = d.company_id
    JOIN funding f ON i.company_id = f.company_id
    WHERE industry IN
        (SELECT industry
		 FROM top_industries)
    GROUP BY industry, EXTRACT(YEAR FROM date_joined)
    ORDER BY year DESC, num_unicorns
)

-- The main query selects `ranked_by_year` records that fall within our desired period
SELECT
    industry,
    year,
    num_unicorns,
    average_valuation_billions
FROM ranked_by_year
WHERE year in ('2019', '2020', '2021')
ORDER BY year DESC, num_unicorns DESC
;

The table below acheives the same result, but the year filtering condition is part of the ranked_by_year CTE, so altering it may be non-intuitive.

DataFrameas

df7

variable

-- Create a CTE for the top-ranked industries for 2019-2021:
WITH top_industries AS (
    SELECT industry, 
    COUNT(i.company_id) AS count
    FROM industries i
    JOIN dates d
        ON i.company_id = d.company_id
    WHERE EXTRACT(year FROM d.date_joined) in ('2019', '2020', '2021')
    GROUP BY industry
    ORDER BY count DESC
    LIMIT 3
),

-- Create a CTE for the top_industries, ranked by year
ranked_by_year AS (
    SELECT
        industry,
        EXTRACT(YEAR FROM date_joined) AS year,
        COUNT(i.company_id) AS num_unicorns,
        ROUND(AVG(valuation/1000000000), 2) AS average_valuation_billions
    FROM industries i
    JOIN dates d ON i.company_id = d.company_id
    JOIN funding f ON i.company_id = f.company_id
    WHERE EXTRACT(YEAR FROM date_joined) IN ('2019', '2020', '2021') AND industry IN
        (SELECT industry
		 FROM top_industries)
    GROUP BY industry, EXTRACT(YEAR FROM date_joined)
    ORDER BY year DESC, num_unicorns
)

SELECT
    industry,
    year,
    num_unicorns,
    average_valuation_billions
FROM ranked_by_year
ORDER BY year DESC, num_unicorns DESC
;

The below query selects all the top companies ranked by year and their evaluation across the desired period. This could be useful if we wanted to change the period of our search or the industries we would like to observe. We would simply update the main query altering the filter condition.

It also performs the arithmetic and rounding to give us our valuation in billions in the main query.

In PostgreSQL, filtering within the CTE can be more optimized in some cases, but it depends on several factors:

Reasons filtering in CTE might be better:

Reduced data size: By filtering in the CTE, you only process relevant data in the main query, improving performance. This is especially true for large datasets and complex filtering conditions.
Optimizer benefits: The PostgreSQL optimizer can potentially use the CTE's filtering information to create a more efficient execution plan.

Reasons filtering in main query might be better:

Readability: Filtering in the main query can sometimes lead to clearer code, especially for simple queries.
Index utilization: If the main query's WHERE clause uses columns with existing indexes, filtering there might leverage those indexes for faster processing.

DataFrameas

df1

variable

WITH top_industries AS
(
    SELECT i.industry, 
        COUNT(i.*)
    FROM industries AS i
    INNER JOIN dates AS d
        ON i.company_id = d.company_id
    WHERE EXTRACT(year FROM d.date_joined) in ('2019', '2020', '2021')
    GROUP BY industry
    ORDER BY count DESC
    LIMIT 3
),

yearly_rankings AS 
(
    SELECT COUNT(i.*) AS num_unicorns,
        i.industry,
        EXTRACT(year FROM d.date_joined) AS year,
        AVG(f.valuation) AS average_valuation
    FROM industries AS i
    INNER JOIN dates AS d
        ON i.company_id = d.company_id
    INNER JOIN funding AS f
        ON d.company_id = f.company_id
    GROUP BY industry, year
)

SELECT industry,
    year,
    num_unicorns,
    ROUND(AVG(average_valuation / 1000000000), 2) AS average_valuation_billions
FROM yearly_rankings
WHERE year in ('2019', '2020', '2021')
    AND industry in (SELECT industry
                    FROM top_industries)
GROUP BY industry, num_unicorns, year
ORDER BY year DESC, num_unicorns DESC

The below query makes use of only one CTE, and the yearly_rankings/ranked_by_year CTE is absorbed into the main query:

DataFrameas

df2

variable

-- Create a CTE for the top-ranked industries for 2019-2021:
WITH top_industries AS (
    SELECT industry, 
    COUNT(i.company_id) AS count
    FROM industries i
    JOIN dates d
        ON i.company_id = d.company_id
    GROUP BY industry
    ORDER BY count DESC
    LIMIT 3
)


SELECT
	industry,
	EXTRACT(YEAR FROM date_joined) AS year,
	COUNT(i.company_id) AS num_unicorns,
	ROUND(AVG(valuation/1000000000), 2) AS average_valuation_billions
FROM industries i
JOIN dates d ON i.company_id = d.company_id
JOIN funding f ON i.company_id = f.company_id
WHERE industry IN
	(SELECT industry
	 FROM top_industries) AND EXTRACT(year FROM d.date_joined) in ('2019', '2020', '2021')
GROUP BY industry, EXTRACT(YEAR FROM date_joined)
ORDER BY year DESC, num_unicorns;

‌
‌
‌

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;}Case Study

dates

funding

industries

companies

The output

Solution

In PostgreSQL, filtering within the CTE can be more optimized in some cases, but it depends on several factors:

Reasons filtering in CTE might be better:

Reasons filtering in main query might be better:

Case Study