SQL Series Part 5: Comparing day on day revenue during a Sale
A sale in any company is an exciting time to see the clock ticking and experience the sale unfold. In businesses which sell products in high volumes like personal care or IT accessories it definitely is exciting to see the sales progress and revenue flow in.
During this time, it isn’t unheard of for business teams to put in JIRA tickets and requests to build “sales trackers”. They are an exciting project for both business teams and the analysts involved especially when they know their projects are being consumed immediately by many people.
Let us try and build something along on those lines and see how it goes.
The query I have in mind would give information like end of the day sales and how much was the sale on the same day last month.
The data will be from the Sales Product Data which Ill pull from Kaggle. It is somewhat relevant to how sales would be tracked and saved in databases at one of the companies.
Now let's start with importing this data into MySQL and write up some code.
- Building day level sales
For starters let's just query the top 100 records to see what data we are working with.
The Order Date has a lot of NULLS so we will ignore them for the purposes of this project, but besides that it seems like we have pretty good amount of information to work with.
Next, we will filter the rows without an Order Date and use the column to get date level sales.
With that we have day on day Units sold and Revenue.
2. Building the Month-To-Date comparisons
What I am looking for here a table that has monthly sales and column wise the nth day of the sale and the record the number of units sold. Now why would I do something like that and not just have days of the month instead?
Well because despite sales happening every month the exact date might not be the same. The company can have a 5-day sale on 12th June and have a similar stock clearing sale starting from 14th July.
So, to do that we will employ the use of CTEs and ROW_NUMBER functions partitioned on Year Month and ordered by the day of the sale.
The output should look like this:
Here I am capturing up to first 6 days after the start of the sale. In some cases, you will notice the quantity dwindling down marking the end of the sale.
References:
Code is present here
Data: Sales Product Data
