- Apply SQL skills to wrangle customer sales transactions and derive useful and actionable insights from it.
We'll be working on below topics in this challenge :
- Connecting to SQL Server Database
- Do some code gyms while trying to answer business questions
- Summarizing our learnings (Very Important!)
How to start with live lab?
- Ping us on Slack for credentials
- Once credentials are received, use any SQL client ex. SQL Server Management Studio, DBeaver, TablePlus etc. to connect to our Open Database
- Practice running queries to solve the questions mentioned in this challenge
E-R Diagram (for your reference) :
Question 1 :
How many sales transactions took place in Jun'16?
- List all the transactions as a table
Question 2 :
Show year wise profit margin
- Profit Margin should be shown as percentage
- It should be rounded off to 2 decimal places
Question 3 :
What is the sales trend MoM for last 3 months?
- Show month wise sales
- Find out the last ever order date from the data
- Show sales trend for that month and 2 months prior to that
Question 4 :
What change will you do in the query to fetch the same data as above for last 6 months and last 12 months?
Question 5 :
Now, what query will you write if all 3 results above needs to be appended together stacked vertically?
Question 6 :
Let's try and make the total sales look more formatted. Continuing from Question 3 :
- Format Sales column to have :
- 2 decimal points
- Append a $ symbol in the beginning
- Add a million comma separator for sales
Question 7 :
Which are the top 3 sub-categories by sales?
Question 8 :
Calculate sales contribution% for each of the category by state
Done writing your SQL scripts? Upload your work here : here
Wish to discuss your solution and solve more interesting cases?
Join us on Slack today!