I have a transaction table, I want to get the product ID of the record which is ranked 3rd highest for sales. Please note there there can be multiple transactions for an item and one transaction can have multiple qty. so i am wanting to find which product id has the 3rd highest qty.

I think i need to use something like rank, but my query returns ranks weirdly, not sure whats wrong.

select distinct t.product_id,
  sum(t.qty) over (partition by t.product_id) qty, 
  rank() over(partition by t.product_id order by t.qty desc) rnk
from transaction t
order by rnk`

CREATE TABLE IF NOT EXISTS "transaction"
  (DATE_ID             BIGINT NOT NULL,
   STORE_ID            INT NOT NULL,
   TRANSACTION_TYPE_ID CHAR(1) NOT NULL,
   PRODUCT_ID          INT NOT NULL,
   QTY                 INT NOT NULL);

INSERT INTO "transaction"
  (DATE_ID, STORE_ID, TRANSACTION_TYPE_ID, PRODUCT_ID, QTY)
VALUES
  (1, 1, 'A', 1, 2),
  (1, 1, 'B', 1, 1),
  (1, 2, 'A', 4, 1),
  (1, 6, 'A', 3, 1),
  (1, 1, 'A', 1, 1),
  (2, 1, 'B', 1, 1),
  (2, 1, 'A', 1, 1),
  (2, 2, 'A', 2, 5),
  (3, 2, 'A', 2, 7),
  (3, 3, 'A', 2, 1),
  (3, 3, 'B', 1, 15),
  (3, 3, 'A', 1, 1),
  (4, 4, 'A', 1, 1),
  (4, 4, 'A', 1, 5),
  (4, 4, 'A', 1, 11),
  (4, 5, 'A', 3, 2),
  (4, 6, 'A', 3, 1),
  (4, 6, 'A', 3, 1),
  (4, 6, 'B', 2, 1),
  (5, 2, 'A', 2, 2),
  (5, 2, 'B', 1, 1),
  (5, 2, 'A', 2, 1),
  (5, 2, 'A', 4, 1),
  (5, 2, 'A', 5, 1),
  (6, 2, 'B', 4, 1),
  (6, 2, 'A', 6, 1),
  (6, 3, 'A', 3, 5),
  (7, 3, 'A', 2, 7),
  (7, 4, 'A', 2, 1),
  (7, 4, 'B', 2, 15),
  (7, 4, 'A', 2, 1),
  (7, 5, 'A', 2, 1),
  (7, 5, 'A', 2, 5),
  (7, 5, 'A', 2, 11),
  (7, 6, 'A', 2, 2),
  (7, 1, 'A', 2, 1),
  (8, 1, 'A', 2, 1),
  (8, 1, 'B', 2, 1),
  (8, 3, 'A', 3, 2),
  (9, 3, 'B', 3, 1),
  (9, 3, 'A', 3, 1),
  (9, 3, 'A', 3, 1),
  (9, 3, 'A', 3, 1),
  (10, 3, 'B', 3, 1),
  (10, 3, 'A', 3, 1),
  (10, 4, 'A', 4, 5),
  (10, 4, 'A', 4, 7),
  (10, 5, 'A', 5, 1),
  (10, 5, 'B', 5, 15),
  (10, 5, 'A', 5, 1),
  (10, 6, 'A', 6, 1),
  (10, 6, 'A', 6, 5),
  (10, 6, 'A', 6, 11),
  (10, 1, 'A', 1, 2),
  (10, 2, 'A', 2, 1),
  (11, 2, 'A', 2, 1),
  (11, 2, 'B', 2, 1),
  (11, 3, 'A', 5, 2),
  (11, 3, 'B', 5, 1),
  (11, 3, 'A', 5, 1),
  (12, 3, 'A', 5, 1),
  (12, 3, 'A', 5, 1)

CodePudding user response：

Yet another option is:

• aggregating the "qty" values per "product_id" (SUM(qty) GROUP BY product_id)
• extracting a ranking value for each product_id summed quantities (DENSE_RANK() OVER(ORDER BY SUM(qty) DESC))
• ordering your output rows with respect to when this ranking value equals 3 (DENSE_RANK() ... = 3)
• keeping only the first row given your ordering (FETCH FIRST 1 ROWS WITH TIES )

SELECT product_id
FROM "transaction"
GROUP BY product_id
ORDER BY DENSE_RANK() OVER(ORDER BY SUM(qty) DESC) = 3 DESC
FETCH FIRST 1 ROWS WITH TIES 

Check the demo here.

CodePudding user response：

select  product_id
from    (
        select   product_id
                ,dense_rank() over(order by sum(qty)) as d_rnk
        from     transaction
        group by product_id 
        ) t
where   d_rnk = 3

Fiddle