-1

I want to retrieve the Top 10 Products - weekly

Rank     This week product   Previous week rank   Last month rank   2 month ago rank     3 month ago rank
 1             Watch                  2                    3                    1               4
 2             Radio                  3                    2                    4               5
 3             Pen                    4                    5                    6               7
 4
 5
 6
 7
 8
 9
 10

Please help me with how to get this type of data from SQL queries. Thanks

MT0
  • 143,790
  • 11
  • 59
  • 117
Ramiz Tariq
  • 387
  • 10
  • 27
  • See [Why should I provide a Minimal Reproducible Example](https://meta.stackoverflow.com/questions/333952/why-should-i-provide-a-minimal-reproducible-example-for-a-very-simple-sql-query) – Raymond Nijland Feb 24 '22 at 14:28
  • Please [edit] the question to include a [MRE] with: the `CREATE TABLE` statement(s) for the table(s); the `INSERT` statements for some sample data; a complete description of the problem including how the ranks should be calculated; the expected output for the sample data; **YOUR** attempt at a solution; the issues/errors with **YOUR** solution. If we don't know what data you have or how the ranks should be calculated then we either would have to guess or we cannot answer the question; please help us to help you by giving a complete question. – MT0 Feb 24 '22 at 15:09
  • https://stackoverflow.com/questions/53372649/sql-join-table-to-itself-to-get-data-for-previous-year?answertab=votes#tab-top – Brandon_R Feb 24 '22 at 15:20

1 Answers1

1

Assuming you have a table:

CREATE TABLE your_table (
  product          VARCHAR2(20),
  some_date_column DATE
)

Then you can use conditional aggregation inside the RANK analytic function:

SELECT product,
       RANK() OVER (
         ORDER BY COUNT(
           CASE
           WHEN some_date_column >= TRUNC(SYSDATE, 'IW')
           AND  some_date_column <  TRUNC(SYSDATE, 'IW') + INTERVAL '7' DAY
           THEN 1
           END
         ) DESC
       ) AS rank_this_week,
       RANK() OVER (
         ORDER BY COUNT(
           CASE
           WHEN some_date_column >= TRUNC(SYSDATE, 'IW') - INTERVAL '7' DAY
           AND  some_date_column <  TRUNC(SYSDATE, 'IW')
           THEN 1
           END
         ) DESC
       ) AS rank_last_week,
       RANK() OVER (
         ORDER BY COUNT(
           CASE
           WHEN some_date_column >= ADD_MONTHS(TRUNC(SYSDATE, 'IW'), -1)
           AND  some_date_column <  TRUNC(SYSDATE, 'MM')
           THEN 1
           END
         ) DESC
       ) AS rank_last_month,
       RANK() OVER (
         ORDER BY COUNT(
           CASE
           WHEN some_date_column >= ADD_MONTHS(TRUNC(SYSDATE, 'IW'), -2)
           AND  some_date_column <  ADD_MONTHS(TRUNC(SYSDATE, 'IW'), -1)
           THEN 1
           END
         ) DESC
       ) AS rank_two_month,
       RANK() OVER (
         ORDER BY COUNT(
           CASE
           WHEN some_date_column >= ADD_MONTHS(TRUNC(SYSDATE, 'IW'), -3)
           AND  some_date_column <  ADD_MONTHS(TRUNC(SYSDATE, 'IW'), -2)
           THEN 1
           END
         ) DESC
       ) AS rank_three_month
FROM   your_table
WHERE  some_date_column < TRUNC(SYSDATE, 'IW') + INTERVAL '7' DAY
AND    some_date_column >= ADD_MONTHS(TRUNC(SYSDATE, 'IW'), -3)
GROUP BY product
ORDER BY rank_this_week
FETCH FIRST 10 ROWS WITH TIES;

Which, for the sample data:

INSERT INTO your_table (product, some_date_column)
SELECT 'A', TRUNC(SYSDATE, 'IW') FROM DUAL CONNECT BY LEVEL <=  1 UNION ALL
SELECT 'B', TRUNC(SYSDATE, 'IW') FROM DUAL CONNECT BY LEVEL <=  2 UNION ALL
SELECT 'C', TRUNC(SYSDATE, 'IW') FROM DUAL CONNECT BY LEVEL <=  3 UNION ALL
SELECT 'D', TRUNC(SYSDATE, 'IW') FROM DUAL CONNECT BY LEVEL <=  4 UNION ALL
SELECT 'E', TRUNC(SYSDATE, 'IW') FROM DUAL CONNECT BY LEVEL <=  5 UNION ALL
SELECT 'F', TRUNC(SYSDATE, 'IW') FROM DUAL CONNECT BY LEVEL <=  6 UNION ALL
SELECT 'G', TRUNC(SYSDATE, 'IW') FROM DUAL CONNECT BY LEVEL <=  7 UNION ALL
SELECT 'H', TRUNC(SYSDATE, 'IW') FROM DUAL CONNECT BY LEVEL <=  8 UNION ALL
SELECT 'I', TRUNC(SYSDATE, 'IW') FROM DUAL CONNECT BY LEVEL <=  9 UNION ALL
SELECT 'J', TRUNC(SYSDATE, 'IW') FROM DUAL CONNECT BY LEVEL <= 10 UNION ALL
SELECT 'K', TRUNC(SYSDATE, 'IW') FROM DUAL CONNECT BY LEVEL <= 11 UNION ALL
SELECT 'A', TRUNC(SYSDATE, 'IW') - INTERVAL '7' DAY FROM DUAL CONNECT BY LEVEL <=  3 UNION ALL
SELECT 'B', TRUNC(SYSDATE, 'IW') - INTERVAL '7' DAY FROM DUAL CONNECT BY LEVEL <=  3 UNION ALL
SELECT 'C', TRUNC(SYSDATE, 'IW') - INTERVAL '7' DAY FROM DUAL CONNECT BY LEVEL <=  1 UNION ALL
SELECT 'D', TRUNC(SYSDATE, 'IW') - INTERVAL '7' DAY FROM DUAL CONNECT BY LEVEL <=  2 UNION ALL
SELECT 'E', TRUNC(SYSDATE, 'IW') - INTERVAL '7' DAY FROM DUAL CONNECT BY LEVEL <=  5 UNION ALL
SELECT 'F', TRUNC(SYSDATE, 'IW') - INTERVAL '7' DAY FROM DUAL CONNECT BY LEVEL <=  7 UNION ALL
SELECT 'G', TRUNC(SYSDATE, 'IW') - INTERVAL '7' DAY FROM DUAL CONNECT BY LEVEL <=  6 UNION ALL
SELECT 'H', TRUNC(SYSDATE, 'IW') - INTERVAL '7' DAY FROM DUAL CONNECT BY LEVEL <=  4 UNION ALL
SELECT 'I', TRUNC(SYSDATE, 'IW') - INTERVAL '7' DAY FROM DUAL CONNECT BY LEVEL <=  5 UNION ALL
SELECT 'J', TRUNC(SYSDATE, 'IW') - INTERVAL '7' DAY FROM DUAL CONNECT BY LEVEL <=  3 UNION ALL
SELECT 'K', TRUNC(SYSDATE, 'IW') - INTERVAL '7' DAY FROM DUAL CONNECT BY LEVEL <=  2
-- Etc.

(Note: this only has data for the last 2 weeks so all the other ranks will be ties.)

Outputs:

PRODUCT RANK_THIS_WEEK RANK_LAST_WEEK RANK_LAST_MONTH RANK_TWO_MONTH RANK_THREE_MONTH
K 1 9 1 1 1
J 2 6 1 1 1
I 3 3 1 1 1
H 4 5 1 1 1
G 5 2 1 1 1
F 6 1 1 1 1
E 7 3 1 1 1
D 8 9 1 1 1
C 9 11 1 1 1
B 10 6 1 1 1

db<>fiddle here

MT0
  • 143,790
  • 11
  • 59
  • 117