ASSIGNMENT: SQL AGGREGATION
OVERVIEW
SQL Aggregation lets you perform a calculation on a set of values, and returns a single value.
Aggregation is a great way to summarize and analyze data in different dimensions, which is why
in this exercise we will apply aggregation to do Exploratory Data Analysis.
WHAT IS EXPLORATORY DATA ANALYSIS?
Exploratory data analysis (EDA) is used by data scientists to analyze and investigate data sets
and summarize their main characteristics, often employing data visualization methods. It helps
determine how best to manipulate data sources to get the answers you need, making it easier for
data scientists to discover patterns, spot anomalies, test a hypothesis, or check assumptions.
WORKING WITH NEW DATASET
As a data expert, you will always be required to work with new and ambiguous datasets. By
asking multiple questions, coming up with different hypothesis, you can explore and understand
the data better to test your hypothesis and check your assumptions. This assignment is meant to
help you sharpen your analytical thinking ability.
ANALYTICS CASE: THE CRUNCHBASE DATASET
The data for the following assignment was pulled from Crunchbase, a crowdsourced index of
startups, founders, investors, and the activities of all three. The dataset includes funding,
investment, and acquisition data on over 40,000 companies.
What kinds of questions can I ask?
• Are there characteristics of a company—industry, location, etc.—that differ by VC? Do
some VCs typically invest together, while others rarely do so? Are companies raising
more money earlier? ARE WE IN A BUBBLE??
What are the tables called?
• You check a full list of tables here. But for this assignment, we only focus on 2 tables.
• tutorial.crunchbase_companies: The first table lists a large portion of companies in
the database; one row per company. The permalink field is a unique identifier for each
row, and also shows the web address. For each company in the table, you can view its
online Crunchbase profile by copying/pasting its permalink after Crunchbase’s web
domain. For example, the third company in the table, “.Club Domains,” has the
permalink “/company/club-domains,” so its profile address would
be [Link] The fields with "funding" in
the name have to do with how much outside investment (in USD) each company has
taken on. The rest of the fields are self-explanatory.
© 2021, Tuan Vu. All rights reserved.
SELECT *
FROM tutorial.crunchbase_companies
• tutorial.crunchbase_companies: This table lists acquisitions—one row per
acquisition. company_permalink in this table maps to the permalink field in
tutorial.crunchbase_companies table. Joining these two fields will add information
about the company being acquired. You'll notice that there is a separate field called
acquirer_permalink as well. This can also be mapped to the permalink field
tutorial.crunchbase_companies to add additional information about the acquiring
company. Don't worry about these joining fields in this assignment. We will learn more
about join in the next lesson.
SELECT *
FROM tutorial.crunchbase_acquisitions
PROBLEM: AGGREGATION QUESTIONS
For visualization questions, you can use anything you want. The preferred way is to use Mode’s
charts, but you can also extract the data and use Excel or anything else.
1. CRUNCHBASE_COMPANIES
Answer the following questions using tutorial.crunchbase_companies table
1. How many records in this table?
2. Write a query that determines counts of every single column. Which column has the most
null values? Can you check the number of NULL records in that column?
3. What is the minimum, maximum, total, and average funding total amount in USD? Find
out which company has the highest funding amount.
4. Each company is categorized with a category code. Can you find the number of
companies, total funding per category code? Which category has the highest total funding
amount? Visualization: Plot a chart to visualize the total funding amount from different
category codes. Example: Your chart should look like this
© 2021, Tuan Vu. All rights reserved. 2
5. What is the average funding amount for each category code? Is the top 10 highest
average funding category code the same as the top 10 category code in the previous
questions? Why do you think that is?
a. Visualization:
i. Plot a chart to visualize the average funding amount from different
category codes.
ii. Plot a chart to show the percentage of funding amount by different
companies in the top category
6. Show the different number of companies by status between 2 states "CA" and "NY”.
a. Visualization: Plot a chart to visualize the difference between the 2 states
2. CRUNCHBASE_ACQUISITIONS
Answer the following questions using tutorial.crunchbase_acquisitions table
7. Write a query that determines counts of every single column. Which column has the most
null values? Can you check the number of NULL records in that column?
8. Show all the different price_currency_code in this dataset.
9. What is the minimum, maximum, total, and average funding total amount in USD? Find
out which company has the lowest funding amount.
© 2021, Tuan Vu. All rights reserved. 3
10. For each acquirer, find the number of companies they acquire and the total amount they
spend.
a. Visualization: Plot a chart to visualize the top 20 acquirers by number of acquired
companies. Example:
11. What is the number of acquired companies and total acquiring amount each year?
b. Visualization: Plot a chart to visualize the number of acquired companies every
year. Does the trend look good? Which year have the highest amount of
acquisitions?
12. What is the number of acquired companies by category code each year? (Hint: exclude
the NULL values)
c. Visualization: Plot a chart to visualize the number of acquired companies by
company category code every year. Can you find any insights from this graph?
(Hint: look at the peak)
© 2021, Tuan Vu. All rights reserved. 4