Need help for Top 10 Products (by Amounts)

I have create new table and insert sample data into the table. I want to get the top 10 products rank by amount weekly. I tried SQL Query but give the wrong data. I have shared the table, sample data, and SQL Query below.

I have a table:

CREATE TABLE product_table (
product_name          VARCHAR2(20),
amount                NUMBER,
datetime DATE
)

Sample Data:

INSERT INTO product_table (product_name, amount, datetime)
SELECT 'P1', 10000, TRUNC(SYSDATE, 'IW') FROM DUAL UNION ALL
SELECT 'P2', 15000, TRUNC(SYSDATE, 'IW') FROM DUAL UNION ALL
SELECT 'P3', 18000, TRUNC(SYSDATE, 'IW') FROM DUAL UNION ALL
SELECT 'P4', 11000, TRUNC(SYSDATE, 'IW') FROM DUAL UNION ALL
SELECT 'P5', 13000, TRUNC(SYSDATE, 'IW') FROM DUAL UNION ALL
SELECT 'P6', 16000, TRUNC(SYSDATE, 'IW') FROM DUAL UNION ALL
SELECT 'P7', 19000, TRUNC(SYSDATE, 'IW') FROM DUAL UNION ALL
SELECT 'P8', 20000, TRUNC(SYSDATE, 'IW') FROM DUAL UNION ALL
SELECT 'P9', 24000, TRUNC(SYSDATE, 'IW') FROM DUAL UNION ALL
SELECT 'P10',26000, TRUNC(SYSDATE, 'IW') FROM DUAL UNION ALL
SELECT 'P11',34000, TRUNC(SYSDATE, 'IW') FROM DUAL UNION ALL
SELECT 'P1', 8000, TRUNC(SYSDATE, 'IW') - INTERVAL '7' DAY FROM DUAL UNION ALL
SELECT 'P2', 17000, TRUNC(SYSDATE, 'IW') - INTERVAL '7' DAY FROM DUAL UNION ALL
SELECT 'P3', 22000, TRUNC(SYSDATE, 'IW') - INTERVAL '7' DAY FROM DUAL UNION ALL
SELECT 'P4', 23000, TRUNC(SYSDATE, 'IW') - INTERVAL '7' DAY FROM DUAL UNION ALL
SELECT 'P5', 26000, TRUNC(SYSDATE, 'IW') - INTERVAL '7' DAY FROM DUAL UNION ALL
SELECT 'P6', 34000, TRUNC(SYSDATE, 'IW') - INTERVAL '7' DAY FROM DUAL UNION ALL
SELECT 'P7', 31000, TRUNC(SYSDATE, 'IW') - INTERVAL '7' DAY FROM DUAL UNION ALL
SELECT 'P8', 42000, TRUNC(SYSDATE, 'IW') - INTERVAL '7' DAY FROM DUAL UNION ALL
SELECT 'P9', 54000, TRUNC(SYSDATE, 'IW') - INTERVAL '7' DAY FROM DUAL UNION ALL
SELECT 'P10', 14000,  TRUNC(SYSDATE, 'IW') - INTERVAL '7' DAY FROM DUAL UNION ALL
SELECT 'P11', 19000, TRUNC(SYSDATE, 'IW') - INTERVAL '7' DAY FROM DUAL UNION ALL
SELECT 'P1', 8000, ADD_MONTHS(TRUNC(SYSDATE, 'IW'), -1) FROM DUAL UNION ALL
SELECT 'P2', 16000, ADD_MONTHS(TRUNC(SYSDATE, 'IW'), -1) FROM DUAL UNION ALL
SELECT 'P3', 21000, ADD_MONTHS(TRUNC(SYSDATE, 'IW'), -1) FROM DUAL UNION ALL
SELECT 'P4', 22000, ADD_MONTHS(TRUNC(SYSDATE, 'IW'), -1) FROM DUAL UNION ALL
SELECT 'P5', 25000, ADD_MONTHS(TRUNC(SYSDATE, 'IW'), -1) FROM DUAL UNION ALL
SELECT 'P6', 33000, ADD_MONTHS(TRUNC(SYSDATE, 'IW'), -1) FROM DUAL UNION ALL
SELECT 'P7', 32000, ADD_MONTHS(TRUNC(SYSDATE, 'IW'), -1) FROM DUAL UNION ALL
SELECT 'P8', 41000, ADD_MONTHS(TRUNC(SYSDATE, 'IW'), -1) FROM DUAL UNION ALL
SELECT 'P9', 53000, ADD_MONTHS(TRUNC(SYSDATE, 'IW'), -1) FROM DUAL UNION ALL
SELECT 'P10', 24000,  ADD_MONTHS(TRUNC(SYSDATE, 'IW'), -1) FROM DUAL UNION ALL
SELECT 'P11', 29000, ADD_MONTHS(TRUNC(SYSDATE, 'IW'), -1) FROM DUAL UNION ALL
SELECT 'P1', 7000, ADD_MONTHS(TRUNC(SYSDATE, 'IW'), -2) FROM DUAL UNION ALL
SELECT 'P2', 15000, ADD_MONTHS(TRUNC(SYSDATE, 'IW'), -2) FROM DUAL UNION ALL
SELECT 'P3', 20000, ADD_MONTHS(TRUNC(SYSDATE, 'IW'), -2) FROM DUAL UNION ALL
SELECT 'P4', 21000, ADD_MONTHS(TRUNC(SYSDATE, 'IW'), -2) FROM DUAL UNION ALL
SELECT 'P5', 24000, ADD_MONTHS(TRUNC(SYSDATE, 'IW'), -2) FROM DUAL UNION ALL
SELECT 'P6', 32000, ADD_MONTHS(TRUNC(SYSDATE, 'IW'), -2) FROM DUAL UNION ALL
SELECT 'P7', 31000, ADD_MONTHS(TRUNC(SYSDATE, 'IW'), -2) FROM DUAL UNION ALL
SELECT 'P8', 40000, ADD_MONTHS(TRUNC(SYSDATE, 'IW'), -2) FROM DUAL UNION ALL
SELECT 'P9', 52000, ADD_MONTHS(TRUNC(SYSDATE, 'IW'), -2) FROM DUAL UNION ALL
SELECT 'P10', 34000,  ADD_MONTHS(TRUNC(SYSDATE, 'IW'), -2) FROM DUAL UNION ALL
SELECT 'P11', 39000, ADD_MONTHS(TRUNC(SYSDATE, 'IW'), -2) FROM DUAL UNION ALL
SELECT 'P1', 6000, ADD_MONTHS(TRUNC(SYSDATE, 'IW'), -3) FROM DUAL UNION ALL
SELECT 'P2', 14000, ADD_MONTHS(TRUNC(SYSDATE, 'IW'), -3) FROM DUAL UNION ALL
SELECT 'P3', 19000, ADD_MONTHS(TRUNC(SYSDATE, 'IW'), -3) FROM DUAL UNION ALL
SELECT 'P4', 20000, ADD_MONTHS(TRUNC(SYSDATE, 'IW'), -3) FROM DUAL UNION ALL
SELECT 'P5', 23000, ADD_MONTHS(TRUNC(SYSDATE, 'IW'), -3) FROM DUAL UNION ALL
SELECT 'P6', 31000, ADD_MONTHS(TRUNC(SYSDATE, 'IW'), -3) FROM DUAL UNION ALL
SELECT 'P7', 30000, ADD_MONTHS(TRUNC(SYSDATE, 'IW'), -3) FROM DUAL UNION ALL
SELECT 'P8', 39000, ADD_MONTHS(TRUNC(SYSDATE, 'IW'), -3) FROM DUAL UNION ALL
SELECT 'P9', 51000, ADD_MONTHS(TRUNC(SYSDATE, 'IW'), -3) FROM DUAL UNION ALL
SELECT 'P10', 54000,  ADD_MONTHS(TRUNC(SYSDATE, 'IW'), -3) FROM DUAL UNION ALL
SELECT 'P11', 69000, ADD_MONTHS(TRUNC(SYSDATE, 'IW'), -3) FROM DUAL;

