Second-Highest Price in Python

A classic SQL interview question: "What is the second-highest price in the products table?" The idea in one sentence: drop every row at the top price, and the MAX of what is left is second place. For this lesson, a hidden setup adds a Kettle for 50, so two products share the top price:

name      | price
Gift card | 50
Kettle    | 50
Lamp      | 29
Teapot    | 22
…         | …

The answer is 29, not 50: the question asks for a price, and two products at 50 are still one price. That is why "sort high to low and take the second row" fails here. A MAX(…) can't sit inside a WHERE, so the top price comes from a subquery. On a made-up scores table it looks like this:

SELECT MAX(points) AS runner_up
FROM scores
WHERE points < (SELECT MAX(points) FROM scores)

Then the interviewer asks: "And the third-highest? The Nth?" The general way is a window function that numbers the values from the top. DENSE_RANK() gives tied values the same number and leaves no gap; RANK() skips a number after a tie, and ROW_NUMBER() ignores ties:

points | ROW_NUMBER | RANK | DENSE_RANK
90     | 1          | 1    | 1
90     | 2          | 1    | 1
75     | 3          | 3    | 2

A window column can't be filtered in its own query's WHERE, so you rank inside a CTE, WITH ranked AS (…), and keep rank 2 in the main query. Your code must print:

second_highest
29
(1 row)
name | price
Lamp | 29
(1 row)

Your task: db is already connected to shop.db. In query, print the second-highest price as second_highest, the MAX below the MAX. In rank_query, rank the products with DENSE_RANK in a CTE named ranked, then print the name and price of rank 2. Each clause is its own string inside the brackets; Python glues them together, so each one ends with a space.

Assignment
Follow the Coding Tutorial to build query one clause per step, then rank_query with its CTE.

Hint
The subquery (SELECT MAX(price) FROM products) is the top price, 50: keep only the prices below it. In rank_query, the WHERE price_rank = 2 goes after FROM ranked, outside the brackets.