analyzing clothing company business performance
using MS. SQL SERVER STUDIO
SQL project/case study from: https://8weeksqlchallenge.com/case-study-7/
steps:
-
import all csv files into SQL SERVER
-
data checking -> clean
-
analysis:
-
High Level Sales Analysis
-
What was the total quantity sold for all products?
-
What is the total generated revenue for all products before discounts?
-
What was the total discount amount for all products?
-
-
Transaction Analysis
-
How many unique transactions were there?
-
What is the average unique products purchased in each transaction?
-
What are the 25th, 50th and 75th percentile values for the revenue per transaction?
25th, 50th and 75th percentile values for the revenue per transaction
with satu as (select txn_id, sum(qty * (price-(discount*price/100.0))) as revenue_per_trx from clothing_sales group by txn_id)
select distinct PERCENTILE_disc(0.25) within group (order by revenue_per_trx) over() as percentile_25th, PERCENTILE_disc(0.5) within group (order by revenue_per_trx) over() as percentile_50th, PERCENTILE_disc(0.75) within group (order by revenue_per_trx) over() as percentile_75th from satu; -
What is the average discount value per transaction?
-
What is the percentage split of all transactions for members vs non-members?
percentage split of all transactions for members vs non-members
with member as (select COUNT(distinct txn_id) as member from clothing_sales where member = 't'),
non_member as (select COUNT(distinct txn_id) as non_member from clothing_sales where member = 'f'),
altogether as (select COUNT(distinct txn_id) as member from clothing_sales )
select (select * from member) * 100.0 / (select * from altogether) as pct_member, (select * from non_member) * 100.0 / (select * from altogether) as pct_non_member; -
What is the average revenue for member transactions and non-member transactions?
average revenue for member transactions and non-member transactions
with satu as (select *, qty * (price*(1-(discount/100.0))) as rev from clothing_sales),
member as (select member, AVG(rev) as avg_rev_member from satu where member = 't' group by member),
non_member as (select member, AVG(rev) as avg_rev_non_member from satu where member = 'f' group by member)
select avg_rev_member, avg_rev_non_member from member, non_member;calculate after discount
-
-
Product Analysis
-
What are the top 3 products by total revenue before discount?
-
What is the total quantity, revenue and discount for each segment?
total quantity, revenue and discount for each segment
with satu as (select pd.segment_name, s.qty, s.price, s.discount from clothing_sales s join clothing_product_details pd on s.prod_id = pd.product_id)
select segment_name, SUM(qty) as total_quantity, SUM(qty*price) as total_revenue_before_disc, -- before discount SUM(qty*price*(1-(discount/100.0))) as total_revenue_after_disc, -- after discount SUM(qty*price*(discount/100.0)) as total_discount from satu group by segment_name; -
What is the top selling product for each segment?
top selling product for each segment
with satu as (select pd.segment_name, pd.product_name, sum(s.qty) as total_selling from clothing_sales s join clothing_product_details pd on s.prod_id = pd.product_id group by pd.segment_name, pd.product_name),
dua as (select *, RANK() over(partition by segment_name order by total_selling desc) as rn from satu)
select segment_name, product_name, total_selling from dua where rn = 1; -
What is the total quantity, revenue and discount for each category?
total quantity, revenue and discount for each category
select pd.category_name, sum(s.qty) as total_quantity, sum(s.qty*s.price) as total_revenue_before_discount, SUM(s.qty*s.price*(1-(s.discount/100.0))) as total_revenue_after_disc, SUM(s.qty*s.price*(s.discount/100.0)) as total_discount from clothing_sales s join clothing_product_details pd on s.prod_id = pd.product_id group by pd.category_name;
-
What is the top selling product for each category?
top selling product for each category
with satu as (select pd.category_name, pd.product_name, sum(s.qty) as total_selling from clothing_sales s join clothing_product_details pd on s.prod_id = pd.product_id group by pd.category_name, pd.product_name),
dua as (select *, RANK() over(partition by category_name order by total_selling desc) as rn from satu)
select category_name, product_name, total_selling from dua where rn = 1; -
What is the percentage split of revenue by product for each segment?
percentage split of revenue by product for each segment
with satu as (select pd.segment_name, pd.product_name, sum(s.qty*s.price) as total_revenue_before_discount, SUM(s.qty*s.price*(1-(s.discount/100.0))) as total_revenue_after_disc from clothing_sales s join clothing_product_details pd on s.prod_id = pd.product_id group by pd.segment_name, pd.product_name)
select segment_name, product_name, total_revenue_before_discount, round(total_revenue_before_discount * 100.0 / SUM(total_revenue_before_discount) over(partition by segment_name), 0) as pct_before_disc, total_revenue_after_disc, round(total_revenue_after_disc * 100.0 / SUM(total_revenue_after_disc) over(partition by segment_name), 0) as pct_after_disc from satu; -
What is the percentage split of revenue by segment for each category?
percentage split of revenue by segment for each category
with satu as (select pd.category_name, pd.segment_name, sum(s.qty*s.price) as total_revenue_before_discount, SUM(s.qty*s.price*(1-(s.discount/100.0))) as total_revenue_after_disc from clothing_sales s join clothing_product_details pd on s.prod_id = pd.product_id group by pd.category_name, pd.segment_name)
select category_name, segment_name, total_revenue_before_discount, round(total_revenue_before_discount * 100.0 / SUM(total_revenue_before_discount) over(partition by category_name), 0) as pct_before_disc, total_revenue_after_disc, round(total_revenue_after_disc * 100.0 / SUM(total_revenue_after_disc) over(partition by category_name), 0) as pct_after_disc from satu; -
What is the percentage split of total revenue by category?
percentage split of total revenue by category
with satu as (select pd.category_name, sum(s.qty*s.price) as total_revenue_before_discount, SUM(s.qty*s.price*(1-(s.discount/100.0))) as total_revenue_after_disc from clothing_sales s join clothing_product_details pd on s.prod_id = pd.product_id group by pd.category_name),
dua as (select SUM(total_revenue_before_discount) as total_before from satu),
tiga as (select SUM(total_revenue_after_disc) as total_after from satu)
select category_name, total_revenue_before_discount, round(total_revenue_before_discount * 100.0/ (select total_before from dua), 0) as pct_revenue_before_disc, total_revenue_after_disc, round(total_revenue_after_disc * 100.0 / (select total_after from tiga), 0) as pct_revenue_after_disc from satu; -
What is the total transaction “penetration” for each product? (hint: penetration = number of transactions where at least 1 quantity of a product was purchased divided by total number of transactions)
total transaction “penetration” for each product
with satu as (select txn_id, prod_id, qty from clothing_sales),
dua as (select prod_id, COUNT(distinct txn_id) as number_of_trx from satu group by prod_id),
tiga as (select prod_id, COUNT(distinct txn_id) as number_of_1_qty from satu where qty = 1 group by prod_id)
select t.prod_id, t.number_of_1_qty, d.number_of_trx, round(t.number_of_1_qty * 1.0 / d.number_of_trx, 2) as penetration from tiga t join dua d on t.prod_id = d.prod_id; -
What is the most common combination of at least 1 quantity of any 3 products in a 1 single transaction?
most common combination
with satu as (select prod_id, qty, txn_id from clothing_sales where txn_id in (select distinct txn_id from clothing_sales where qty = 1)),
dua as (select s1.prod_id as prod_id_1, s2.prod_id as prod_id_2, s3.prod_id as prod_id_3, CONCAT(s1.prod_id, ' - ', s2.prod_id, ' - ', s3.prod_id) as mix from satu s1 join satu s2 on s1.prod_id < s2.prod_id and s1.txn_id = s2.txn_id join satu s3 on s2.prod_id < s3.prod_id and s2.txn_id = s3.txn_id where s1.qty = 1 and s2.qty > 1 and s3.qty > 1)
select prod_id_1, prod_id_2, prod_id_3, mix, COUNT(mix) as cnt_common from dua group by prod_id_1, prod_id_2, prod_id_3, mix order by COUNT(mix) desc;
-
-