Resume Project challenge of providing insight on Consumer Goods Domain

request1:

Provide the list of markets in which customer "Atliq Exclusive" operates its business in the APAC region.

    
    select distinct(market) from dim_customer
    where customer = 'Atliq Exclusive'
    and region = 'APAC

request2:

What is the percentage of unique product increase in 2021 vs. 2020? The final output contains these fields,unique_products_2020 unique_products_2021 percentage_chg

                    with 
                    Unique_Product_2020 as(
                    select count(distinct(product_code)) as unique_product_2020
                    from fact_sales_monthly
                    where fiscal_year = 2020),
                    Unique_Product_2021 as(
                    select count(distinct(product_code)) as unique_product_2021
                    from fact_sales_monthly
                    where fiscal_year = 2021),
                    Percentage_Change as(
                    select
                    round(((unique_product_2021-unique_product_2020)/unique_product_2020)*100)
                    as pct_change 
                    from Unique_Product_2021,
                         Unique_Product_2020)
                    select * from Unique_Product_2020,
                                  Unique_Product_2021,
                                  Percentage_Change;

request 3:

Provide a report with all the unique product counts for each segment and sort them in descending order ofproduct counts. The final output contains 2 fields,segment product_count

select segment ,count(distinct(product_code)) as product_count
from dim_product
group by segment 
order by product_count desc;select segment ,count(distinct(product_code)) as product_count
from dim_product
group by segment 
order by product_count desc; 

request 4:

Follow-up: Which segment had the most increase in unique products in 2021 vs 2020? The final output contains these fields, segment product_count_2020 product_count_2021 difference

with
Unique_Segment_Product_2020 as(
select t2.segment,
count(distinct(t1.product_code)) as unique_product_2020
from fact_sales_monthly t1 join dim_product t2
on t1.product_code = t2.product_code
where fiscal_year = 2020
group by t2.segment),
Unique_Segment_Product_2021 as(
select t2.segment,
count(distinct(t1.product_code)) as unique_product_2021
from fact_sales_monthly t1 join dim_product t2
on t1.product_code = t2.product_code
where fiscal_year = 2021
group by t2.segment)
select s1.segment , unique_product_2020 ,unique_product_2021,
unique_product_2021 - unique_product_2020
as difference
from Unique_Segment_Product_2020 s1 join 
Unique_Segment_Product_2021 s2 on
s1.segment =s2.segment
order by difference desc;

request 5:

Get the products that have the highest and lowest manufacturing costs. The final output should contain these fields, product_code product manufacturing_cost

select f.product_code , product ,manufacturing_cost
from fact_manufacturing_cost f join dim_product p
on f.product_code = p.product_code 
where f.manufacturing_cost in (
select max(manufacturing_cost) from fact_manufacturing_cost
union
select min(manufacturing_cost) from fact_manufacturing_cost)
order by manufacturing_cost desc;

request 6:

Generate a report which contains the top 5 customers who received an average high pre_invoice_discount_pct for the fiscal year 2021 and in the Indian market. The final output contains these fields, customer_code customer average_discount_percentage

select c.customer_code ,c.customer,
round(avg(d.pre_invoice_discount_pct),2) as avg_discount_pct    
from dim_customer c join fact_pre_invoice_deductions d
on c.customer_code = d.customer_code 
where market = 'India'
group by  c.customer_code
order by avg_discount_pct desc limit 5;

request 7:

Get the complete report of the Gross sales amount forthe customer “Atliq Exclusive” for each month .This analysis helps to get an idea of low and high-performing months and take strategic decisions. The final report contains these columns:Month Year Gross sales Amount

select monthname(s.date) as month,
year(s.date) year,s.fiscal_year    ,
round(sum(s.sold_quantity*g.gross_price)/1000000,2) as gross_amount
from dim_customer c join fact_sales_monthly s
on c.customer_code = s.customer_code join fact_gross_price g 
on s.product_code = g.product_code
where c.customer = 'Atliq Exclusive'
group by year(s.date), monthname(s.date)
order by year(s.date) asc , month(s.date) asc;

request 8:

In which quarter of 2020, got the maximum total_sold_quantity?The final output contains these fields sorted by the total_sold_quantity,Quarter total_sold_quantity

with base_query as (
select *,date_add(date,interval  4 month) as formatted_date  from fact_sales_monthly)
select year(formatted_date) as fiscal_year,
quarter(formatted_date) as quarter_  ,
sum(sold_quantity) as total_sold_quantity
from base_query group by year(formatted_date) , quarter(formatted_date);

request 9:

Which channel helped to bring more gross sales in the fiscal year 2021 and the percentage of contribution? The final output contains these fields,channel gross_sales_mln percentage

with base_query as(
select s.fiscal_year,c.channel ,s.sold_quantity , g.gross_price ,
(s.sold_quantity*g.gross_price) as gross_amount
from fact_sales_monthly s join fact_gross_price g 
on s.product_code = g.product_code join dim_customer c on
s.customer_code = c.customer_code where s.fiscal_year = 2021)

select channel,total_gross_amount,
round((total_gross_amount/sum(total_gross_amount) over())*100,2) as pct from 
(select channel ,sum(gross_amount) as total_gross_amount from base_query
group by channel) t order by pct desc limit 1;

request 10:

Get the Top 3 products in each division that have a high total_sold_quantity in the fiscal_year 2021? The final output contains these fields,division product_code

with base_query as
(select s.fiscal_year,p.division,s.product_code,p.product,s.sold_quantity,
rank() over(partition by p.division order by sold_quantity desc ) as product_rank_division
from fact_sales_monthly s join dim_product p on s.product_code = p.product_code
where fiscal_year =2021 group by p.division ,p.product_code)
select * from base_query where product_rank_division < 4