ABCDEFGHIJKLMNOPQRSTUVW
1
Sumber DataNoTaskSQL
2
https://docs.google.com/spreadsheets/d/1-GNKodGrRYP-7KrTUhMnJDs0Z8Iem0uF/edit?usp=sharing&ouid=110088446600106737002&rtpof=true&sd=true
1Case Study: Brand Name Extraction from Product Names

Background:
• As BI Analysts, we were tasked to identify the brand names associated with each product in a dataset.
• We requested the BI Engineers to add product names, but due to their workload, they couldn't prioritize our request, and we are facing a tight deadline set by stakeholders.
• Upon initial review of the product data, we observed that in most cases, the brand name appears as the first word in the product name (with a few exceptions that we can overlook given the urgency of the deadline).

Data Expectations:
• Obtain a dataset where each product is associated with its corresponding brand name extracted from the product name.
SELECT
*,
CASE
WHEN product_name LIKE "O'Sullivan%" THEN "O'Sullivan" -- Specific exception handling for a brand
ELSE REGEXP_EXTRACT(product_name, r'^[a-zA-Z0-9_.-]+') -- Extract brand name from product name
END AS brand_name
FROM ade-test-data.Ade_data.Categories;
3
2Case Study: Monthly Analytics on Customer Orders and Discounts


Background:
• As BI Analysts, we need to provide monthly insights into customer behavior and order patterns from the ade-test-data.Ade_data.ade_binar_superstore dataset.
• The analysis aims to understand new customer acquisition, organic versus inorganic orders, organic order ratios, and total discounts issued per month.
• Assumption: Every discount provided is part of a marketing promotional program.

Data Expectations:
• Obtain monthly aggregated metrics including new customers, organic and inorganic orders, organic order ratio, and total discounts issued.
WITH first_date_data AS (
SELECT
Customer_ID,
MIN(Order_Date) AS first_transaction_date
FROM `ade-test-data.Ade_data.ade_binar_superstore`
GROUP BY Customer_ID
),
total_discount_data AS (
SELECT
DATE_TRUNC(Order_Date, MONTH) AS bulan,
Order_Id,
SUM(Discount) AS total_discount
FROM `ade-test-data.Ade_data.ade_binar_superstore`
GROUP BY bulan, Order_Id
),
total_order_data AS (
SELECT
bulan,
COUNT(DISTINCT Order_Id) AS total_order_overall,
COUNT(DISTINCT CASE WHEN total_discount = 0 THEN Order_Id END) AS total_order_organic,
COUNT(DISTINCT CASE WHEN total_discount > 0 THEN Order_Id END) AS total_order_inorganic,
SUM(total_discount) AS total_discount_bulanan
FROM total_discount_data
GROUP BY bulan
)
SELECT
bulan,
total_order_overall,
total_order_organic,
total_order_inorganic,
total_order_organic/total_order_overall AS order_organic_ratio,
total_discount_bulanan
FROM total_order_data
4
3SQL Query to Correct Typo in Region Name
Case Study:
There is a typo in the spelling of one of the regions ('Southn' instead of 'South') in the dataset. We need to correct this typo using SQL.		SELECT
CASE WHEN Region = 'Southn' THEN 'South' ELSE Region END AS Corrected_Region
FROM ade-test-data.Ade_data.ade_binar_superstore;
5
4SQL Query to Identify Priority Customers by Segment
Case Study:
The company has tasked BI Analysts with identifying priority customers within each customer segment based on their total purchases. Priority customers are defined as the top 5 customers with the highest total sales within each segment.		SELECT
o.customer_id,
c.customer_name,
c.segment,
ROW_NUMBER() OVER(PARTITION BY c.segment ORDER BY SUM(o.sales) DESC) AS segment_rank,
ROUND(SUM(o.sales), 0) AS total_sales
FROM ade-test-data.Ade_data.ade_binar_superstore AS o
LEFT JOIN ade-test-data.Ade_data.Customer_View AS c
ON o.customer_id = c.customer_id
GROUP BY o.customer_id, c.customer_name, c.segment
QUALIFY segment_rank BETWEEN 1 AND 5;
6
5SQL Query to Determine Top 10 Best-Selling Products by Total Units Sold
Case Study:
The objective is to identify the top 10 best-selling products based on the total quantity of units sold. This analysis will provide insights into the most popular products in terms of sales volume.		SELECT
b.Product_ID,
b.product_name,
SUM(a.Quantity) AS total_units_sold,
ROW_NUMBER() OVER(ORDER BY SUM(a.Quantity) DESC) AS rank_total_units_sold
FROM ade-test-data.Ade_data.ade_binar_superstore AS a
LEFT JOIN ade-test-data.Ade_data.Categories AS b
ON a.Product_ID = b.Product_ID
GROUP BY b.Product_ID, b.product_name
QUALIFY rank_total_units_sold <= 10;
7
6SQL Query for Monthly Organic Order Ratio
Objective:
Calculate the monthly organic order ratio, which compares the number of organic orders (orders without discount) to the total number of orders placed each month.

