Output top 3 most profitable products every quarter

Question

I'm trying to output a top 3 products per quarter, that should be a total of 12 rows, since 3 top products per quarter. Closest output is the one provided below i have no idea how to like partition it every quarter

SELECT * FROM (SELECT QUARTER, PRODUCT_NAME, SUM(QUANTITY) "QTY_SOLD", SALES, SUM(PROFIT) "PROFIT_GENERATED" FROM DELIVERIES_FACT 
    WHERE EXTRACT(YEAR from SHIP_DATE) = 2015 GROUP BY  QUARTER, PRODUCT_NAME, SALES ORDER BY "PROFIT_GENERATED" DESC)
    WHERE rownum <= 3

getting an output of

score 0 · Answer 1 · answered May 17 '18 at 14:42

I've written this SQL extracting the calendar quarter from SHIP_DATE; you can adjust as needed.

Similarly, RANK(), ROW_NUMBER(), and DENSE_RANK() all are different; you may wish to experiment with each analytical function to see which best fits your data and handles ties the way you want them to.

SELECT * 
  FROM (SELECT RANK() OVER (PARTITION BY SHIP_QUARTER 
                            ORDER BY PROFIT_GENERATED desc) AS PROFIT_RANK_BY_Q, 
               ORIG.* 
          FROM
               (SELECT EXTRACT(QUARTER from SHIP_DATE) AS SHIP_QUARTER, 
                       PRODUCT_NAME, 
                       SUM(QUANTITY) "QTY_SOLD", SALES, SUM(PROFIT) "PROFIT_GENERATED" 
                  FROM DELIVERIES_FACT 
                 WHERE EXTRACT(YEAR from SHIP_DATE) = 2015 
                 GROUP BY EXTRACT(QUARTER from SHIP_DATE), PRODUCT_NAME, SALES 
               )
       )
 WHERE PROFIT_RANK_BY_Q <= 3
 order by SHIP_QUARTER, PROFIT_RANK_BY_Q

Output top 3 most profitable products every quarter

1 Answers1