🚀 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