I analyzed a data set from an online store. Although the data is not current, my intention was to put into practice and demonstrate the use of window functions and JOINs in the preparation of a sales report.
Some of the ways in which these functions were used in the report included: the combination of records between two or more tables to obtain different information from each one of them, evaluating the performance of an aspect or set of elements through rankings (sales and number of monthly orders, number of products sold, sales by province, customers) and percentage over totals.
To see in detail the SQL queries I used, you can access the document with the name "OnlineShoppingQuerys.sql" in this repository.
Average sale per year.
The highest average sale was reached in the first year, but then, the following 3 years, the average sale has been below that of the first year by around 15%.
Annual revenue and year-over-year difference.
After the first year, the growth percentage was negative, but by the fourth year it started to pick up and there was a recovery.
Annual profit and year-over-year difference.
In terms of profits, the year-over-year difference has been mostly negative.
Annual sales by product subcategories.
There appear to be no significant changes in the subcategories' rankings over the years.
Annual sales by province.
Of the 13 provinces, 5 - Ontario, British Columbia, Alberta, New Brunswick, and Prince Edward Island - saw their highest annual sales in the first year but thereafter failed to match or exceed that first year.
Monthly sales for each year.
First, the sales of the first year have been ordered according to the month with the highest sales, and then we can see their evolution over the years through the annual rankings.
There does not seem to be a relevant pattern for monthly sales. Only December and October have relative stability at the top over the 4 years.
Monthly sales and orders. Is there a correlation?
I compared the monthly orders of each year, sorted by their annual ranking, with the monthly sales of each year and their ranking, looking to see if there was any correlation between sales and the number of orders. Comparing both rankings shows that there is not a relationship, higher number of orders does not mean higher sales.
Number of customers per year who made at least one order.
The first year the online shop reached the highest number of customers with at least one order, but has not equaled or exceeded that number since.
Number of customers per year by customer segment who placed at least one order.
The number of customers in the Consumer segment has declined since the first year. The number of customers in the Corporate segment has always been higher than the rest.
Top 10 customers per year with the highest sales amount.
Bottom 10 customers per year in terms of sales.
Quantity sold of each product per year.
Bookcases; Scissors, rulers and trimmers; and Copiers and Fax, were the only sub-categories below 1000 units sold. Bookcases 1 year, scissors, rulers and trimmers 2 years, and copiers and fax all 4 years.
The number of orders for each type of shipping and the percentage of each type over the total.
Regular air shipment is by far the most common type of shipment.
Number of shipping per month, sorted from the month with the highest number of shipments.
There is no significant variation in the number of shipments each month.
