SQL Case Study — Trends in Startups
Problem Statement
Howdy! It’s your first day as a TechCrunch reporter. Your first task is to write an article on the rising trends in the startup world. To get you started with your research, your boss emailed you file that contains a table called startups. It is a portfolio of some of the biggest names in the industry. Write queries with aggregate functions to retrieve some interesting insights about these companies.
Case Study Questions
- Getting started, take a look at the startups table: What are the column names?
- Calculate the total number of companies in the table.
- We want to know the total value of all companies in this table.
- What is the highest amount raised by a startup?
- The maximum amount of money raised, during ‘Seed’ stage.
- In what year was the oldest company on the list founded?
- Return the average valuation.
- Return the average valuation, in each category.
- Return the name of each category with the total number of companies that belong to it.
- Filter the result to only include categories that have more than three companies in them. What are the most competitive markets?
- What is the average size of a startup in each location?
- What is the average size of a startup in each location, with average sizes above 500?
Solution
- Getting started, take a look at the startups table: What are the column names?
It has 70 rows and 10 columns
column names are:
name
location
category
employees
raised
valuation
founded
stage
ceo
info
2. Calculate the total number of companies in the table.
Use COUNT to find out how many companies in the tables.
Total 70 companies in the tables.
3. We want to know the total value of all companies in this table.
Use SUM to know the total valuation of all companies.
The total valuation is $974,455,790,000!
4 . What is the highest amount raised by a startup?
Use MAX to know highest amount raised by a startup
Highest amount raised by a startup is $11,500,000,000
5. The maximum amount of money raised, during ‘Seed’ stage
Use MAX and WHERE to find maximum amount of money raised, during ‘Seed’ stage
Maximum amount of money raised, during ‘Seed’ stage is $18,000,000
6. In what year was the oldest company on the list from founded?
Use MIN to find the oldest company from list
The oldest company on the list from founded in year 1994.
7 . Return the average valuation .
Use AVG to find average valuation
The average valuation is $15,974,685,081.967213
8. Return the average valuation, in each category.
9. Return the name of each category with the total number of companies that belong to it
10. Filter the result to only include categories that have more than three companies in them. What are the most competitive markets?
11. What is the average size of a startup in each location?
12. What is the average size of a startup in each location, with average sizes above 500?
GitHub Link for this Case Study :- Trends in Startup
Thanks for reading this article so far. If you like then please share them with your friends and colleagues. If you have any questions or feedback, then please drop a note.