Query:

SELECT 
   RANK() OVER (
     ORDER BY SUM(
       CASE
       WHEN datetime >= TRUNC(SYSDATE, 'IW')
       AND  datetime <  TRUNC(SYSDATE, 'IW')   INTERVAL '7' DAY
       THEN 1
       END
     ) DESC
   ) AS rank_this_week,
   product_name,
   SUM(AMOUNT) TOTAL_AMOUNT
FROM PRODUCT_TABLE
GROUP BY product_name, amount
ORDER BY AMOUNT DESC
FETCH FIRST 10 ROWS ONLY;

Result:

This query does not show correct Top 10 Products (by Amounts) - weekly Rank

CodePudding user response：

You haven't said what your expected output is; however, you appear to want to order by the weekly amount so you need to order by the rank_this_week column and not include amount in the GROUP BY clause:

SELECT RANK() OVER (
         ORDER BY SUM(
           CASE
           WHEN datetime >= TRUNC(SYSDATE, 'IW')
           AND  datetime <  TRUNC(SYSDATE, 'IW')   INTERVAL '7' DAY
           THEN amount
           END
         ) DESC
       ) AS rank_this_week,
       product_name,
       SUM(
         CASE
         WHEN datetime >= TRUNC(SYSDATE, 'IW')
         AND  datetime <  TRUNC(SYSDATE, 'IW')   INTERVAL '7' DAY
         THEN amount
         END
       ) AS total_amount_this_week,
       SUM(AMOUNT) TOTAL_AMOUNT
