As part of the February month Codebasics resume challenge, I have written SQL queries for the given ad-hoc-requests with the detailed presentation.

Link to the Challenge

Provide insights to the Management in Cosumer Goods domain

Atliq Hardwares (imaginary company) is one of the leading computer hardware producers in India and well expanded in other countries too.

However, the management noticed that they do not get enough insights to make quick and smart data-informed decisions. They want to expand their data analytics team by adding several junior data analysts. Tony Sharma, their data analytics director wanted to hire someone who is good at both tech and soft skills. Hence, he decided to conduct a SQL challenge which will help him understand both the skills.

Imagine yourself as the applicant for this role and perform the following task

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

• Dataset required to provide
• Metadata
• Hints (try not to use the hints to develop your skills quicker)
• Sample questions and Presentation

• SQL Workbench (For SQL)
• Microsoft Excel (For data visualization)
• Microsoft Power Point (To create an interactive presentation)

• Leant how to work and deal with the large amount of the data.
• Learnt how to write SQL queries for getting some required meaningful output from the data table.
• Got to know about some Advanced SQL concepts like Common Table Expression(CTEs), Self Joins, Pivoting data with case and many more.

1. List of Markets in which Customer "Atliq Exclusive" operates its business in APAC region are India, Indonesia, Japan, Philiphines, South Korea, Australia, New Zealand and Bangladesh.
2. Total Unique Product Count in 2020 is 245.Total Unique Product Count in 2021 is 334. Increase in % of Product Count is 36.33%.
3. In total we have 6 segments and Accessories and Peripherals hold the maximum unique product count.
4. Accessories segment has the most increase in unique product in 2021 vs 2020.
5. ‘AQ Master Wired x1 Ms’ has the highest manufacturing cost while ‘AQ HOME Allin 1 Gen 2’ has the lowest manufacturing cost.
6. Top 5 customers who received an average high ‘Pre Invoice Discount’ for the fiscal year 2021 and in the Indian market are: Flipkart, Viveks, Ezone, Croma, Amazon.
7. Nov 2020 has the highest while March 2020 has the lowest Gross sales for the customer ‘Atliq Exclusive’.
8. 1st Quarter of 2020 i.e. {Sept, Oct, Nov} has the maximum Total Sold Quantity.
9. Channel ‘Retailer’ helped to bring more Gross Sales in the fiscal year 2021 with the total of 73% contribution.
10. Top 3 products in each division (N&S, P&A, PC) that have a high Total Sold Quantity in the Fiscal Year 2021 are shown in the chart.