🚀 SQL Scenario Based Interview Questions with Answers Part 2📌 Question 11: Find the Second Highest Salary in Each Department Table: employees (employee_id, department_id, salary)
WITH ranked_salary AS (
SELECT *,
DENSE_RANK() OVER (
PARTITION BY department_id
ORDER BY salary DESC
) AS rnk
FROM employees
)
SELECT department_id, employee_id, salary
FROM ranked_salary
WHERE rnk = 2;
📌 Question 12: Identify Users Who Purchased on Their First Visit Tables: visits (user_id, visit_date) | orders (user_id, order_date)
WITH first_visit AS (
SELECT user_id,
MIN(visit_date) AS first_visit_date
FROM visits
GROUP BY user_id
)
SELECT DISTINCT f.user_id
FROM first_visit f
JOIN orders o
ON f.user_id = o.user_id
AND f.first_visit_date = o.order_date;
📌 Question 13: Find Products Never Sold Tables: products (product_id, product_name) | sales (product_id)
SELECT p.product_id, p.product_name
FROM products p
LEFT JOIN sales s
ON p.product_id = s.product_id
WHERE s.product_id IS NULL;
📌 Question 14: Calculate Month-over-Month Revenue Growth Table: orders (order_date, revenue)
WITH monthly_revenue AS (
SELECT DATE_TRUNC('month', order_date) AS month,
SUM(revenue) AS total_revenue
FROM orders
GROUP BY 1
)
SELECT month,
total_revenue,
LAG(total_revenue) OVER (ORDER BY month) AS previous_month,
ROUND(
100.0 *
(total_revenue - LAG(total_revenue) OVER (ORDER BY month))
/
LAG(total_revenue) OVER (ORDER BY month),
2
) AS growth_pct
FROM monthly_revenue;
📌 Question 15: Find Employees Earning More Than Department Average Table: employees (employee_id, department_id, salary)
SELECT employee_id, department_id, salary
FROM (
SELECT *,
AVG(salary) OVER (
PARTITION BY department_id
) AS dept_avg
FROM employees
) t
WHERE salary > dept_avg;
📌 Question 16: Find Longest Consecutive Login Streak Table: logins (user_id, login_date)
WITH cte AS (
SELECT user_id,
login_date,
login_date -
ROW_NUMBER() OVER (
PARTITION BY user_id
ORDER BY login_date
) * INTERVAL '1 day' AS grp
FROM logins
)
SELECT user_id, COUNT(*) AS streak_days
FROM cte
GROUP BY user_id, grp
ORDER BY streak_days DESC;
📌 Question 17: Find Peak Sales Day of Every Month Table: sales (sale_date, amount)
WITH daily_sales AS (
SELECT DATE(sale_date) AS sale_day,
SUM(amount) AS revenue
FROM sales
GROUP BY DATE(sale_date)
)
SELECT *
FROM (
SELECT *,
ROW_NUMBER() OVER (
PARTITION BY DATE_TRUNC('month', sale_day)
ORDER BY revenue DESC
) rn
FROM daily_sales
) t
WHERE rn = 1;
📌 Question 18: Find Customers Who Ordered Every Month Table: orders (customer_id, order_date)
WITH customer_months AS (
SELECT customer_id,
COUNT(DISTINCT DATE_TRUNC('month', order_date)) AS months_active
FROM orders
GROUP BY customer_id
),
total_months AS (
SELECT COUNT(DISTINCT DATE_TRUNC('month', order_date)) AS total_months
FROM orders
)
SELECT customer_id
FROM customer_months c
CROSS JOIN total_months t
WHERE c.months_active = t.total_months;
📌 Question 19: Find Top Selling Product Category Tables: products (product_id, category) | sales (product_id, quantity)
SELECT category, SUM(quantity) AS total_sold
FROM sales s
JOIN products p ON s.product_id = p.product_id
GROUP BY category
ORDER BY total_sold DESC
LIMIT 1;
📌 Question 20: Calculate Median Salary Table: employees (employee_id, salary)
SELECT PERCENTILE_CONT(0.5)
WITHIN GROUP (ORDER BY salary)
AS median_salary
FROM employees;
💡 Double Tap ❤️ For More 
