Top SQL Interview Questions

 SELECT MAX(salary) AS SecondHighestSalary
 FROM employees
 WHERE salary < (SELECT MAX(salary) FROM employees);

 SELECT salary AS SecondHighestSalary
 FROM employees
 ORDER BY salary DESC
 LIMIT 1 OFFSET 1;

 SELECT salary AS SecondHighestSalary
 FROM (
     SELECT salary, row_number() OVER (ORDER BY salary DESC) AS rank
     FROM employees
 ) AS RankedSalaries
 WHERE rank = 2;

 SELECT column_name, COUNT(*) AS count
 FROM table_name
 GROUP BY column_name
 HAVING COUNT(*) > 1;

 SELECT e1.*
 FROM employees e1
 JOIN employees e2 ON e1.manager_id = e2.id
 WHERE e1.salary > e2.salary;

 SELECT *
 FROM table_name
 ORDER BY column_name DESC
 LIMIT N;

 SELECT department, COUNT(*) AS employee_count
 FROM employees
 GROUP BY department;

 SELECT department, COUNT(*) AS employee_count
 FROM employees
 GROUP BY department
 ORDER BY employee_count DESC
 LIMIT 1;

 SELECT e1.*
 FROM employees e1
 JOIN employees e2 ON e1.salary = e2.salary
 WHERE e1.id != e2.id;

 SELECT *
 FROM employees
 WHERE name LIKE 'A%';

 SELECT *
 FROM employees
 ORDER BY employee_id DESC
 LIMIT 1;

SELECT *
FROM employees
WHERE join_date >= DATE_SUB(CURDATE(), INTERVAL 6 MONTH);

SELECT DISTINCT salary AS NthHighestSalary
FROM employees
ORDER BY salary DESC
LIMIT 1 OFFSET N-1;

SELECT salary AS NthHighestSalary
FROM (
    SELECT salary, ROW_NUMBER() OVER (ORDER BY salary DESC) AS rank
    FROM employees
) AS RankedSalaries
WHERE rank = N;

DELETE FROM sales
WHERE id NOT IN (
    SELECT id
    FROM (
        SELECT id, ROW_NUMBER() OVER (PARTITION BY order_date, product_name, customer_id ORDER BY id) AS rnk
    ) AS RankedSales
    WHERE rnk = 1
)

SELECT t1.id + 1 AS MissingID
FROM employees t1
LEFT JOIN employees t2 ON t1.id + 1 = t2.id
WHERE t2.id IS NULL
ORDER BY MissingID;

SELECT CONCAT(first_name, ' ', last_name) AS full_name
FROM employees;

SELECT id, name, salary, SUM(salary) OVER (ORDER BY id) AS cumulative_salary
FROM employees;

UPDATE employees
SET column1 = column2, column2 = column1
WHERE condition;

SELECT *
FROM employees
WHERE name REGEXP '^[aeiouAEIOU]+$';

SELECT 
    Year,
    SUM(CASE WHEN Product = 'A' THEN Amount ELSE 0 END) AS Product_A,
    SUM(CASE WHEN Product = 'B' THEN Amount ELSE 0 END) AS Product_B
FROM 
    Sales
GROUP BY 
    Year
ORDER BY 
    Year;

SELECT id, name, department, salary
FROM (
    SELECT
        e.*,
        RANK() OVER (PARTITION BY department ORDER BY salary DESC) as rnk
    FROM employees e
) sub
WHERE rnk = 1;

SELECT customer_id, order_date, COUNT(*) AS purchase_count
FROM orders
GROUP BY customer_id, order_date
HAVING COUNT(*) > 1;

SELECT 
    month,
    AVG(sales) OVER (ORDER BY month ROWS BETWEEN 2 PRECEDING AND CURRENT ROW) AS moving_average
FROM Sales
ORDER BY month;

SELECT 
    id, 
    name, 
    department, 
    salary, 
    RANK() OVER (PARTITION BY department ORDER BY salary DESC) AS salary_rank
FROM employees;

SELECT employee_id, COUNT(DISTINCT manager_id) AS manager_count
FROM employee_managers
GROUP BY employee_id
HAVING manager_count > 1;

SELECT order_date, COUNT(*) AS order_count
FROM orders
GROUP BY order_date
ORDER BY order_count DESC
LIMIT 1;

SELECT *
FROM TableA a
FULL OUTER JOIN TableB b ON a.id = b.id
WHERE a.id IS NULL OR b.id IS NULL;

SELECT 
    id, 
    value,
    value - LAG(value) OVER (ORDER BY id) AS difference
FROM TableName;

-- Outer SELECT to choose all columns resulting from the PIVOT operation
SELECT *
FROM (
    -- Inner SELECT to retrieve the raw data of city, year, and sales
    SELECT city, year, sales
    FROM sales
)
-- PIVOT operation to convert rows to columns
PIVOT (
    SUM(sales) 
    -- Specify the year values to pivot and alias them as Sales_<year>
    FOR year IN (2019 AS Sales_2019, 2020 AS Sales_2020, 2021 AS Sales_2021)
)
ORDER BY city;

WITH CTE AS (
    SELECT *, ROW_NUMBER() OVER (ORDER BY id) AS row_num
    FROM table_name
)
DELETE FROM table_name
WHERE id IN (SELECT id FROM CTE WHERE row_num % 2 = 0);

SELECT customer_id, MIN(ordered_at) AS first_purchase_date
FROM orders
GROUP BY customer_id;

WITH MonthlySales AS (
    SELECT 
        month,
        count(distinct order_id) AS monthly_sales
    FROM orders
    WHERE ordered_at IS NOT NULL
    AND cancelled_at IS NULL
    GROUP BY month
)
SELECT 
    month,
    monthly_sales,
    SUM(monthly_sales) OVER (ORDER BY month) AS running_total
FROM MonthlySales
ORDER BY month;

SELECT 
    id, 
    name, 
    salary,
    RANK() OVER (ORDER BY salary DESC) AS salary_rank
FROM employees;

SELECT 
    id, 
    name, 
    salary,
    (salary / SUM(salary) OVER ()) * 100 AS salary_percentage
FROM employees;

SELECT
    order_date,
    sales_amount,
    SUM(sales_amount) OVER (ORDER BY order_date) AS cumulative_sales
FROM orders;

SELECT 
    id, 
    transaction_date, 
    amount,
    amount - LAG(amount) OVER (ORDER BY transaction_date) AS transaction_difference
FROM transactions;

SELECT 
    id, 
    name, 
    salary,
    LEAD(salary) OVER (ORDER BY id) AS next_salary,
    LAG(salary) OVER (ORDER BY id) AS previous_salary
FROM
    employees;