Retrieve Nth Highest Value Using Window Functions

SELECT product_name, price
FROM (
    SELECT
        product_name,
        price,
        DENSE_RANK() OVER (ORDER BY price DESC) as rank_num
    FROM products
) AS ranked_products
WHERE rank_num = 3; -- To find the 3rd highest price

How it works: This query uses a window function, DENSE_RANK(), to assign a rank to each product based on its price in descending order. The outer query then filters these ranked results to retrieve products with a specific rank (e.g., the 3rd highest price). This is a highly efficient way to find Nth highest/lowest values, implement leaderboards, or identify top performers in various web application scenarios, avoiding less efficient subquery methods.