SQL Projects

Overview

ATTENTION! If the cells with the code are not displayed, then you need to log in.

Here I gathered some of my works with SQL (specifically with PostgeSQL). Each task contains:

background information
structure of database
task description
my code with its result.

Following skills have been showed:

Dealing with the if/then/else logic in SQL
Creating complex nested queries
Using common table expressions
Applying SQL window functions
Working with dates and times
Performing mathematical operations with complicated logic
Aggregating and describing data (minimum, maximum, average, count, and sum)
Filtering data based on a certain condition or several conditions

If you got any question feel free to contact, I will be delighted to answer. Happy reading!

P.S. I've included a bonus, one of my masterpieces, finding median in any quantitative category without built-in functions.

1. Top 5 States With 5 Star Businesses

Find the top 5 states with the most 5 star businesses. Output the state name along with the number of 5-star businesses and order records by the number of 5-star businesses in descending order. In case there are ties in the number of businesses, return all the unique states. If two states have the same result, sort them in alphabetical order.

Column Name	Data Type	Description
business_id	varchar	Unique identifier for each business
name	varchar	Name of the business
neighborhood	varchar	Neighborhood where the business is located
address	varchar	Street address of the business
city	varchar	City where the business is located
state	varchar	State where the business is located
postal_code	varchar	Postal code of the business
latitude	float	Latitude coordinate of the business location
longitude	float	Longitude coordinate of the business location
stars	float	Average rating of the business
review_count	int	Number of reviews for the business
is_open	int	Indicator if the business is open (1) or closed (0)
categories	varchar	Categories or types of the business

Expected Output

state	n_businesses
AZ	10
ON	5
NV	4
IL	3
OH	3

DataFrameas

df3

variable

SELECT *
  FROM 'yelp_business.csv'

DataFrameas

df1

variable

  WITH ranking AS (
SELECT state,
       COUNT(stars) FILTER (WHERE stars = 5)                                   AS n_business,
       DENSE_RANK() OVER (ORDER BY COUNT(stars) FILTER (WHERE stars = 5) DESC) AS ranks
  FROM 'yelp_business.csv'
 GROUP BY 1
 ORDER BY 2 DESC, 1
)
SELECT state, n_business
  FROM ranking
 WHERE ranks < 5

2. Monthly Percentage Difference

