SQL Data Analysis Starting with Ecommerce Data

Practice Queries

Practice Queries

Orders Table Review

Bigquery table name meaning

JOIN to join other tables

[Practice] Merging product information tables using JOIN

Perform numerical operations with arithmetic operators

[Practice] Numerical Operations with Arithmetic Operators

[Practice] Finding sales by order using arithmetic operators

Practice Queries

Aggregate functions that summarize data

[Practice] Aggregate function example

[Practice] Aggregating sales by order

Defining the problem

Practice Queries

WHERE to filter data

Boolean Expressions - Asking questions that can be answered with yes or no

Combine questions with AND, OR and switch to NOT

[Practice] Boolean expressions

Setting a period using the comparison operator, BETWEEN

[Practice] Setting a period using comparison operators or BETWEEN

Problem! Should refunds and cancellations be excluded?

Excluding refund and cancellation transactions 1 - Intuitively using NOT and AND

[Practice] Excluding refunds and cancellation transactions 1 - NOT, AND

Excluding refunds and cancellation transactions 2 - DeMorgan's Law, simplified to IN

[Practice] Excluding refund and cancellation transactions 2 - DeMorgan's law, simplified to IN

Practice Queries

[Question] “Which one do you think contributed more to sales, men or women?”

Understanding GROUP BY

[Note] Google Drive Dataset Link

[Practice] Understanding GROUP BY

[Practice] Group by men and women and calculate total sales

[Question] “I want to know which age group has made the most recent purchases.”

Group by age group with CASE WHEN and get total sales

Why group data?

[Practice] Group by age group using CASE WHEN and calculate total sales

[Question] “What are the recent sales trends?”

View sales trends by date

[Practice] View sales trends by date

[Practice] View sales trends - by week, by month

Practice Queries

[Question] “How many customers are there who signed up but never made a payment?”

INNER JOIN, OUTER JOIN, CROSS JOIN all organized

[Practice] Finding customers with no sales using OUTER JOIN (small dataset)

[Practice] Finding customers with no sales using OUTER JOIN

Daily, weekly, monthly time division and movement aggregation

Windows Function 1 - OVER clause theory (PARTITION BY, ORDER BY, BETWEEN)

Windows Function 2 - Aggregating with Example Data

[Practice] Using window functions on example data

[Practice] Finding the total sales movement for the 7th and 28th with SUM OVER

[Practice] Smoothing daily sales data with AVG OVER

[Practice] Calculating percentages using SUM OVER

[Question] “I want to know the best-selling brand… and the least-selling brand!”

Understanding ROW_NUMBER, RANK, DENSE_RANK

[Practice] Try using RANK, DENSE_RANK, ROW_NUMBER

[Practice] Calculating sales by brand

[Practice] Extracting Top 5 and Bottom 5 Products Using ORDER BY and LIMIT

[Practice] Extracting Top5 and Bottom5 with one query using the ranking function

Sales Dashboard Review

Create reports and connect data sources

Create a score card

Controlling the period with parameters

Add a time series chart

Chart Data Analysis

User Analytics Dashboard Review

Add a bar chart of sales percentage by user age and gender

Analyzing gender contribution to sales using a pie chart

Analyze sales ratio of top 5 inflow channels with bar chart

Analyzing the Top 5 Countries by Sales with Bar Charts

Visualize regional sales breakdown with a balloon map chart

Product Dashboard Review

Analyzing top 5 sales brands with bar graphs

Analyzing top 5 categories with bar graphs

Visualizing sales by category with treemap

Class Summary