5-Year Interview: Ranking, Joins & Aggregation

1. Practice Database Structure

Every question in this series uses the same six tables, so you can create them once and run each query yourself.

customers

Used for duplicate emails (Q7) and customers without orders (Q8).

products

Catalogue joined through order_items.

orders

Latest order (Q9), top customer (Q10) and monthly sales (Q11–Q12).

order_items

One row per product line in an order.

employees

Salary ranking and department questions (Q1–Q6).

departments

Joined to employees on department_id.

2. Q1. Find the Second-Highest Salary

Find the second-highest distinct salary.

Subquery solution

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

Better: window function

SELECT salary
FROM (
    SELECT
        salary,
        DENSE_RANK() OVER (ORDER BY salary DESC) AS rnk
    FROM employees
) x
WHERE rnk = 2;

3. Q2. Find the Third-Highest Salary

Solution

SELECT salary
FROM (
    SELECT
        salary,
        DENSE_RANK() OVER (
            ORDER BY salary DESC
        ) AS rnk
    FROM employees
) x
WHERE rnk = 3;

4. Q3. Highest-Paid Employee in Each Department

Solution

SELECT *
FROM (
    SELECT
        e.*,
        ROW_NUMBER() OVER (
            PARTITION BY department_id
            ORDER BY salary DESC
        ) AS rn
    FROM employees e
) x
WHERE rn = 1;

5. Q4. Top 3 Employees from Each Department

One of the most commonly asked questions at 5 years of experience.

Solution

SELECT *
FROM (
    SELECT
        e.*,
        DENSE_RANK() OVER (
            PARTITION BY department_id
            ORDER BY salary DESC
        ) AS rnk
    FROM employees e
) x
WHERE rnk <= 3;

6. Q5. Employees Earning More Than the Company Average

Solution

SELECT
    id,
    name,
    salary
FROM employees
WHERE salary > (
    SELECT AVG(salary)
    FROM employees
);

Follow-up: the same query with a CTE

WITH company_average AS (
    SELECT AVG(salary) AS avg_salary
    FROM employees
)
SELECT
    e.id,
    e.name,
    e.salary
FROM employees e
CROSS JOIN company_average a
WHERE e.salary > a.avg_salary;

7. Q6. Employees Earning More Than Their Department Average

Solution

SELECT *
FROM (
    SELECT
        e.*,
        AVG(salary) OVER (
            PARTITION BY department_id
        ) AS department_avg
    FROM employees e
) x
WHERE salary > department_avg;

8. Q7. Find Duplicate Customer Emails

Solution

SELECT
    email,
    COUNT(*) AS total
FROM customers
GROUP BY email
HAVING COUNT(*) > 1;

Follow-up: prevent duplicates at the database level

ALTER TABLE customers
ADD CONSTRAINT uk_customer_email UNIQUE (email);

9. Q8. Customers Who Never Placed an Order

Solution 1: LEFT JOIN

SELECT
    c.id,
    c.name
FROM customers c
LEFT JOIN orders o
    ON c.id = o.customer_id
WHERE o.id IS NULL;

Solution 2: NOT EXISTS

SELECT
    c.id,
    c.name
FROM customers c
WHERE NOT EXISTS (
    SELECT 1
    FROM orders o
    WHERE o.customer_id = c.id
);

10. Q9. Latest Order of Every Customer

Solution

SELECT *
FROM (
    SELECT
        o.*,
        ROW_NUMBER() OVER (
            PARTITION BY customer_id
            ORDER BY order_date DESC, id DESC
        ) AS rn
    FROM orders o
) x
WHERE rn = 1;

11. Q10. Customer with the Highest Total Purchase

Solution

SELECT
    customer_id,
    SUM(total_amount) AS total_purchase
FROM orders
GROUP BY customer_id
ORDER BY total_purchase DESC
LIMIT 1;

Better when ties matter

SELECT *
FROM (
    SELECT
        customer_id,
        SUM(total_amount) AS total_purchase,
        DENSE_RANK() OVER (
            ORDER BY SUM(total_amount) DESC
        ) AS rnk
    FROM orders
    GROUP BY customer_id
) x
WHERE rnk = 1;

If two customers both spent ₹5 lakh, the ranked version returns both, while LIMIT 1 silently drops one.

12. Q11 (Bonus). Monthly Sales

Solution

SELECT
    YEAR(order_date) AS year,
    MONTH(order_date) AS month,
    SUM(total_amount) AS total_sales
FROM orders
GROUP BY
    YEAR(order_date),
    MONTH(order_date)
ORDER BY
    year,
    month;

13. Q12 (Bonus). Month-over-Month Sales Growth

Step 1: monthly sales with the previous month

WITH monthly_sales AS (
    SELECT
        YEAR(order_date) AS year,
        MONTH(order_date) AS month,
        SUM(total_amount) AS sales
    FROM orders
    GROUP BY
        YEAR(order_date),
        MONTH(order_date)
)
SELECT
    year,
    month,
    sales,
    LAG(sales) OVER (
        ORDER BY year, month
    ) AS previous_month_sales
FROM monthly_sales;

Step 2: growth percentage

WITH monthly_sales AS (
    SELECT
        YEAR(order_date) AS year,
        MONTH(order_date) AS month,
        SUM(total_amount) AS sales
    FROM orders
    GROUP BY
        YEAR(order_date),
        MONTH(order_date)
),
sales_with_previous AS (
    SELECT
        year,
        month,
        sales,
        LAG(sales) OVER (
            ORDER BY year, month
        ) AS previous_sales
    FROM monthly_sales
)
SELECT
    year,
    month,
    sales,
    previous_sales,
    ROUND(
        ((sales - previous_sales) / previous_sales) * 100,
        2
    ) AS growth_percentage
FROM sales_with_previous;

14. 5-Year SQL Interview Progression