Given a table of purchases by date, calculate the month-over-month percentage change in revenue. The output should include the year-month date (YYYY-MM) and percentage change, rounded to the 2nd decimal point, and sorted from the beginning of the year to the end of the year. The percentage change column will be populated from the 2nd month forward and can be calculated as ((this month's revenue - last month's revenue) / last month's revenue)*100.

Expected Output

year_month	revenue_diff_pct
2019-01
2019-02	-28.56
2019-03	23.35
2019-04	-13.84
2019-05	13.49

DataFrameas

df4

variable

SELECT *
  FROM 'sf_transactions.csv'

DataFrameas

df2

variable

Run cancelled

  WITH sub AS (
SELECT DISTINCT
       DATE_TRUNC('month', created_at)                                 AS date,
       SUM(value) OVER (PARTITION BY DATE_TRUNC('month', created_at)) AS month_rev
  FROM 'sf_transactions.csv'
 ORDER BY date
)
SELECT date,
       ((month_rev - LAG(month_rev) OVER (ORDER BY date)) /
        LAG(month_rev) OVER (ORDER BY date)) * 100 AS revenue_diff_pct
  FROM sub

3. Analyzing Unicorn Companies

Did you know that the average return from investing in stocks is 10% per year (not accounting for inflation)? 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.

The output

Your query should return a table in the following format:

industry	year	num_unicorns	average_valuation_billions
industry1	2021	---	---
industry2	2020	---	---
industry3	2019	---	---
industry1	2021	---	---
industry2	2020	---	---
industry3	2019	---	---
industry1	2021	---	---
industry2	2020	---	---
industry3	2019	---	---

Where industry1, industry2, and industry3 are the three top-performing industries.

Project Instructions

Your task is to first identify the three best-performing industries based on the number of new unicorns created in 2019, 2020, and 2021 combined.

From those industries (1), you will need to find the number of unicorns within these industries (2), the year that they became a unicorn (3), and their average valuation, converted to billions of dollars and rounded to two decimal places (4).

With the above information you can then finish your query to return a table containing industry, year, num_unicorns, and average_valuation_billions. For readability, the firm have asked you to sort your results by year and number of unicorns, both in descending order.

DataFrameas

df

variable

Run cancelled

  WITH top_industries AS (SELECT industry
                            FROM (SELECT ind1.industry, COUNT(dt1.company_id) AS cnt
                                    FROM public.dates dt1
                                         JOIN public.industries ind1
                                         USING (company_id)
                                   WHERE dt1.date_joined >= '2019-01-01'
                                     AND dt1.date_joined < '2022-01-01'
                                   GROUP BY 1
                                   ORDER BY 2 DESC
                                   LIMIT 3) AS counting)

SELECT ind.industry,
       LEFT(dt.date_joined::TEXT, 4)           AS year,
       COUNT(dt.company_id)                    AS num_unicorns,
       ROUND(AVG(f.valuation) / 1000000000, 2) AS average_valuation_billions
  FROM public.dates dt
       JOIN public.industries ind
       USING (company_id)
       JOIN top_industries ti
       ON ti.industry = ind.industry
       JOIN public.funding f
       ON f.company_id = ind.company_id
 WHERE dt.date_joined >= '2019-01-01'
   AND dt.date_joined < '2022-01-01'
 GROUP BY 1, 2
 ORDER BY 2 DESC, 3 DESC

4. Analyzing Motorcycle Part Sales

You're working for a company that sells motorcycle parts, and they've asked for some help in analyzing their sales data!

They operate three warehouses in the area, selling both retail and wholesale. They offer a variety of parts and accept credit cards, cash, and bank transfer as payment methods. However, each payment type incurs a different fee.

The board of directors wants to gain a better understanding of wholesale revenue by product line, and how this varies month-to-month and across warehouses. You have been tasked with calculating net revenue for each product line and grouping results by month and warehouse. The results should be filtered so that only "Wholesale" orders are included.

They have provided you with access to their database, which contains the following table called sales:

Sales

Column	Data type	Description
`order_number`	`VARCHAR`	Unique order number.
`date`	`DATE`	Date of the order, from June to August 2021.
`warehouse`	`VARCHAR`	The warehouse that the order was made from— `North`, `Central`, or `West`.
`client_type`	`VARCHAR`	Whether the order was `Retail` or `Wholesale`.
`product_line`	`VARCHAR`	Type of product ordered.
`quantity`	`INT`	Number of products ordered.
`unit_price`	`FLOAT`	Price per product (dollars).
`total`	`FLOAT`	Total price of the order (dollars).
`payment`	`VARCHAR`	Payment method—`Credit card`, `Transfer`, or `Cash`.
`payment_fee`	`FLOAT`	Percentage of `total` charged as a result of the `payment` method.

Your query output should be presented in the following format:

`product_line`	`month`	`warehouse`	`net_revenue`
product_one	---	---	---
product_one	---	---	---
product_one	---	---	---
product_one	---	---	---
product_one	---	---	---
product_one	---	---	---
product_two	---	---	---
...	...	...	...

DataFrameas

df5

variable

SELECT *
  FROM public.sales

DataFrameas

revenue_by_product_line

variable

Run cancelled

SELECT product_line,
       TO_CHAR(date, 'Month')                            AS month,
       warehouse,
       ROUND(SUM(total * (1 - payment_fee))::NUMERIC, 2) AS net_revenue
  FROM sales
 WHERE client_type = 'Wholesale'
 GROUP BY 1, 3, 2
 ORDER BY 1, 2, 4 DESC

Bonus: Find Median

Find median value in valuation without using buil-in function for median. Then compare result with the result of built in function.

‌
‌
‌

.mfe-app-workspace-kj242g{position:absolute;top:-8px;}.mfe-app-workspace-11ezf91{display:inline-block;}.mfe-app-workspace-11ezf91:hover .Anchor__copyLink{visibility:visible;}Overview

1. Top 5 States With 5 Star Businesses

2. Monthly Percentage Difference

3. Analyzing Unicorn Companies

4. Analyzing Motorcycle Part Sales

Bonus: Find Median

Overview