SELECT DISTINCT year(order_date) AS Distinct_Year,
COUNT(order_date) AS Number_Of_Transactions
FROM sales_order
GROUP BY Distinct_Year;1. Exploring Business Sales Period
Before we dive into exploring sales for the selected period, it is important to note that the sales database contains five table of fictitious records which will be used to demonstrate how exploration of a business historical sales records can be performed using MySQL DBMS. The analysis will involve looking into various key performance of selected entities based on several factors such as sales, cost, profit, etc.
The distinct Sales period.
| Distinct Year | Number Of Transactions |
|---|---|
| 2018 | 1,836 |
| 2019 | 3,030 |
| 2020 | 3,125 |
The selected sales record started from the year 2018 and ended in 2020 also there were more orders in 2020 than previous years.
Given the different number of product quantity order in each year, do all the sales years contain the full twelve 12 months.
SELECT tbl.order_year,
COUNT(tbl.order_month) AS number_of_distinct_month
FROM(SELECT order_date,
year(order_date) AS order_year,
month(order_date) AS order_month
FROM sales_order
GROUP BY order_year, order_month
LIMIT 50) AS tbl
GROUP BY tbl.order_year;| Order Year | Number Of Distinct Month |
|---|---|
| 2018 | 8 |
| 2019 | 12 |
| 2020 | 12 |
Apart from the year 2018 which only sales order records for 8 different months all other year have a complete 12 months.
Let’s get the Summary of sales across previous sales years.
WITH sales_year AS (
SELECT year(order_date) AS order_year,
monthname(order_date) AS order_month,
(order_quantity * unit_price) AS sales
FROM sales_order
)
SELECT order_year,
MIN(sales) AS Minimum,
round(AVG(sales), 2) AS Average,
round(MAX(sales), 2) AS Maximum,
round(SUM(sales), 2) AS Total
FROM sales_year
GROUP BY order_year;| Order Year | Minimum | Average | Maximum | Total |
|---|---|---|---|---|
| 2018 | 167.5 | 10,508.4 | 52,313.6 | 19,293,494.2 |
| 2019 | 167.5 | 10,407.5 | 51,831.2 | 31,534,789.5 |
| 2020 | 167.5 | 10,196.6 | 52,206.4 | 31,864,442.9 |
More sales was made in 2020 than the previous 2 years given the total amount, but on an average the year 2018 had the highest amount followed by the preceding years.
How much profit was made in previous sales years?
CREATE TEMPORARY TABLE rev_date_tbl
SELECT order_date,
year(order_date) AS order_year,
monthname(order_date) AS order_month,
order_quantity,
unit_price,
(order_quantity * unit_price) AS sales,
((order_quantity * unit_cost) + ((order_quantity * unit_price) * discount_applied)) AS cost,
(order_quantity * unit_price) - ((order_quantity * unit_cost) + ((order_quantity * unit_price) * discount_applied)) AS profit
FROM sales_order;SELECT order_year,
round(AVG(profit), 2) AS Average_Profit,
round(SUM(profit), 2) AS Total_Proft
FROM rev_date_tbl
GROUP BY order_year;| Order Year | Average Profit | Total Proft |
|---|---|---|
| 2018 | 2,694.58 | 4,947,254.21 |
| 2019 | 2,699.86 | 8,180,576.37 |
| 2020 | 2,623.20 | 8,197,496.32 |
The total amount of profit made in 2020 was the highest, this was closely followed by the year 2019 while for the average amount of profit the year 2019 had the highest profit followed by 2018 and 2020.
Given the amount made in each of the last sales years, how much was made in each month for all recorded sales years.
SELECT order_month,
round(AVG(profit), 2) AS Average_Profit,
round(SUM(profit), 2) AS Total_Profit
FROM rev_date_tbl
GROUP BY order_month;| Order Month | Average Profit | Total Profit |
|---|---|---|
| January | 2,844.14 | 1,618,314.79 |
| February | 2,661.81 | 1,336,229.61 |
| March | 2,601.91 | 1,168,257.75 |
| April | 2,557.10 | 1,362,936.06 |
| May | 2,737.33 | 1,505,532.43 |
| June | 2,593.12 | 1,918,911.40 |
| July | 2,641.59 | 2,100,067.46 |
| August | 2,693.08 | 2,130,230.10 |
| September | 2,654.40 | 1,977,530.97 |
| October | 2,527.68 | 1,883,123.41 |
| November | 2,826.95 | 2,207,849.37 |
| December | 2,675.53 | 2,116,343.54 |
The month of November had the highest amount of profit for all three years, while January had the highest average profit. Also March in general had the lowest profit earned in a month.
Now that We know that 2020 has the highest amount of the three years present, let’s get the profit for each month in that year.
SELECT order_month,
round(AVG(profit), 2) AS Average_Profit,
round(SUM(profit), 2) AS Total_Profit
FROM rev_date_tbl
WHERE order_year = 2020
GROUP BY order_month
ORDER BY Total_Profit DESC;| 2020: Order Month | Average Profit | Total Profit |
|---|---|---|
| January | 3,048.54 | 841,396.27 |
| February | 2,726.09 | 736,043.11 |
| March | 2,512.94 | 593,052.71 |
| April | 2,497.04 | 654,225.51 |
| May | 2,879.75 | 757,374.55 |
| June | 2,621.77 | 642,332.91 |
| July | 2,893.97 | 813,204.32 |
| August | 2,568.25 | 644,630.10 |
| September | 2,387.78 | 606,495.82 |
| October | 2,215.23 | 624,694.52 |
| November | 2,468.77 | 646,817.43 |
| December | 2,622.34 | 637,229.08 |
For the year 2020 the highest amount of profit was made in January while the month with the lowest amount of profit is march, For the average profit made across 2020, The highest amount was also made in January while October had the lowest average amount.
Insight
The number of transactions conducted in 2020 and 2019 are almost evenly distributed with the year 2020 having the largest number of transactions, sales and most importantly profit. November is the month with the most profit summed up for all the selected sales period but in 2020 the most profit was made during the first month of the year.