SQL for Data Analyst
Based on the World Economic Forum (WEF), the data analyst profession is strategically ranked as the most needed job in 2025. Data analyst is someone who is responsible for processing data to extract useful information. Data can be used for minimizing assumptions, making informed decisions, maximizing opportunities, making predictions, seeing trends, and so on.This job is extremely important in a variety of fields of work, particularly in this digitalization era. I seized the chance to launch a career as a data analyst because of the extremely excellent work prospects and my interest in data.
To become a data analyst, in addition to having good analytical and communication skills, you also need the ability to run programming languages such as SQL, BI tools, Python, and other data visualization tools. Of the many types of tools that I know and have used, only Excel Due to a great desire, I decided to take part in the bootcamp held by MySkill. In this bootcamp, I and other participants were taught how to use SQL, Python, and Google Data Studio.
This paper is one of the final assignments and consists of three parts. In the first part, I will discuss SQL. SQL (Structured Query Language) is the language used to communicate with databases. SQL is used to create databases, input data, update data, and so on. The SQL application used is PostgreSQL.
Given a dataset containing companies classified as unicorns in early April 2022. The dataset is then imported by opening the Query Tool > opening the SQL script file in pgAdmin> run.
After successfully importing the dataset, the data is then processed based on request as follows
Question 1
Sort continents by the largest number of companies. Which continent has the most unicorns?
The data needed is the continent and company for that the data will be taken from the unicorn_companies dataset. Then the data will be sorted by continent with the highest number of companies so that the query used is
Distinct is used so that none of the same company_id will beaffected
Question 2
What countries have the number of unicorns above 100?
It takes data on the country and the number of unicorns that can be found on the unicorn_companies dataset. The data shown is countries with more than 100 unicorns using the HAVING syntax.
Question 3
What is the biggest industry among unicorn companies based on their total funding? What is the average valuation?
It takes data on the industry with the largest total funding and its average valuation. The total funding data is in the unicorn_funding dataset to be obtained using the SUM aggregate function while the average valuation data is in the dataset obtained using the AVG unicorn_industries aggregate function. Kedua dataset will be combined with the join function, which is inner join. So that the query to be used is:
Question 4
Based on this dataset, for the number 3 answer industry, how many companies join as unicorns each year in the 2016–2022 range?
The data requested is the number of Fintech industry companies from 2016–2022. Year data is obtained by extracting data from date_joined column using the EXTRACT function There are 3 datasets used, namely unicorn_companies, unicorn_industries, and unicorn_dates. Multiple INNER JOIN is used to combine these three datasets. To retrieve company data from the Fintech industry from the 2016–2022 range, the AND syntax used.
Question 5
Display detailed company data (company name, city of origin, country and continent of origin) along with industry and valuation. Which countries do the companies with the largest valuations come from and what are the industries?
What about Indonesia? What company has the most valuation in Indonesia?
Get Helena Paulina’s stories in your inbox
Join Medium for free to get updates from this writer.
The company detail data is in 3 different datasets, so multiple INNER JOINS are used. To only display the country of Indonesia, the WHERE clause is added as below.
Question 6
How old was the oldest company when the company merged into a unicorn company? Which country is the company from?
The data that will be displayed are company_id, company, country, date joined, year joined and company age. Company age data is obtained by extracting years from the date_joined column.
Question 7
For companies established between 1960 and 2000 (upper and lower limits fall into the range), how old was the oldest company when the company merged into a unicorn company (date_joined)? Which country is the company from?
The data needed is a company founded between 1960–2000, the age of the company is sorted from the oldest and the origin of the company’s country. The age of the company is obtained by extracting the years from the column date_joined subtracted by the years of self-esteem. Because the data is in 2 datasets so an INNER JOIN is used. Ufor the range of 1960–2000 used syntax AND.
Question 8
a. How many companies are financed by at least one investor that bears the name ‘venture’?
Investor name data is contained in the unicorn_funding dataset. However only investors contain the name ‘venture’ so a WHERE clause is used to filter and a LIKE operator for the specific name ‘venture’. Used string lower to change values in columns in lowercase.
b. How many companies are financed by at least one investor that contains the name:
- Venture
- Capital
- Partner
The CASE WHEN statement is used. CASE WHEN is the same as the IF Else function in Excel. A case statement passes a condition and returns the value when the first condition is met (such as an if-then-else statement). So, once a condition is correct, it will stop reading and return the result. LIKE is used for specific filters.
Question 9
In Indonesia, there are many startups engaged in logistics services. How many logistics startups are there unicorns in Asia? How many logistics startups are unicorns in Indonesia?
Just like the previous question, CASE WHEN is used. WHERE clause is used to filter the logistics industry and the Asian continent
For me who has no background in IT, these questions are a challenge. Quite a lot of syntax is used in these nine questions but there are many other syntax that I need to master to get to know SQL better. So, in the second part I’m going to talk about Python. You can directly click this link https://medium.com/@helenapaulina55/python-for-data-analys-39312bc54516
