HomeChallengesExploring Customer sales transactions in SQL
learnbook poster
Exploring Customer sales transactions in SQLLast updated at Mon Feb 14 2022Skills
data-wrangling
Tools
sql

Learning Outcomes


  • Apply SQL skills to wrangle customer sales transactions and derive useful and actionable insights from it.

Tasks


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) :

erd.png

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!


Created with 💙 by
author avatar
Amit Choudhary
Data Practitioner,Mentorskool
mentorskool logo
Mentee Today, Mentor Tomorrow
No 206, A Block, Sonesta Silver Oak,Varthur, Bangalore 560066,Karnataka
Copyright - 2022 © Mentorskool - All rights reserved.