Find the second top selling product in terms of sales and quantity-CodePudding

There are two Tables - orders and item_line

orders

order_id	created_at	total_amount
123	2022-11-11 13:40:50	450.00
124	2022-10-30 00:40:50	1500.00

item_line

order_id	product_id	product_name	quantity	unit_price
123	a1b	milo	4	100.00
123	c2d	coke	5	10.00
124	c2d	coke	150	10.00

The question is: Find the second top selling product in terms of sales and quantity in the current year sold between 6PM to 9PM.

My Take on This is -

SELECT * FROM (
    SELECT i.product_name,
            SUM(o.total_amount)sales,
            SUM(i.quantity)total_qty,
            ROW_NUMBER() OVER (ORDER BY SUM(o.total_amount) DESC,SUM(i.quantity)total_qty DESC) AS rn
    FROM item_line i
    WHERE o.created_at BETWEEN 18:00:00 AND 21:00:00
    JOIN orders o on o.order_id = i.order_id
    GROUP BY i.product_name ) temp
WHERE rn = 2;

But it's not correct. What wrong I am doing?

CodePudding user response：

SELECT * FROM ( 
    SELECT i.product_name,SUM(o.total_amount)AS 'Net Sales',
    ROW_NUMBER() OVER(ORDER BY SUM(o.total_amount) DESC) AS rn
    FROM item_line i
    JOIN orders o on o.order_id = i.order_id
    WHERE DATEPART(HOUR,o.created_at) BETWEEN 18 AND 21
    GROUP BY i.product_name) temp
WHERE rn =2;

        -- In terms of total quantity
SELECT * FROM ( 
    SELECT i.product_name,SUM(i.quantity)AS 'Total Quantity',
    ROW_NUMBER() OVER(ORDER BY SUM(i.quantity) DESC) AS rn
    FROM item_line i
    JOIN orders o on o.order_id = i.order_id
    WHERE DATEPART(HOUR,o.created_at) BETWEEN 18 AND 21
    GROUP BY i.product_name) temp
WHERE rn =2;

CodePudding user response：

select o.order_id, sum(quantity), total_amount from orders [o] 
inner join item_line[i] on o.order_id = i.order_id
group by o.order_id, total_amount order by total_amount desc, sum(quantity) desc

OFFSET     1 ROWS       
FETCH NEXT 1 ROWS ONLY;

you can add target date time in filter