SQL Interview Questions & Answers

Q: Fetch all orders where the total_value is greater than the average order value.?

Input Table: Orders order_id customer_id total_value 901 1 5000 902 2 7000 903 3 6000 904 4 8000 I use a subquery to calculate the average order value using AVG() . Then I use the result in the outer query to return orders whose total value is greater than that average. ``` sql -- ============

Q: Find the most frequently sold product in each category.?

Products product_id category product_name 601 A Apple 602 A Banana 603 B Milk 604 B Bread Sales sale_id product_id quantity 1 601 10 2 602 5 3 601 8 4 603 12 5 604 4 Then we can calculate total quantity sold: Apple → 10 + 8 = 18 Banana → 5 Milk → 12 Bread → 4 Therefore: Category A → Apple Category B

Q: Find customers who have never placed an order.?

The key concept here is a LEFT JOIN with IS NULL . 1. Customers Table customer_id customer_name 1 John 2 Alice 3 Bob 2. Orders Table order_id customer_id 101 1 102 2 I use a LEFT JOIN between Customers and Orders on customer_id. The LEFT JOIN keeps all customers, including those without orders. Then

Q: Calculate the average sales amount for each region.

Input Table: Sales sales_id region sales_amount 501 North 50000 502 South 70000 503 North 80000 504 West 90000 I group the sales records by region using GROUP BY , and then use the AVG() aggregate function to calculate the average sales amount for each region. ``` CREATE TABLE Sales ( &

Q: Write a SQL query to fetch all orders that were placed on weekends (Saturday or Sunday).?

Input table: Orders order_id customer_id order_date 401 1 2024-02-17 402 2 2024-02-18 403 3 2024-02-19 404 1 2024-02-24 Let's identify the days: order_id order_date Day Weekend? 401 2024-02-17 Saturday ✅ 402 2024-02-18 Sunday ✅ 403 2024-02-19 Monday ❌ 404 2024-02-24 Saturday ✅ I use the DAYOFWEEK()

Q: Write a query to retrieve the first and last purchase date for each customer.?

Input Table: Transactions transaction_id customer_id purchase_date 301 1 2024-01-10 302 2 2024-01-12 303 1 2024-02-15 304 2 2024-02-20 305 1 2024-03-05 ``` sql CREATE TABLE Transactions ( transaction_id INT, customer_id INT, purchase_date DATE ); INSERT IN

Q: Find customers who have placed more than 5 orders in the last 30 days.?

Input Table: Orders order_id customer_id order_date 201 1 2024-02-15 202 2 2024-02-10 203 1 2024-02-18 204 3 2024-02-20 205 1 2024-02-25 206 2 2024-02-27 207 1 2024-03-02 ``` sql CREATE TABLE Orders ( order_id INT, customer_id INT, order_date DATE ); INSERT

Q: Write a query to fetch the top 3 employees with the highest salaries from an Employees table.?(Asked in Accenture)

Fetch the Top 3 Employees with the Highest Salaries Input Table: Employees emp_id emp_name salary 101 Alice 95000 102 Bob 87000 103 Charlie 91000 104 David 75000 105 Eve 99000 ``` sql CREATE TABLE Employees ( emp_id INT, emp_name VARCHAR(100), salary DECIMAL

Vishal Taneja·

3w agoInterview Question

Q.1

Fetch all orders where the total_value is greater than the average order value.? Medium

Input Table: `Orders`

order_id	customer_id	total_value
901	1	5000
902	2	7000
903	3	6000
904	4	8000

I use a subquery to calculate the average order value using AVG(). Then I use the result in the outer query to return orders whose total value is greater than that average.

-- =========================================
-- QUESTION 10
-- Find Orders Where Total Order Value
-- Exceeds the Average Order Value
-- =========================================
 
-- =========================================
-- 1. Remove Existing Table
-- =========================================
 
DROP TABLE IF EXISTS Orders;
 
-- =========================================
-- 2. Create Orders Table
-- =========================================
 
CREATE TABLE Orders (
    order_id INT,
    customer_id INT,
    total_value DECIMAL(10, 2)
);
 
-- =========================================
-- 3. Insert Orders
-- =========================================
 
INSERT INTO Orders (
    order_id,
    customer_id,
    total_value
)
VALUES
    (901, 1, 5000),
    (902, 2, 7000),
    (903, 3, 6000),
    (904, 4, 8000);
 
-- =========================================
-- 4. Select All Orders
-- =========================================
 
SELECT *
FROM Orders;
 
-- =========================================
-- 5. Calculate Average Order Value
-- =========================================
 
SELECT
    AVG(total_value) AS average_order_value
FROM Orders;
 
-- =========================================
-- 6. Find Orders Above Average
-- =========================================
 
SELECT
    order_id,
    customer_id,
    total_value
FROM Orders
WHERE total_value &gt; (
    SELECT AVG(total_value)
    FROM Orders
);
▶ Run to see output

Vishal Taneja·

3w agoInterview Question

Q.2

Find the most frequently sold product in each category.? Medium

Products

product_id	category	product_name
601	A	Apple
602	A	Banana
603	B	Milk
604	B	Bread

Sales

sale_id	product_id	quantity
1	601	10
2	602	5
3	601	8
4	603	12
5	604	4

Then we can calculate total quantity sold:


Apple  → 10 + 8 = 18
Banana → 5
Milk   → 12
Bread  → 4

Therefore:


Category A → Apple
Category B → Milk

SQL concept

The solution normally involves:


Products
   ↓
JOIN Sales
   ↓
GROUP BY category + product
   ↓
SUM(quantity) / COUNT(*)
   ↓
Rank products within each category
   ↓
Take rank = 1

-- =========================================
-- QUESTION 8
-- Find the Most Frequently Sold Product
-- in Each Category
-- =========================================
 
-- =========================================
-- 1. Remove old tables if they exist
-- =========================================
 
DROP TABLE IF EXISTS ProductSales;
DROP TABLE IF EXISTS Products;
 
-- =========================================
-- 2. Create Products Table
-- =========================================
 
CREATE TABLE Products (
    product_id INT,
    category VARCHAR(50),
    product_name VARCHAR(100)
);
 
-- =========================================
-- 3. Insert Products
-- =========================================
 
INSERT INTO Products (
    product_id,
    category,
    product_name
)
VALUES
    (601, 'A', 'Apple'),
    (602, 'A', 'Banana'),
    (603, 'A', 'Mango'),
    (604, 'B', 'Milk'),
    (605, 'B', 'Bread'),
    (606, 'B', 'Butter');
 
-- =========================================
-- 4. Create ProductSales Table
-- =========================================
 
CREATE TABLE ProductSales (
    sale_id INT,
    product_id INT
);
 
-- =========================================
-- 5. Insert Sales
-- =========================================
 
INSERT INTO ProductSales (
    sale_id,
    product_id
)
VALUES
    (1, 601),
    (2, 601),
    (3, 601),
    (4, 602),
    (5, 602),
    (6, 603),
    (7, 604),
    (8, 604),
    (9, 604),
    (10, 604),
    (11, 605),
    (12, 605),
    (13, 606);
 
-- =========================================
-- 6. Check Products
-- =========================================
 
SELECT *
FROM Products;
 
-- =========================================
-- 7. Check Product Sales
-- =========================================
 
SELECT *
FROM ProductSales;
 
-- =========================================
-- 8. Count How Many Times Each Product
--    Was Sold
-- =========================================
 
SELECT
    p.category,
    p.product_id,
    p.product_name,
    COUNT(ps.sale_id) AS times_sold
FROM Products p
JOIN ProductSales ps
    ON p.product_id = ps.product_id
GROUP BY
    p.category,
    p.product_id,
    p.product_name
ORDER BY
    p.category,
    times_sold DESC;
 
-- =========================================
-- 9. Find Most Frequently Sold Product
--    in Each Category
-- =========================================
 
WITH product_sales AS (
    SELECT
        p.category,
        p.product_id,
        p.product_name,
        COUNT(ps.sale_id) AS times_sold
    FROM Products p
    JOIN ProductSales ps
        ON p.product_id = ps.product_id
    GROUP BY
        p.category,
        p.product_id,
        p.product_name
),
 
ranked_products AS (
    SELECT
        category,
        product_id,
        product_name,
        times_sold,
        ROW_NUMBER() OVER (
            PARTITION BY category
            ORDER BY times_sold DESC
        ) AS rn
    FROM product_sales
)
 
SELECT
    category,
    product_id,
    product_name,
    times_sold
FROM ranked_products
WHERE rn = 1
ORDER BY category;
▶ Run to see output

pyspark·

3w agoInterview Question

Q.3

Find customers who have never placed an order.? Medium

The key concept here is a LEFT JOIN with IS NULL.

1. Customers Table

customer_id	customer_name
1	John
2	Alice
3	Bob

2. Orders Table

order_id	customer_id
101	1
102	2

I use a LEFT JOIN between Customers and Orders on customer_id. The LEFT JOIN keeps all customers, including those without orders. Then I filter where the Orders customer_id is NULL to identify customers who have never placed an order.

CREATE TABLE Customers (
    customer_id INT,
    customer_name VARCHAR(100)
);
INSERT INTO Customers (customer_id, customer_name)
VALUES
    (1, 'John'),
    (2, 'Alice'),
 
    (3, 'Bob');
CREATE TABLE Orders (
    order_id INT,
    customer_id INT
 
);
INSERT INTO Orders (order_id, customer_id)
VALUES
    (101, 1),
 
    (102, 2);
 
SELECT
    c.customer_id,
    c.customer_name
FROM Customers c
LEFT JOIN Orders o
    ON c.customer_id = o.customer_id
 
WHERE o.customer_id IS NULL;
▶ Run to see output

pyspark·

3w agoInterview Question

Q.4

Find the second-highest salary from the Employees table without using LIMIT or TOP. Medium

Example Employees Table

Suppose we have:

emp_id	emp_name	salary
101	Alice	95000
102	Bob	87000
103	Charlie	91000
104	David	75000
105	Eve	99000

I use a subquery to find the maximum salary, filter out that salary using the WHERE clause, and then use MAX() on the remaining salaries to get the second-highest salary.

CREATE TABLE Employees (
    emp_id INT,
    emp_name VARCHAR(100),
    salary DECIMAL(10, 2)
);
INSERT INTO Employees (emp_id, emp_name, salary)
VALUES
    (101, 'Alice', 95000),
    (102, 'Bob', 87000),
    (103, 'Charlie', 91000),
    (104, 'David', 75000),
 
    (105, 'Eve', 99000);
 
SELECT MAX(salary) AS second_highest_salary
FROM Employees
WHERE salary &lt; (
    SELECT MAX(salary)
    FROM Employees
 
);
▶ Run to see output

pyspark·

3w agoInterview Question

Q.5

Calculate the average sales amount for each region. Medium

Input Table: `Sales`

sales_id	region	sales_amount
501	North	50000
502	South	70000
503	North	80000
504	West	90000

I group the sales records by region using GROUP BY, and then use the AVG() aggregate function to calculate the average sales amount for each region.

CREATE TABLE Sales (
    sales_id INT,
    region VARCHAR(50),
    sales_amount DECIMAL(10, 2)
);
INSERT INTO Sales (sales_id, region, sales_amount)
VALUES
    (501, 'North', 50000),
    (502, 'South', 70000),
    (503, 'North', 80000),
 
    (504, 'West', 90000);
 
SELECT
    region,
    AVG(sales_amount) AS avg_sales
FROM Sales
 
GROUP BY region;
▶ Run to see output

pyspark·

3w agoInterview Question

Q.6

Write a SQL query to fetch all orders that were placed on weekends (Saturday or Sunday).? Medium

Input table: `Orders`

order_id	customer_id	order_date
401	1	2024-02-17
402	2	2024-02-18
403	3	2024-02-19
404	1	2024-02-24

Let's identify the days:

order_id	order_date	Day	Weekend?
401	2024-02-17	Saturday	✅
402	2024-02-18	Sunday	✅
403	2024-02-19	Monday	❌
404	2024-02-24	Saturday	✅

I use the DAYOFWEEK() function to determine the weekday for each order date, and then filter for Sunday and Saturday using IN (1, 7).

CREATE TABLE Orders (
    order_id INT,
    customer_id INT,
    order_date DATE
);
INSERT INTO Orders (order_id, customer_id, order_date)
VALUES
    (401, 1, '2024-02-17'),
    (402, 2, '2024-02-18'),
    (403, 3, '2024-02-19'),
 
    (404, 1, '2024-02-24');
 
SELECT
    order_id,
    customer_id,
    order_date
FROM Orders
WHERE DAYOFWEEK(order_date) IN (1, 7);
▶ Run to see output

pyspark·

3w agoInterview Question

Q.7

Write a query to retrieve the first and last purchase date for each customer.? Medium

Input Table: Transactions

transaction_id	customer_id	purchase_date
301	1	2024-01-10
302	2	2024-01-12
303	1	2024-02-15
304	2	2024-02-20
305	1	2024-03-05

CREATE TABLE Transactions (
    transaction_id INT,
    customer_id INT,
    purchase_date DATE
);
INSERT INTO Transactions
    (transaction_id, customer_id, purchase_date)
VALUES
    (301, 1, '2024-01-10'),
    (302, 2, '2024-01-12'),
    (303, 1, '2024-02-15'),
    (304, 2, '2024-02-20'),
 
    (305, 1, '2024-03-05');
 
SELECT
    customer_id,
    MIN(purchase_date) AS first_purchase,
    MAX(purchase_date) AS last_purchase
FROM Transactions
 
GROUP BY customer_id;
▶ Run to see output

pyspark·

3w agoInterview Question

Q.8

Find customers who have placed more than 5 orders in the last 30 days.? Medium

Input Table: `Orders`

order_id	customer_id	order_date
201	1	2024-02-15
202	2	2024-02-10
203	1	2024-02-18
204	3	2024-02-20
205	1	2024-02-25
206	2	2024-02-27
207	1	2024-03-02

CREATE TABLE Orders (
    order_id INT,
    customer_id INT,
    order_date DATE
);
INSERT INTO Orders (order_id, customer_id, order_date)
VALUES
    (201, 1, '2024-02-15'),
    (202, 2, '2024-02-10'),
    (203, 1, '2024-02-18'),
    (204, 3, '2024-02-20'),
    (205, 1, '2024-02-25'),
    (206, 2, '2024-02-27'),
 
    (207, 1, '2024-03-02');
 
SELECT customer_id,
       COUNT(order_id) AS order_count
FROM Orders
WHERE order_date &gt;= CURRENT_DATE - INTERVAL 30 DAY
GROUP BY customer_id
HAVING COUNT(order_id) &gt; 5;
▶ Run to see output

pyspark·

3w agoInterview Question

Q.9

Write a query to fetch the top 3 employees with the highest salaries from an Employees table.?(Asked in Accenture) Easy

Fetch the Top 3 Employees with the Highest Salaries

Input Table: Employees

emp_id	emp_name	salary
101	Alice	95000
102	Bob	87000
103	Charlie	91000
104	David	75000
105	Eve	99000

CREATE TABLE Employees (
    emp_id INT,
    emp_name VARCHAR(100),
    salary DECIMAL(10, 2)
);
INSERT INTO Employees (emp_id, emp_name, salary)
VALUES
    (101, 'Alice', 95000),
    (102, 'Bob', 87000),
    (103, 'Charlie', 91000),
    (104, 'David', 75000),
 
    (105, 'Eve', 99000);
SELECT emp_id, emp_name, salary
 
FROM Employees;
▶ Run to see output

Vishal Taneja·

Sep 14, 2026Interview Question

Q.10

A SQL query that normally takes 2 minutes is now taking 20 minutes. What will you do? Medium

1. Check the execution plan

First, I would check the query's execution plan.

The execution plan tells us how the database is actually executing the query.

For example:


Query
  ↓
Execution Plan
  ↓
Table Scan?
Index Scan?
Join?
Sort?
Aggregation?

I want to find the expensive operation.

2. Compare old vs new execution plan

This is very important.

The query used to take:


2 minutes ✅

Now:


20 minutes ❌

So I would compare:


Previous execution plan
          VS
Current execution plan

Maybe previously:


Index Scan
    ↓
100K rows

Now:


Full Table Scan
    ↓
100 Million rows

That immediately gives us a direction.

3. Check whether data volume increased

Maybe the query itself didn't change.

The data may simply have grown.

For example:


Last month:
10 million rows
     ↓
2 minutes

Now:


100 million rows
     ↓
20 minutes

So I would check:

Table size
Number of rows
Recent data growth
Historical data volume

4. Look for full table scans

Suppose the query is:


SELECT *
FROM transactions
WHERE customer_id = 1001;

If the database scans the entire table:


100 million rows
       ↓
Scan everything
       ↓
Find customer 1001

that's potentially expensive.

An appropriate index or partitioning strategy might allow the database to access only the required data.

5. Check joins and join cardinality

This is another common reason for slow queries.

Suppose:


transactions
      JOIN
customers

You should check:

Join keys
Number of rows on each side
Whether the join unexpectedly produces many rows
Whether the join condition is correct
Whether a many-to-many join was accidentally created

For example:


Table A = 1 million rows
Table B = 1 million rows

An incorrect join can produce a huge intermediate result.


1M × 1M
    ↓
Very large result
    ↓
Query becomes slow

6. Check indexes or partition pruning

For traditional relational databases, check whether the expected indexes are being used.

For large data-lake systems such as Spark/Databricks, think about partition pruning and file/data skipping rather than traditional database indexes.

For example:


Transactions
├── 2026-09-10
├── 2026-09-11
├── 2026-09-12
├── 2026-09-13
└── 2026-09-14

If the query asks:


WHERE transaction_date = '2026-09-14'

we ideally want:


Read only Sept 14
       ↓
Avoid historical data

This is partition pruning.

7. Check functions on indexed columns

Consider:


WHERE YEAR(transaction_date) = 2026

Depending on the database and indexing strategy, applying a function to the column can prevent efficient use of an index.

Often, a range predicate is better:


WHERE transaction_date >= '2026-01-01'
  AND transaction_date < '2027-01-01'

The exact behavior depends on the database optimizer, but this is a useful thing to investigate.

8. Check unnecessary columns

Suppose someone uses:


SELECT *
FROM transactions;

when they only need:


SELECT transaction_id, customer_id, amount
FROM transactions;

Selecting unnecessary columns can increase:


I/O
 ↓
Memory
 ↓
Network transfer
 ↓
Processing time

So I would select only the required columns.

9. Check statistics

Database optimizers use statistics to choose execution plans.

For example:


Old statistics
      ↓
Good execution plan
      ↓
2 minutes

But if statistics become stale:


Stale statistics
      ↓
Bad cardinality estimate
      ↓
Bad execution plan
      ↓
20 minutes

So I would check whether statistics need to be refreshed.

10. Check recent deployments

Because the query suddenly became slow, I would ask:

“Was there any recent code or database change?”

Check:

SQL changes
New joins
New filters
Removed filters
Schema changes
Index changes
Partition changes
Configuration changes

For example:


Before deployment
      ↓
2 minutes ✅

Deployment
      ↓
Query changed
      ↓
20 minutes ❌

This can quickly identify the root cause.

11. For Spark / Databricks

Since you're preparing for Data Engineer interviews, this is especially important.

For Spark, I would additionally check:


Spark UI
   ↓
Stages
   ↓
Tasks
   ↓
Shuffle
   ↓
Partition sizes

Look for:

Large shuffle


Data
 ↓
Shuffle
 ↓
Huge amount of data
 ↓
Slow

Data skew


Partition 1 → 500 MB
Partition 2 → 400 MB
Partition 3 → 450 MB
Partition 4 → 40 GB ❌

One task takes much longer than the others.

Too many small files


Millions of tiny files
        ↓
Large file-listing/metadata overhead
        ↓
Slow job

Missing partition pruning


Query needs 1 day
      ↓
Scans 5 years ❌

I wouldn't immediately rewrite the query. First, I would investigate systematically. I would check the current execution plan and compare it with the previous plan to identify what changed. Then I would check data volume, full table scans, joins and join cardinality, indexes or partition pruning, functions applied to filter columns, unnecessary columns, and database statistics. I would also check whether a recent deployment or schema change affected the query. If this is a Spark/Databricks workload, I would additionally check Spark UI for expensive stages, shuffle, data skew, partition sizes and small-file issues. Once I identify the bottleneck, I would apply the appropriate optimization and validate the improvement.