Find Second Highest Order
MediumPremium
You are given the following tables:
orders table:
departments table:
customers table:
Find the 2nd highest order amount in the fashion department. Your output should have the following column: order_amount
WITH cte AS (
SELECT
d.department_name,
o.order_amount,
RANK() OVER (PARTITION BY d.department_name ORDER BY o.order_amount DESC) AS amount_ranking
FROM orders o
JOIN departments d ON o.department_id = d.department_id
WHERE d.department_name = 'Fashion'
)
SELECT
order_amount
FROM cte
WHERE amount_ranking = 2;
SELECT o.order_amount FROM orders o JOIN departments d ON d.department_id = o.department_id WHERE d.department_name = 'Fashion' ORDER BY order_amount DESC LIMIT 1 OFFSET 1;