-- create a table for department
CREATE TABLE DEPARTMENT_TABLE(
	DEPARTMENT_ID INT,
	DEPARTMENT_NAME VARCHAR(50)
)

-- insert some data for department, with departmentīs ID and its name.
INSERT INTO DEPARTMENT_TABLE VALUES (1 ,'CANDY')
INSERT INTO DEPARTMENT_TABLE VALUES (24,'SPORT EQUIPMENT')
INSERT INTO DEPARTMENT_TABLE VALUES (32,'TOYS')
INSERT INTO DEPARTMENT_TABLE VALUES (34,'OFFICE SUPPLY')
INSERT INTO DEPARTMENT_TABLE VALUES (40,'MEAT')
INSERT INTO DEPARTMENT_TABLE VALUES (49,'DRINKS')
INSERT INTO DEPARTMENT_TABLE VALUES (51,'VEGETABLES')
INSERT INTO DEPARTMENT_TABLE VALUES (56,'CONDIMENTS')
INSERT INTO DEPARTMENT_TABLE VALUES (72,'SAUCES')
-- create table with store, department, amount and quantity
CREATE TABLE SALES_STORE_DEPARTMENT
(
	STORE			VARCHAR(10),
	DEPARTMENT_ID	INT,
	AMOUNT			NUMERIC(12,2),
	QUANTITY		INT
)

