AtliQ Hardware Consumer Goods Insights - Codebasics Challenge
Project Overview: Leveraged MySQL for sophisticated data querying and analytics to address management’s ad hoc requests in the consumer goods sector.
Skills Gained: Enhanced SQL proficiency, specializing in:
- Subqueries: For filtering and aggregating data.
- CTEs (Common Table Expressions): For structuring complex queries more clearly.
- JOINs: To combine and analyze data from multiple tables.
- Window Functions: Utilizing RANK() to perform advanced data analysis and ranking.
Insights Delivered:
- Product Trends: Identified and analyzed trends in product sales.
- Top Customers: Determined top customers based on sales volume.
- Discount Strategies: Assessed the impact of various discount strategies on sales.
- Sales Metrics: Calculated key sales metrics, including average sales and total quantities sold.
Impact and Learnings: Showcased actionable insights and demonstrated the effectiveness of SQL in addressing complex analytical tasks.
Key Takeaways: Mastery of MySQL’s advanced features is crucial for in-depth data analysis, highlighting the importance of continual learning and adaptation in the data field.
- 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";
- What is the percentage of unique product increase in 2021 vs. 2020?
WITH year_in_20_21 as (
SELECT
COUNT(DISTINCT CASE WHEN fiscal_year = 2020 THEN product_code END) as unique_products_2020,
COUNT(DISTINCT CASE WHEN fiscal_year = 2021 THEN product_code END) as unique_products_2021
FROM
fact_sales_monthly
)
SELECT
unique_products_2020,
unique_products_2021,
ROUND(ABS(unique_products_2020-unique_products_2021)/(unique_products_2020+unique_products_2021) * 100,2) as percentage_chg
FROM
year_in_20_21;
3. Provide a report with all the unique product counts for each segment and sort them in descending order of product counts.
SELECT
segment,
COUNT(DISTINCT product_code) AS product_count
FROM
dim_product
GROUP BY
segment
ORDER BY
product_count DESC;
- Follow-up: Which segment had the most increase in unique products in 2021 vs 2020?
WITH year_in_20_21 as (
SELECT
COUNT(DISTINCT CASE WHEN fiscal_year = 2020 THEN product_code END) as unique_products_2020,
COUNT(DISTINCT CASE WHEN fiscal_year = 2021 THEN product_code END) as unique_products_2021
FROM
fact_sales_monthly
)
SELECT
unique_products_2020,
unique_products_2021,
ROUND(ABS(unique_products_2020-unique_products_2021)/(unique_products_2020+unique_products_2021) * 100,2) as percentage_chg
FROM
year_in_20_21;
5. Get the products that have the highest and lowest manufacturing costs.
WITH HighestCost AS (
SELECT
dp.product_code,
dp.product,
fmc.manufacturing_cost
FROM
dim_product dp
JOIN
fact_manufacturing_cost fmc
ON
dp.product_code = fmc.product_code
ORDER BY fmc.manufacturing_cost DESC
LIMIT 1
),
-- Get the product with the lowest manufacturing cost
LowestCost AS (
SELECT
dp.product_code,
dp.product,
fmc.manufacturing_cost
FROM
dim_product dp
JOIN
fact_manufacturing_cost fmc
ON
dp.product_code = fmc.product_code
ORDER BY fmc.manufacturing_cost ASC
LIMIT 1
)
-- Combine the results
SELECT * FROM HighestCost
UNION ALL
SELECT * FROM LowestCost;
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.
SELECT
dc.customer_code,
dc.customer,
ROUND(AVG(fpid.pre_invoice_discount_pct),3) AS average_discount_percentage
FROM
fact_pre_invoice_deductions fpid
JOIN
dim_customer dc
ON
dc.customer_code = fpid.customer_code
WHERE
fiscal_year = 2021 AND market = 'India'
GROUP BY
dc.customer_code,
dc.customer
ORDER BY
average_discount_percentage DESC
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.
SELECT
MONTHNAME(fsm.date) AS "Month",
fsm.fiscal_year As "Year",
ROUND(SUM(fgp.gross_price * fsm.sold_quantity),2) AS "Gross sales Amount"
FROM
fact_sales_monthly fsm
JOIN
fact_gross_price fgp
ON
fsm.product_code = fgp.product_code AND fsm.fiscal_year = fgp.fiscal_year
JOIN
dim_customer dc
ON
dc.customer_code = fsm.customer_code
WHERE
dc.customer = "Atliq Exclusive"
GROUP BY
MONTHNAME(fsm.date), fsm.fiscal_year
ORDER BY
ROUND(SUM(fgp.gross_price * fsm.sold_quantity),2) DESC;
8. In which quarter of 2020, got the maximum total_sold_quantity?
SELECT
quarter(date) AS "Quarter",
SUM(sold_quantity) AS total_sold_quantity
FROM
fact_sales_monthly
WHERE
fiscal_year = 2020
GROUP BY
quarter(date)
ORDER BY
total_sold_quantity DESC
LIMIT 1 -- THIS LIMIT IS OPTIONAL IF YOU REMOVE IT YOU WILL ABLE TO SEE total_sold_quantity for all the 4 Quarter
;
9. Which channel helped to bring more gross sales in the fiscal year 2021 and the percentage of contribution?
SELECT
dc.channel AS "channel",
ROUND((SUM(fgp.gross_price * fsm.sold_quantity)/1e6),2) AS gross_sales_mln,
CONCAT(ROUND(100.0 * SUM(fgp.gross_price * fsm.sold_quantity) /
(SELECT SUM(fgp.gross_price * fsm.sold_quantity)
FROM fact_sales_monthly fsm
JOIN fact_gross_price fgp
ON fsm.product_code = fgp.product_code
AND fsm.fiscal_year = fgp.fiscal_year
WHERE fsm.fiscal_year = 2021), 2),'%') AS "percentage"
FROM
fact_sales_monthly fsm
JOIN
fact_gross_price fgp
ON
fsm.product_code = fgp.product_code
AND fsm.fiscal_year = fgp.fiscal_year
JOIN
dim_customer dc
ON
dc.customer_code = fsm.customer_code
WHERE
fsm.fiscal_year = 2021
GROUP BY
dc.channel
ORDER BY
gross_sales_mln DESC
LIMIT 1 -- OPTIONAL
;
10. Get the Top 3 products in each division that have a high total_sold_quantity in the fiscal_year 2021?
WITH RankedProducts AS (
SELECT
dp.division AS "Division",
dp.product_code AS "Product_Code",
dp.product AS "Product",
SUM(fsm.sold_quantity) AS "total_sold_quantity",
RANK() OVER (PARTITION BY dp.division ORDER BY SUM(fsm.sold_quantity) DESC) AS "rank_order"
FROM
fact_sales_monthly fsm
JOIN
dim_product dp ON fsm.product_code = dp.product_code
WHERE
fsm.fiscal_year = 2021
GROUP BY
dp.division, dp.product_code, dp.product
)
SELECT
Division,
Product_Code,
Product,
total_sold_quantity,
rank_order
FROM
RankedProducts
WHERE
rank_order <= 3
ORDER BY
Division, rank_order;