ASSIGNMENT: SQL JOINS
OVERVIEW
SQL Join is powerful tool that let you combine all the data together. Joins allow you do deeper
and more detail analysis on an expanded set of information.
In this assignment, we will still be using the data from the Crunchbase dataset. But we will be
able to connect all the data together and answer more detail questions and generate more insights
for our Exploratory Data Analysis
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 are the tables called?
• You check a full list of tables here. But for this assignment, we will be using the
following three 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.
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. The foreign key you use to join these two tables will depend entirely on
whether you're looking to add information about the acquiring company or the company
that was acquired.
© 2021, Tuan Vu. All rights reserved.
SELECT *
FROM tutorial.crunchbase_acquisitions
• tutorial.crunchbase_investments: This table lists investments—one row per
investment. company_permalink in this table. There can be multiple investments per
company—it's even possible that one investor could invest in the same company multiple
times. The column names are pretty self-explanatory. The company_permalink field in
the tutorial.crunchbase_investments table maps to permalink in the
tutorial.crunchbase_companies table.
SELECT *
FROM tutorial.crunchbase_investments
PROBLEM: JOINS QUESTIONS
Before working on the problems, please spend some time familiar yourself with the tables. By
asking yourself multiple questions, you will be able to explore and extract lots of interesting
insights from the dataset.
1. Write a query that shows a company's name, "status" (found in the
crunchbase_companies table), and the number of unique investors in that company.
Limit to only companies in the state of New York. Sort the number of investors from
most to fewest. Also think about what type of Join you use, and why you choose it over
the others?
2. Write a query to show how many companies with no investors and how many that has
investors. Visualization: Show the difference between number of companies with vs
without investors. Example: Your chart should look like this
© 2021, Tuan Vu. All rights reserved. 2
3. What is the percentage of companies that being acquired vs not being acquired with
investors and without investors? Visualization: Can you show the percentage difference
in these 2 cases?
4. Of all the company being invested, write a query that compares the average amount of
investment raised by companies in 'San Francisco' city to companies in 'NY' city and by
status. Visualization: Show the difference in amount of investment by status between
these two cities. Which company status has on average the largest amount of investment?
5. Of all the company being invested, write a query that show the average amount of
investment raised by companies in the US by status every year. Visualization: Show the
average investment amount by status over year.
© 2021, Tuan Vu. All rights reserved. 3