Objective

Given a Sales table containing records of product sales, each with a unique identifier, product name, and revenue generated, write a SQL query to determine the third highest distinct revenue value from the sales data.

Additional information

• If there are fewer than three distinct revenue values, the query should return null.
• The result should be a single column named revenue.
• The id column is guaranteed to be unique for each record.
• Use standard SQL syntax without relying on database-specific extensions.

SELECT revenue
FROM (
    SELECT
        revenue,
        ROW_NUMBER() OVER (ORDER BY revenue DESC) AS rank
    FROM (
        SELECT DISTINCT revenue FROM sales
    ) distinct_sales
) ranked
WHERE rank = 3;

Explanation

Step 1: Extract distinct revenue values

The innermost subquery uses SELECT DISTINCT revenue FROM sales to eliminate duplicate revenue values so each unique amount is counted only once when ranking.

SELECT DISTINCT revenue FROM sales

Step 2: Assign a sequential rank to each distinct revenue

ROW_NUMBER() OVER (ORDER BY revenue DESC) numbers the distinct revenues starting from 1 for the highest value. Unlike RANK(), ROW_NUMBER() never assigns the same number twice.

SELECT revenue, ROW_NUMBER() OVER (ORDER BY revenue DESC) AS rank
FROM (...) distinct_sales

Step 3: Filter for rank = 3

The outer query selects only the row where rank = 3, which is the third highest distinct revenue. If fewer than three distinct values exist, no row matches and the query returns nothing (equivalent to NULL).

WHERE rank = 3;