SQL challenge by Codebasics

Domain: Consumer Goods

Function: Executive Management

I’m thrilled to share my recently completed Resume Building Project – SQL challenge by Codebasics with you all. 📊🔍 I was determined to push my boundaries and enhance my technical and Analytical skills. 💪

🔰 Project Overview
1️⃣ Project: Providing Insights to Management
2️⃣ Domain: Consumer Goods
3️⃣ Function: Executive Management

🔰 Project Introduction
Atliq Hardware, a major computer hardware company in India with a global presence, has encountered a challenge. They lack timely and intelligent insights for effective decision-making. To address this, they plan to hire junior data analysts. The director, Tony Sharma, seeks candidates with strong technical and interpersonal abilities. To assess these skills, he organized a SQL challenge.

➡ Tasks
The project involved tackling 10 ad hoc requests using SQL queries that catered to the top-tier management’s needs.

Check ‘ad-hoc-requests.pdf’ – there are 10 ad hoc requests for which the business needs insights.
You need to run a SQL query to answer these requests.
The target audience of this dashboard is top-level management – hence you need to create a presentation to show the insights.
Be creative with your presentation, audio/video presentation will have more weightage.

Codebasics SQL Challenge
Requests:

Provide the list of markets in which customer “Atliq Exclusive” operates its business in the APAC region.
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
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
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
Get the products that have the highest and lowest manufacturing costs. The final output should contain these fields, product_code product manufacturing_cost codebasics.io
Generate a report that 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
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 make strategic decisions. The final report contains these columns: Month Year Gross sales Amount
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
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
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

Insights Found

There are 8 countries in which the customer “Atliq Exclusive” operates its business in the APAC region.
There are 334 unique products in 2021 and 245 unique products in 2020. 36.33% growth in unique products from 2020 to 2021
The notebook segment contains a maximum number of unique product count is 129 and the Networking segment contains a maximum number of unique product count is 9.
Compared to the fiscal year 2020. In 2021 Accessories segment increased its maximum percentage is 38.20% and the Networking segment experienced slower growth i.e. 3.37%.
AQ HOME Allin1 Gen 2 (A6120110206) product’s highest manufacturing cost is 240.54 And AQ Master wired x1 Ms (A2118150101) product’s lowest manufacturing cost is 0.89.
Flipkart has the highest pre-invoice discount percentage. Amazon has the lowest pre-invoice discount percentage
March 2020 shows the lowest gross sales of $0.38M Fiscal year 2020. Highest gross sales in the month of November 2020.
The maximum total_sold_quantity Quarter of FY-2020 is Q1 ( 7005619 ). The minimum total_sold_quantity Quarter of FY-2020 is Q3 ( 2075087 ).
Retailer channel helped to bring more gross sales in 2021 and the percentage of contribution is 73.23%. The distributor channel has less gross sales in 2021 and the percentage of contribution is 11.30%.
In the N&S division, AQ Pen drives 2 IN 1. In the P&A division, Q Gamers MS. In the PC division, the AQ Digit product has the highest ranking.

Overall performance

Atliq Hardware has a strong partnership with Flipkart. Q2 and Q3, there was a decline in total sales, likely influenced by the outbreak of the Covid-19 pandemic.
Among the different segments, Notebooks, Accessories, and Peripherals stand out as major contributors to the company’s sales
One positive highlight was the substantial contribution from the retailer channel, which accounted for an impressive 73.22% of gross sales.

Concept Used

CTE’s: Common Table Expression
Sub Queries
Window Function
Multiple JOINS
Aggregate Functions

➡️ Tools used
MYSQL workbench, Power BI, and Canva

➡ Check it out!
You can dive into the details of my project on my GitHub repository

Massive Thanks to, Dhaval Patel and Hemanand Vadivel for this amazing Dataset. Also, their YouTube channel Codebasics helped me throughout this analytical Project!

If you have any feedback or suggestions to further improve my skills in data analytics, please feel free to share.

#DataAnalytics #SQLProject #PersonalGrowth #ContinuousLearning
#sqlqueries #sqlchallenge #sqltraining #powerbi ##resumebuilding #resumeproject #dataanalyst #consumerinsights

Resume Building Project – SQL challenge by Codebasics