Master SQL with These Expert-Level Interview Questions and Answers

SQL (Structured Query Language) is a cornerstone for any data professional. Whether you are an aspiring data analyst, a database administrator, or a developer, mastering SQL can significantly enhance your career prospects. Below, we’ve curated a list of real-world SQL interview questions divided into three categories: Medium, Challenging, and Advanced Problem-Solving. Each question is accompanied by a detailed answer, making this blog your ultimate resource for SQL interview preparation.

Medium-Level SQL Interview Questions

1. Write a query to find the second highest salary in an employee table.

SELECT MAX(salary) AS second_highest_salary

FROM employees

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

Explanation: This query uses a subquery to find the highest salary and filters the main query to find the next highest.

2. Fetch all employees whose names contain the letter “a” exactly twice.

SELECT *

FROM employees

WHERE name LIKE '%a%a%' AND name NOT LIKE '%a%a%a%';

Explanation: The LIKE operator is used to match patterns in the names.

3. How do you retrieve only duplicate records from a table?

SELECT column1, column2, COUNT(*)

FROM table_name

GROUP BY column1, column2

HAVING COUNT(*) > 1;

Explanation: Grouping by relevant columns and using HAVING ensures only duplicates are fetched.

4. Write a query to calculate the running total of sales by date.

SELECT order_date, sales_amount,

       SUM(sales_amount) OVER (ORDER BY order_date) AS running_total

FROM sales;

Explanation: The SUM function with a WINDOW clause calculates the cumulative total.

5. Find employees who earn more than the average salary in their department.

SELECT employee_id, name, salary, department_id

FROM employees

WHERE salary > (SELECT AVG(salary)

                FROM employees e2

                WHERE e2.department_id = employees.department_id);

Explanation: A correlated subquery compares each employee’s salary against their department’s average.

6. Retrieve the total number of orders placed each day.

SELECT order_date, COUNT(*) AS total_orders

FROM orders

GROUP BY order_date;

Explanation: The COUNT function is used to aggregate orders by date.

7. Fetch all products that have never been ordered.

SELECT p.product_id, p.product_name

FROM products p

LEFT JOIN orders o ON p.product_id = o.product_id

WHERE o.product_id IS NULL;

Explanation: A LEFT JOIN identifies products with no matching orders.

8. Write a query to list all employees hired in the last 30 days.

SELECT *

FROM employees

WHERE hire_date >= DATEADD(DAY, -30, GETDATE());

Explanation: Filtering by hire_date ensures only recent hires are fetched.

9. Retrieve the top 3 earning employees in each department.

SELECT department_id, employee_id, salary

FROM (

    SELECT department_id, employee_id, salary,

           RANK() OVER (PARTITION BY department_id ORDER BY salary DESC) AS rank

    FROM employees

) ranked

WHERE rank <= 3;

Explanation: The RANK function helps in identifying the top earners.

Challenging SQL Interview Questions

1. Retrieve customers who made their first purchase in the last 6 months.

SELECT customer_id, MIN(order_date) AS first_purchase_date

FROM orders

GROUP BY customer_id

HAVING MIN(order_date) >= DATEADD(MONTH, -6, GETDATE());

Explanation: The MIN function identifies the earliest order date, filtered to the last 6 months.

2. How do you pivot a table to convert rows into columns?

SELECT *

FROM (SELECT employee_id, department, salary FROM employees) AS source_table

PIVOT (

    SUM(salary)

    FOR department IN ([HR], [Finance], [IT])

) AS pivot_table;

Explanation: Pivoting transforms rows into columns for easier analysis.

3. Write a query to calculate the percentage change in sales month-over-month.

WITH MonthlySales AS (

    SELECT DATEPART(MONTH, order_date) AS month, SUM(sales_amount) AS total_sales

    FROM sales

    GROUP BY DATEPART(MONTH, order_date)

)

SELECT month, 

       total_sales, 

       LAG(total_sales) OVER (ORDER BY month) AS previous_month_sales,

       ((total_sales - LAG(total_sales) OVER (ORDER BY month)) * 100.0 / LAG(total_sales) OVER (ORDER BY month)) AS percentage_change

FROM MonthlySales;

Explanation: The LAG function retrieves the sales of the previous month for percentage calculations.

4. Find the median salary of employees in a table.