SQL Query:		SELECT
DATE_TRUNC(Order_Date, MONTH) AS Month,
COUNT(DISTINCT CASE WHEN Discount = 0 THEN Order_Id END) AS total_order_organic,
COUNT(DISTINCT Order_Id) AS total_order_overall,
COUNT(DISTINCT CASE WHEN Discount = 0 THEN Order_Id END) / COUNT(DISTINCT Order_Id) AS order_organic_ratio
FROM `ade-test-data.Ade_data.ade_binar_superstore`
GROUP BY Month;
8
7SQL Query for Distribution of Ship Modes Used
Objective:
Provide the distribution data of Ship Modes used in all transactions, represented as percentages.		WITH data_transaksi_table AS (
SELECT
COUNT(DISTINCT Order_ID) AS total_transactions,
COUNT(DISTINCT CASE WHEN Ship_Mode = 'Standard Class' THEN Order_ID END) AS standard_class,
COUNT(DISTINCT CASE WHEN Ship_Mode = 'First Class' THEN Order_ID END) AS first_class,
COUNT(DISTINCT CASE WHEN Ship_Mode = 'Second Class' THEN Order_ID END) AS second_class,
COUNT(DISTINCT CASE WHEN Ship_Mode = 'Same Day' THEN Order_ID END) AS same_day
FROM `ade-test-data.Ade_data.ade_binar_superstore`
)

SELECT
CONCAT(ROUND(standard_class / total_transactions * 100, 1), '%') AS standard_class_percentage,
CONCAT(ROUND(first_class / total_transactions * 100, 1), '%') AS first_class_percentage,
CONCAT(ROUND(second_class / total_transactions * 100, 1), '%') AS second_class_percentage,
CONCAT(ROUND(same_day / total_transactions * 100, 1), '%') AS same_day_percentage
FROM data_transaksi_table;
9
8SQL Query for Distribution of Ship Modes Used
Objective:
Provide the distribution data of Ship Modes used in all transactions, presented as percentages.		SELECT Ship_Mode,
CONCAT(ROUND(COUNT(DISTINCT Order_ID) * 100.0 / (SELECT COUNT(DISTINCT Order_ID) FROM ade-test-data.Ade_data.ade_binar_superstore), 1), '%') AS percentage
FROM ade-test-data.Ade_data.ade_binar_superstore
GROUP BY Ship_Mode
ORDER BY percentage DESC;
10
9SQL Query for Total Inorganic Orders per Customer
Objective:
Calculate the total number of inorganic orders (orders with discounts) per customer.		SELECT
a.Customer_ID,
b.Customer_Name,
COUNT(DISTINCT CASE WHEN a.Discount > 0 THEN a.Order_Id END) AS total_order_inorganic
FROM `ade-test-data.Ade_data.ade_binar_superstore` a
JOIN `ade-test-data.Ade_data.Customer_View` b
ON a.Customer_ID = b.Customer_ID
GROUP BY a.Customer_ID, b.Customer_Name;
11
10Total Discount Amount Obtained per Customer

Objective: Calculate the total discount amount obtained per customer.		SELECT
Customer_ID,
Customer_Name,
SUM(Original_Price - Sales) AS Total_Discount
FROM (
SELECT
Order_ID,
Customer_ID,
Customer_Name,
Product_ID,
Sales,
(Sales / (1 - Discount)) AS Original_Price
FROM `ade-test-data.Ade_data.ade_binar_superstore`
JOIN `ade-test-data.Ade_data.Customer_View`
USING (Customer_ID)
)
GROUP BY Customer_ID, Customer_Name;
12
11Case Study: Analysis of Customer Sales in Superstore

Objective: Calculate the total sales amount per customer.		SELECT
Customer_ID,
Customer_Name,
SUM(Sales) AS Total_Sales
FROM ade-test-data.Ade_data.ade_binar_superstore
INNER JOIN ade-test-data.Ade_data.Customer_View
ON Customer_ID = Customer_ID
GROUP BY Customer_ID, Customer_Name;
13
12Case Study: Analysis of Customer Purchases in Superstore

Objective: Calculate the total quantity of items purchased per customer.		SELECT
Customer_ID,
Customer_Name,
SUM(Quantity) AS Total_Quantity
FROM ade-test-data.Ade_data.ade_binar_superstore
INNER JOIN ade-test-data.Ade_data.Customer_View
ON ade_binar_superstore.Customer_ID = Customer_View.Customer_ID
GROUP BY Customer_ID, Customer_Name;
14
13Case Study: Monthly New Customer Analysis in 2017

Objective: Calculate the number of new customers each month in 2017.		WITH first_date_data AS (
SELECT
Customer_ID,
DATE_TRUNC(MIN(Order_Date), MONTH) AS first_transaction_month
FROM ade-test-data.Ade_data.ade_binar_superstore
WHERE EXTRACT(YEAR FROM Order_Date) = 2017
GROUP BY Customer_ID
)
SELECT
first_transaction_month,
COUNT(DISTINCT Customer_ID) AS new_customer
FROM first_date_data
GROUP BY first_transaction_month
ORDER BY first_transaction_month ASC;
15
14Case Study: Monthly Organic Orders Analysis

Objective: Calculate the number of orders without any discount each 		SELECT
DATE_TRUNC(Order_Date, MONTH) AS Month,
COUNT(DISTINCT CASE WHEN Discount = 0 THEN Order_Id END) AS total_order_organic
FROM `ade-test-data.Ade_data.ade_binar_superstore`
GROUP BY Month;
16
15Case Study: Monthly Inorganic Orders Analysis

Objective: Calculate the number of orders with any discount each month.		SELECT
DATE_TRUNC(Order_Date, MONTH) AS Month,
COUNT(DISTINCT CASE WHEN Discount > 0 THEN Order_Id END) AS total_order_inorganic
FROM `ade-test-data.Ade_data.ade_binar_superstore`
GROUP BY Month;
17
18
19
20
21
22
23
24
25
26
27
28
29
30
31
32
33
34
35
36
37
38
39
40
41
42
43
44
45
46
47
48
49
50
51
52
53
54
55
56
57
58
59
60
61
62
63
64
65
66
67
68
69
70
71
72
73
74
75
76
77
78
79
80
81
82
83
84
85
86
87
88
89
90
91
92
93
94
95
96
97
98
99
100