-- INSERT SOME DUMMY DATA
INSERT INTO SALES_STORE_DEPARTMENT VALUES ('1040',32,774.00,43)
INSERT INTO SALES_STORE_DEPARTMENT VALUES ('644',72,136.00,8)
INSERT INTO SALES_STORE_DEPARTMENT VALUES ('1040',34,504.00,28)
INSERT INTO SALES_STORE_DEPARTMENT VALUES ('17'	,72,1995.00,105)
INSERT INTO SALES_STORE_DEPARTMENT VALUES ('517',51,36.00,2)
INSERT INTO SALES_STORE_DEPARTMENT VALUES ('644',51,1003.00,59)
INSERT INTO SALES_STORE_DEPARTMENT VALUES ('17'	,51,1261.00,66)
INSERT INTO SALES_STORE_DEPARTMENT VALUES ('1040',40,1152.00,64)
INSERT INTO SALES_STORE_DEPARTMENT VALUES ('1040',1 ,6210.00,345)
INSERT INTO SALES_STORE_DEPARTMENT VALUES ('17'	,34,1026.00,54)
INSERT INTO SALES_STORE_DEPARTMENT VALUES ('256',34,1368.00,76)
INSERT INTO SALES_STORE_DEPARTMENT VALUES ('17'	,49,4688.00,261)
INSERT INTO SALES_STORE_DEPARTMENT VALUES ('539',56,1386.00,77)
INSERT INTO SALES_STORE_DEPARTMENT VALUES ('256',32,516.00,29)
INSERT INTO SALES_STORE_DEPARTMENT VALUES ('256',1 ,32472.60,1835)
INSERT INTO SALES_STORE_DEPARTMENT VALUES ('517',72,216.00,12)
INSERT INTO SALES_STORE_DEPARTMENT VALUES ('644',40,901.00,53)
INSERT INTO SALES_STORE_DEPARTMENT VALUES ('256',51,1170.00,65)
INSERT INTO SALES_STORE_DEPARTMENT VALUES ('517',56,1710.00,95)
INSERT INTO SALES_STORE_DEPARTMENT VALUES ('644',24,782.00,46)
INSERT INTO SALES_STORE_DEPARTMENT VALUES ('17'	,56,11704.00,616)
INSERT INTO SALES_STORE_DEPARTMENT VALUES ('539',24,720.00,40)
INSERT INTO SALES_STORE_DEPARTMENT VALUES ('256',49,16398.00,911)
INSERT INTO SALES_STORE_DEPARTMENT VALUES ('644',56,323.00,19)
INSERT INTO SALES_STORE_DEPARTMENT VALUES ('256',72,1098.00,61)
INSERT INTO SALES_STORE_DEPARTMENT VALUES ('1040',49,2030.00,114)
INSERT INTO SALES_STORE_DEPARTMENT VALUES ('539',40,2106.00,117)
INSERT INTO SALES_STORE_DEPARTMENT VALUES ('539',49,2869.00,160)
INSERT INTO SALES_STORE_DEPARTMENT VALUES ('1040',24,684.00,38)
INSERT INTO SALES_STORE_DEPARTMENT VALUES ('644',32,416.00,24)
INSERT INTO SALES_STORE_DEPARTMENT VALUES ('17'	,24,2907.00,153)
INSERT INTO SALES_STORE_DEPARTMENT VALUES ('517',24,252.00,14)
INSERT INTO SALES_STORE_DEPARTMENT VALUES ('1040',72,486.00,27)
INSERT INTO SALES_STORE_DEPARTMENT VALUES ('1040',56,2070.00,128)
INSERT INTO SALES_STORE_DEPARTMENT VALUES ('539',32,126.00,7)
INSERT INTO SALES_STORE_DEPARTMENT VALUES ('17'	,32,1330.00,70)
INSERT INTO SALES_STORE_DEPARTMENT VALUES ('539',34,288.00,16)
INSERT INTO SALES_STORE_DEPARTMENT VALUES ('644',1 ,5457.00,330)
INSERT INTO SALES_STORE_DEPARTMENT VALUES ('517',40,1782.00,99)
INSERT INTO SALES_STORE_DEPARTMENT VALUES ('517',1 ,4752.00,585)
INSERT INTO SALES_STORE_DEPARTMENT VALUES ('1040',51,198.00,11)
INSERT INTO SALES_STORE_DEPARTMENT VALUES ('644',49,2601.00,153)
INSERT INTO SALES_STORE_DEPARTMENT VALUES ('17'	,40,4218.00,222)
INSERT INTO SALES_STORE_DEPARTMENT VALUES ('256',24,828.00,46)
INSERT INTO SALES_STORE_DEPARTMENT VALUES ('517',34,162.00,9)
INSERT INTO SALES_STORE_DEPARTMENT VALUES ('517',32,558.00,31)
INSERT INTO SALES_STORE_DEPARTMENT VALUES ('644',34,340.00,20)
INSERT INTO SALES_STORE_DEPARTMENT VALUES ('256',40,12096.00,672)
INSERT INTO SALES_STORE_DEPARTMENT VALUES ('17'	,1 ,14632.59,884)
INSERT INTO SALES_STORE_DEPARTMENT VALUES ('256',56,12312.00,684)
INSERT INTO SALES_STORE_DEPARTMENT VALUES ('539',72,288.00,16)
INSERT INTO SALES_STORE_DEPARTMENT VALUES ('539',1,14364.00,845)
INSERT INTO SALES_STORE_DEPARTMENT VALUES ('517',49,1573.00,88)
INSERT INTO SALES_STORE_DEPARTMENT VALUES ('539',51,144.00,8)

-- verify the your data

SELECT S.STORE,  D.DEPARTMENT_NAME, S.AMOUNT, S.QUANTITY
  FROM SALES_STORE_DEPARTMENT  S JOIN DEPARTMENT_TABLE D ON D.DEPARTMENT_ID = S.DEPARTMENT_ID 
ORDER BY S.STORE, D.DEPARTMENT_NAME ASC


-- TOP 5 DEPARTMENTS
SELECT top 5 DEPARTMENT_NAME, SUM(AMOUNT) AMOUNT, SUM(QUANTITY) QUANTITY
  FROM SALES_STORE_DEPARTMENT S JOIN DEPARTMENT_TABLE D ON D.DEPARTMENT_ID = S.DEPARTMENT_ID
GROUP BY DEPARTMENT_NAME
ORDER BY 2 DESC

-- TOP STORES
SELECT STORE, SUM(AMOUNT) AMOUNT, SUM(QUANTITY) QUANTITY
  FROM SALES_STORE_DEPARTMENT
GROUP BY STORE
ORDER BY 2 DESC


-- now you execute your query to rank the TOP 5 departartments by store
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