Let´s say that you are the CEO of a big chain of supermarkets, you know what are your best departments and your most profitable stores, but you would like to know what are your top 5 departments by store.
Sales by Stores and Departments
TOP 5 Departments
It´s very simple to know what are the top 5 departments, since you just need to use the function SUM() for the amount and quantity and group the other columns. Also, you will add the TOP 5 on the select command. After that, you need to sort decreasingly and that´s it!
TOP 5 Departments by Store
This one is simple too, you need to use the funcion rank(). This functions allows you to create rankings based in items of the query result.
To use this function you just need to provide
1 ) in which column will be the reference (PARTITION BY), in this case, since you want to know the ranking by store, you will put store.
2 ) The column to sort increasingly or decreasingly (ORDER BY )
So the comand will be:
SELECT *
FROM (
SELECT S.STORE, D.DEPARTMENT_NAME, S.AMOUNT, S.QUANTITY, RANK() OVER (PARTITION BY S.STORE ORDER BY S.QUANTITY DESC) RANKING
FROM SALES_STORE_DEPARTMENT S JOIN DEPARTMENT_TABLE D ON D.DEPARTMENT_ID = S.DEPARTMENT_ID ) Q
WHERE Q.RANKING <= 5
Note that a subquery was required in order to limit the ranking by 5
The script used for this solution is in the link below.
I hope it helps you 🙂