FROM PRODUCT_TABLE
GROUP BY product_name
ORDER BY rank_this_week
FETCH FIRST 10 ROWS ONLY;

Which, for the sample data, outputs:

RANK_THIS_WEEK PRODUCT_NAME TOTAL_AMOUNT_THIS_WEEK TOTAL_AMOUNT

1 P11 34000 190000

2 P10 26000 152000

3 P9 24000 234000

4 P8 20000 182000

5 P7 19000 143000

6 P3 18000 100000

7 P6 16000 146000

8 P2 15000 77000

9 P5 13000 111000

10 P4 11000 97000

RANK_THIS_WEEK	PRODUCT_NAME	TOTAL_AMOUNT_THIS_WEEK	TOTAL_AMOUNT
1	P11	34000	190000
2	P10	26000	152000
3	P9	24000	234000
4	P8	20000	182000
5	P7	19000	143000
6	P3	18000	100000
7	P6	16000	146000
8	P2	15000	77000
9	P5	13000	111000
10	P4	11000	97000

db<>fiddle here

CodePudding user response：

After you posted a comment and displayed desired result, here's one option. Read comments within code.

SQL> with
  2  -- Each DATES CTE returns desired "date" value
  3  dates_tw  as (select to_char(sysdate, 'yyyy iw') this_week                 from dual),
  4  dates_pw  as (select to_char(sysdate - 7, 'yyyy iw') previous_week         from dual),
  5  dates_lm  as (select trunc(add_months(sysdate, -1), 'mm') last_month       from dual),
  6  dates_ma2 as (select trunc(add_months(sysdate, -2), 'mm') two_months_ago   from dual),
  7  dates_ma3 as (select trunc(add_months(sysdate, -3), 'mm') three_months_ago from dual),

  8  sums as
  9  -- compute total amount for reach "date" value from DATES CTEs, hence CROSS JOIN.
 10  -- As DATES CTEs contain a single row, that shouldn't be too bad (performance wise)
 11    (select p.product_name,
 12        sum(case when to_char(p.datetime, 'yyyy iw') = tw.this_week         then amount else 0 end) sum_tw,
 13        sum(case when to_char(p.datetime, 'yyyy iw') = pw.previous_week     then amount else 0 end) sum_pw,
 14        sum(case when trunc(p.datetime, 'mm')        = lm.last_month        then amount else 0 end) sum_lm,
 15        sum(case when trunc(p.datetime, 'mm')        = ma2.two_months_ago   then amount else 0 end) sum_ma2,
 16        sum(case when trunc(p.datetime, 'mm')        = ma3.three_months_ago then amount else 0 end) sum_ma3
 17      from product_table p cross join dates_tw tw
 18                           cross join dates_pw pw
 19                           cross join dates_lm lm
 20                           cross join dates_ma2 ma2
 21                           cross join dates_ma3 ma3
 22      group by product_name
 23    ),

 24  ranks as
 25  -- rank products per each total amount
 26    (select s.product_name,
 27       rank() over (order by sum_tw desc) rnk_tw,
 28       rank() over (order by sum_pw desc) rnk_pw,
 29       rank() over (order by sum_lm desc) rnk_lm,
 30       rank() over (order by sum_ma2 desc) rnk_ma2,
 31       rank() over (order by sum_ma3 desc) rnk_ma3
 32     from sums s
 33    )

 34  -- finally, return only product that "now" rank as the first 5 (or as many as you want),
 35  -- and display their rank for other dates
 36  select rnk_tw as "rank this week",
 37         product_name,
 38         rnk_pw as "previous week",
 39         rnk_lm as "last month",
 40         rnk_ma2 as "2 months ago",
 41         rnk_ma3 as "3 months ago"
 42  from ranks
 43  where rnk_tw <= 5
 44  order by rnk_tw;

rank this week PRODUCT_NAME         previous week last month 2 months ago 3 months ago
-------------- -------------------- ------------- ---------- ------------ ------------
             1 P11                              8          5            3            1
             2 P10                             10          7            4            2
             3 P9                               1          1            1            3
             4 P8                               2          2            2            4
             5 P7                               4          4            6            6

SQL>