SELECT AVG(salary) AS median_salary

FROM (

    SELECT salary, 

           NTILE(2) OVER (ORDER BY salary) AS tile

    FROM employees

) AS subquery

WHERE tile IN (1, 2);

Explanation: The NTILE function splits salaries into tiles for median computation.

5. Fetch all users who logged in consecutively for 3 days or more.

WITH ConsecutiveLogins AS (

    SELECT user_id, login_date,

           DATEDIFF(DAY, ROW_NUMBER() OVER (PARTITION BY user_id ORDER BY login_date), login_date) AS grp

    FROM logins

)

SELECT user_id

FROM ConsecutiveLogins

GROUP BY user_id, grp

HAVING COUNT(*) >= 3;

Explanation: The difference between row numbers and dates groups consecutive logins.

6. Identify products with the highest sales in each category.

SELECT category_id, product_id, MAX(sales_amount) AS max_sales

FROM sales

GROUP BY category_id, product_id;

Explanation: The MAX function finds the highest sales within each category.

7. Retrieve the top 3 earning employees in each department.

SELECT department_id, employee_id, salary

FROM (

    SELECT department_id, employee_id, salary,

           RANK() OVER (PARTITION BY department_id ORDER BY salary DESC) AS rank

    FROM employees

) ranked

WHERE rank <= 3;

Explanation: The RANK function helps in identifying the top earners.

8. Fetch all customers with no purchase history in the last year.

SELECT customer_id

FROM customers

WHERE customer_id NOT IN (

    SELECT DISTINCT customer_id

    FROM orders

    WHERE order_date >= DATEADD(YEAR, -1, GETDATE())

);

Explanation: Subqueries and filtering ensure only inactive customers are fetched.

9. Calculate the average order value for each customer.

SELECT customer_id, AVG(order_amount) AS average_order_value

FROM orders

GROUP BY customer_id;

Explanation: Grouping by customer allows calculation of their average order value.

Advanced Problem-Solving SQL Interview Questions

1. Rank products by sales in descending order for each region.

SELECT region, product_id, SUM(sales_amount) AS total_sales,

       RANK() OVER (PARTITION BY region ORDER BY SUM(sales_amount) DESC) AS rank

FROM sales

GROUP BY region, product_id;

Explanation: The RANK function assigns rankings within each region.

2. Fetch all employees whose salaries fall within the top 10% of their department.

WITH DepartmentSalaries AS (

    SELECT department_id, salary,

           PERCENT_RANK() OVER (PARTITION BY department_id ORDER BY salary DESC) AS rank

    FROM employees

)

SELECT *

FROM DepartmentSalaries

WHERE rank <= 0.1;

Explanation: PERCENT_RANK calculates the relative rank of each salary within the department.

3. Identify orders placed during business hours (e.g., 9 AM to 6 PM).

SELECT *

FROM orders

WHERE CAST(order_time AS TIME) BETWEEN '09:00:00' AND '18:00:00';

Explanation: Filtering on TIME ensures only business-hour orders are fetched.

4. Retrieve customers who made purchases across at least three different categories.

SELECT customer_id

FROM orders

GROUP BY customer_id

HAVING COUNT(DISTINCT category_id) >= 3;

Explanation: Counting distinct categories identifies diversified purchasing behavior.

5. Write a query to find gaps in sequential numbering within a table.

SELECT id + 1 AS missing_number

FROM table_name t1

WHERE NOT EXISTS (

    SELECT 1 FROM table_name t2 WHERE t2.id = t1.id + 1

);

Explanation: The query checks for missing IDs by ensuring id + 1 doesn’t exist.

6. Calculate the average order value for each customer.

SELECT customer_id, AVG(order_amount) AS average_order_value

FROM orders

GROUP BY customer_id;

Explanation: Grouping by customer allows calculation of their average order value.

7. Retrieve overlapping date ranges in a bookings table.

SELECT b1.booking_id, b2.booking_id

FROM bookings b1

JOIN bookings b2 ON b1.start_date < b2.end_date AND b1.end_date > b2.start_date

WHERE b1.booking_id <> b2.booking_id;

Explanation: A self-join identifies overlapping date ranges in bookings.

These questions and their solutions will prepare you for SQL interviews and help you stand out as a candidate. Bookmark this page and revisit it as you polish your SQL expertise!