SQL queries_script.sql

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

SELECT 
    market, customer, region
FROM
    dim_customer
WHERE
    customer = 'Atliq Exclusive'
        AND region = 'APAC';
 
/* 2. 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_1 as (
select count(distinct p.product_code) as unique_products_2020
from dim_product p
join fact_sales_monthly f on p.product_code=f.product_code
where f.fiscal_year=2020),

unique_2 as(
select count(distinct p.product_code) as unique_products_2021
from dim_product p
join fact_sales_monthly f on p.product_code=f.product_code
where f.fiscal_year=2021)

select unique_products_2020
		,unique_products_2021
		,ROUND(((unique_products_2021-unique_products_2020)/unique_products_2020)*100,2) as Percentage_change
from unique_1 
join unique_2 on 1=1;

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

SELECT DISTINCT
    segment AS segment,
    COUNT(DISTINCT product_code) AS Product_count
FROM
    dim_product
GROUP BY segment
ORDER BY Product_count DESC;
 
/*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 product21 as(
select p.segment
	, count(distinct p.product_code) as product_count_2021
    from dim_product p
join fact_sales_monthly f on p.product_code=f.product_code
where fiscal_year=2021
group by segment
),
product20 as(
select p.segment
	, count(distinct p.product_code) as product_count_2020
    from dim_product p
join fact_sales_monthly f on p.product_code=f.product_code
where fiscal_year=2020
group by segment
)
select product21.segment
, product_count_2020
, product_count_2021 
,(product_count_2021 - product_count_2020) as difference
from product21 
join product20 on product21.Segment=product20.segment
order by difference desc;

/* 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 
    p.product_code,
    p.product,
    ROUND(m.manufacturing_cost, 2) AS manufacturing_cost
FROM
    dim_product p
        JOIN
    fact_manufacturing_cost m ON p.product_code = m.product_code
WHERE
    m.manufacturing_cost = (SELECT 
            MIN(manufacturing_cost)
        FROM
            fact_manufacturing_cost)
        OR m.manufacturing_cost = (SELECT 
            MAX(manufacturing_cost)
        FROM
            fact_manufacturing_cost)
ORDER BY manufacturing_cost DESC;

/* 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 rank() over(order by avg(f.pre_invoice_discount_pct)*100 desc) as rank_
		,c.customer_code
		,c.customer
        ,round(avg(f.pre_invoice_discount_pct)*100,2) as average_discount_percentage
        
from dim_customer c 
join fact_pre_invoice_deductions f on c.customer_code=f.customer_code 
where f.fiscal_year=2021 
and c.market='India'
group by c.customer_code,customer
limit 5;

/* 7. Get the complete report of the Gross sales amount for the 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 
    MONTH(f.date) AS month,
    YEAR(f.date) AS year,
    ROUND(SUM(g.gross_price * f.sold_quantity) / 1000000,
            2) AS Gross_sales_Amount_mln
FROM
    fact_sales_monthly f
        JOIN
    fact_gross_price g ON f.product_code = g.product_code
        JOIN
    dim_customer c ON f.customer_code = c.customer_code
WHERE
    c.customer = 'Atliq Exclusive '
GROUP BY MONTH(f.date) , YEAR(f.date)
ORDER BY year , month;

/* 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 */

SELECT 
    CASE
        WHEN MONTH(date) IN (9 , 10, 11) THEN 'Q1'
        WHEN MONTH(date) IN (12 , 1, 2) THEN 'Q2'
        WHEN MONTH(date) IN (3 , 4, 5) THEN 'Q3'
        WHEN MONTH(date) IN (6 , 7, 8) THEN 'Q4'
    END AS QUARTER,
    SUM(sold_quantity) AS total_sold_quantity
FROM
    fact_sales_monthly
WHERE
    fiscal_year = '2020'
GROUP BY QUARTER
ORDER BY total_sold_quantity DESC;


/* 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 cte1 as 
(select c.channel
		,Round(SUM(f.gross_price * m.sold_quantity)/1000000,2) as  gross_sales_mln from
fact_sales_monthly m
join dim_customer c
on m.customer_code=c.customer_code
join fact_gross_price f
on m.product_code=f.product_code
where m.fiscal_year=2021
group by c.channel
order by gross_sales_mln desc),

cte2 as
(select sum( gross_sales_mln) as total_pct from cte1 )

select cte1.*
		, round((cte1.gross_sales_mln/cte2.total_pct)*100,2) as percentage
from cte1 join cte2;

/* 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
product
total_sold_quantity
rank_order*/

with divi as (
select p.division as division
,p.product_code as product_code
        ,p.product as product
        ,sum(s.sold_quantity) as total_sold_quantity from dim_product p
join fact_sales_monthly s on p.product_code = s.product_code   
where s.fiscal_year = 2021
group by p.division,p.product_code,p.product
order by total_sold_quantity desc),

rnk as ( select *, 
dense_rank() over (partition by division order by total_sold_quantity desc) as rank_order
from divi)

select * from rnk
where rank_order<=3;