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. |
--Determining the three best-performing industries based on the number of new unicorns created between 2019 and 2021.
SELECT
industry,
COUNT(*) AS number_of_unicorns
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;1 hidden cell
The three industries that saw the greatest number of unicorn companies being created between 2019 and 2021 were Fintech, Internet software & services, and E-commerce & direct-to-consumer.
The change in the number of companies becoming unicorns over 2019-2021 is shown by the chart below.
Number of Companies that became Unicorns between 2019-2021
The number of E-commerce & direct-to-consumer, Fintech, and Internet Software & Services companies that became unicorn companies was not drastically different between 2019 and 2020. In 2021, however, all three industries experienced a large increase in the number of companies that became unicorns.
The increased number of unicorn companies implies that, unless a proportionally large increase in the number of investors or capital invested in E-commerce & direct-to-consumer, Fintech, and Internet Software & Services companies also occurred, the average valuation of the companies that became unicorns in 2021 is likely to be lower than previous years. This is supported by the following table.
--Determining the number of unicorns and average valuation (in billions)
--for each of the top 3 industries for each year between 2019 and 2021.
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, average_valuation
ORDER BY industry, year DESCAverage Valuation for Unicorn E-commerce, Fintech, and Internet Software Companies 2019-2021
The average valuation for unicorn companies in 2021 was lower across all three industries but their overall trends from 2019-2021 were not the same.
The e-commerce & direct-to-consumer companies had very similar average valuations in 2019 and 2021 at 2.58 and 2.47 billion, respectively. This suggests that the increased average valuation in 2020 may have been an outlier event.
The average valuations of fintech companies decreased consistently between 2019 and 2021, dropping from 6.8 billion to 2.75 billion. Whether this trend continues into 2022 seems to depend, at least in part, on whether similar growth as seen between 2020 and 2021 in the number of unicorn companies continues.
The internet software & services industry saw very similar average valuations between 2019 and 2020, coming in at 4.23 and 4.35 billion, respectively. 2021 marked the largest year-over-year percentage drop of any industry, falling approximately 50.3%.