🧠 SQL Interview Question (Commonly Asked)
📌

products(product_id, product_name, category_id, price)

❓ Ques :

👉 Find the second highest priced product in each category.

🧩 How Interviewers Expect You to Think

• Partition data by category
• Rank products based on price (descending)
• Understand difference between RANK, DENSE_RANK, and ROW_NUMBER
• Handle ties properly
• Filter after ranking logic

💡 SQL Solution

WITH ranked_products AS (
SELECT
product_id,
product_name,
category_id,
price,
DENSE_RANK() OVER (
PARTITION BY category_id
ORDER BY price DESC
) AS price_rank
FROM products
)

SELECT
product_id,
product_name,
category_id,
price
FROM ranked_products
WHERE price_rank = 2;

🔥 Why this question is powerful:

• Tests window functions deeply
• Checks ranking logic understanding
• Very common in Data Analyst interviews

❤️ React if you want more scenario-based SQL questions