ANALYSIS ON
SUPERMART_DB
1. Sales Performance by Region
QUESTION: What is the total sales amount by region?
SOLUTION:
SELECT [Link], SUM([Link])
FROM customer AS c INNER JOIN sales AS s
ON c.customer_id=s.customer_id GROUP BY 1;
2. Top-Selling Products
QUESTION: Which products generated the most sales?
SOLUTION:
SELECT p.product_id, p.product_name, SUM([Link]) AS totalsales
FROM product AS p INNER JOIN sales AS s
ON p.product_id=s.product_id
GROUP BY 1,2 ORDER BY 3 DESC;
3. Discount Impact on Profit
QUESTION: How does the discount affect profit?
SOLUTION:
SELECT CASE
WHEN discount=0 THEN 'NO DISCOUNT OR 0% DISCOUNT'
WHEN discount>0 AND discount<=0.5 THEN ' 1-50% DISCOUNT'
ELSE ' 50%+ DISCOUNT'
END AS discount_range, AVG(profit) AS avg_profit, SUM(sales) AS total_sales,
SUM(profit) AS total_profit FROM sales
GROUP BY discount_range ORDER BY total_profit DESC;
(MAX PROFIT IS IN 0% DISCOUNT AND LEAST IS IN 50+% DISCOUNT)
4.
Sales
by
Customer Segment
QUESTION: How much sales does each customer segment contribute?
SOLUTION:
SELECT [Link], SUM([Link]) AS total_sales
FROM customer AS c INNER JOIN sales AS s
ON c.customer_id=s.customer_id
GROUP BY 1 ORDER BY 2 ASC;
5. Product Category Sales
QUESTION: What are the total sales for each product category?
SOLUTION:
SELECT [Link], SUM([Link]) AS totalsales
FROM product AS p INNER JOIN sales AS s
ON p.product_id=s.product_id
GROUP BY 1 ORDER BY 2 ASC;
6.
Customer Orders by Ship Mode
QUESTION: How many orders were shipped by each shipping mode?
SOLUTION:
SELECT ship_mode, COUNT(quantity) FROM sales
GROUP BY 1 ORDER BY 2;
7.
Sales by Date
QUESTION: What are the total sales for each month?
SOLUTION:
SELECT EXTRACT(MONTH FROM order_date) AS order_by_month,
SUM(sales) AS total_sales FROM sales
GROUP BY 1 ORDER BY 1;
8.
Customer Distribution by State
QUESTION: How many customers are there in each state?
SOLUTION:
SELECT state, COUNT(customer_id)
FROM customer GROUP BY 1 ORDER BY 2 DESC;
9.
Top
5
Customers by Sales
QUESTION: Who are the top 5 customers in terms of total sales?
SOLUTION:
SELECT c.customer_name,c.customer_id, SUM([Link]) AS total_sales
FROM sales AS s INNER JOIN customer AS c
ON c.customer_id= s.customer_id
GROUP BY 1,2 ORDER BY 3 DESC LIMIT 5;
(For name we will perform join)
10. Product Performance in Subcategories
QUESTION: What is the total sales for each product subcategory?
SOLUTION:
SELECT p.sub_category, SUM([Link]) AS total_sales
FROM product AS p INNER JOIN sales AS s
ON p.product_id=s.product_id GROUP BY 1 ORDER BY 2 DESC;
11.
Rank Products by Sales
QUESTION: How can we rank products by their total sales within each product
category?
SOLUTION:
SELECT * FROM(
SELECT *, DENSE_RANK() OVER(PARTITION BY category ORDER BY total_sales DESC)
AS ranks FROM(SELECT p.product_id, p.product_name, [Link],
SUM([Link]) AS total_sales FROM sales AS s INNER JOIN product AS p
ON s.product_id=p.product_id GROUP BY 1,2,3 ORDER BY 4)
AS rnk) AS top_rank WHERE ranks<=3;
12.
Cumulative Sales by Date
QUESTION: How can we calculate cumulative sales over time (running total) for
each product?
SOLUTION:
SELECT*FROM(SELECT product_id, order_date, FLOOR(sales) AS acc_sales,
SUM(FLOOR(sales)) OVER(PARTITION BY product_id
ORDER BY order_date) AS running_total FROM sales) AS pro;
[Link] Top 3 Customers by Profit
QUESTION: How can we find the top 3 customers based on profit within each
region?
SOLUTION:
SELECT * FROM(
SELECT*, DENSE_RANK() OVER(PARTITION BY region ORDER BY total_profit DESC)
AS ranks FROM(SELECT c.customer_id, c.customer_name,[Link],
SUM([Link]) as total_profit FROM customer AS c INNER JOIN sales AS s
ON c.customer_id=s.customer_id GROUP BY 1,2) AS top_3) AS top WHERE ranks<=3;
[Link] Sales by Segment with Row Number
QUESTION: How can we find the average sales for each segment and assign a row
number to each customer based on their sales?
SOLUTION:
SELECT*, ROW_NUMBER() OVER(PARTITION BY segment ORDER BY avg_sales) AS
row_num_for_avg_sales FROM( SELECT [Link],c.customer_id,c.customer_name,
AVG([Link]) AS avg_sales FROM customer AS c INNER JOIN sales AS s ON
c.customer_id=s.customer_id GROUP BY 2,3) AS sales_avg;
[Link] in Sales Between Consecutive Days
QUESTION: How can we calculate the difference in sales between consecutive
days for each product?
SOLUTION:
SELECT*,LAG(sales, 1) OVER (PARTITION BY product_name ORDER BY order_date) AS
sls_diff_of_cons_day FROM(SELECT p.product_name, p.product_id, s.order_date SUM([Link])
as sales FROM product AS p INNER JOIN sales AS s ON p.product_id=s.product_id GROUP BY
1,2,3 ORDER BY 4) AS sls_of_cons_day;
[Link] Percent of Total Sales by Region
QUESTION: How can we calculate the percentage of total sales contributed by
each region?
SOLUTION:
SELECT [Link], SUM([Link]) AS region_sales, SUM([Link]) * 100.0 / SUM(SUM([Link]))
OVER () AS sales_percent FROM customer AS c INNER JOIN sales AS s
ON c.customer_id = s.customer_id GROUP BY [Link];
[Link] Moving Average of Sales
QUESTION: How can we calculate the moving average of sales over the last 3
orders for each product?
SOLUTION:
SELECT p.product_name, s.order_date, FLOOR(AVG([Link])) AS avg_sales,
AVG(FLOOR([Link])) OVER (PARTITION BY p.product_name ORDER BY s.order_date
ROWS BETWEEN 2 PRECEDING AND CURRENT ROW) AS moving_avg FROM product AS
p INNER JOIN sales AS s ON p.product_id=s.product_id GROUP BY 1,2,[Link];
[Link] Largest and Smallest Order by Customer
QUESTION: How can we find the largest and smallest order (by sales) for each
customer?
SOLUTION:
SELECT [Link], c.customer_name,
MIN([Link]) OVER (PARTITION BY c.customer_name) AS min_sales,
MAX([Link]) OVER (PARTITION BY c.customer_name) AS max_sales
FROM customer AS c INNER JOIN sales AS s ON c.customer_id = s.customer_id;
[Link] Total of Profit by Customer
QUESTION: How can we calculate the running total of profit for each customer?
SOLUTION:
SELECT c.customer_name, s.order_date, FLOOR([Link]), SUM(FLOOR([Link])) OVER
(PARTITION BY c.customer_name ORDER BY s.order_date) AS running_profit
FROM customer AS c INNER JOIN sales AS s ON c.customer_id = s.customer_id;
[Link] Dense Rank of Sales by Ship Mode
QUESTION: How can we assign a dense rank to each sale based on total sales,
grouped by ship mode?
SOLUTION:
SELECT ship_mode, sales, DENSE_RANK() OVER (PARTITION BY ship_mode
ORDER BY sales DESC) AS sales_total_rank FROM sales GROUP BY 1,2;
DRIVE LINK FOR .SQL FILE FOR MINI PROJECT:
[Link]
HVHd/view?usp=drive_link
THANK YOU