Master every SQL Interview

People underestimate SQL Interview. Infact, SQL is the most important skill especially for a career in data.

SQL is the most fundamental skill you need to master. Infact, every data role needs SQL! Its a pre-requisite. And even in a world of AI , ask anyone that wants to get started and the first thing they are told to do is learning SQL and Python. Because those two tools help you to master any skills on top of that. And you may be very surprised. But even in a world filled with AI , SQL is still one of the most asked interview skills. And preparing for it is vital. I cannot count the number of times I have been asked SQL questions. Here are the roles where SQL is a necessity :

1. Data Analyst

2. Data Engineer

3. Data Science

4. Solutions Architect at a Data Company like AWS, Google Cloud, Databricks.

5. Business Intelligence Engineer

So, if you’re working on any of the above roles, chances of you being asked a SQL question are very high. Now, There are 2 types of SQL interview questions :

1. Theoretical questions

2. Practical solving SQL queries

Practically solving SQL queries is still asked in interviews like Amazon Business Intelligence interviews or any Data Engineering interviews. This requires solving coding questions on a shared screen in a limited time and running the queries. It also involves ensuring all the test cases run successfully. With the competition so high, it is a given that you solve all the questions successfully. Else, most times you won’t be able to clear the interview. For instance, in 1 hour interview, I was asked questions on multiple joins, rank, window functions , where , having , order by clauses.

Before sharing concept specific questions, also sharing some of the resources where you can practice these questions :

Leetcode 50

Advanced SQL 50 : Needs premium

Most asked SQL questions : Link

800+ SQL questions

1. Window Functions

Window functions allow calculations across rows relative to the current row, without grouping rows.

Examples:

• ROW_NUMBER(), RANK(), DENSE_RANK(), LAG(), LEAD()

Here are clear and structured SQL practice questions that you can directly use to practice each window function (ROW_NUMBER(), RANK(), DENSE_RANK(), LAG(), LEAD()). These questions include realistic table and column names and are designed similarly to medium-level LeetCode or HackerRank challenges:

---

ROW_NUMBER() Questions:

Question 1: Top 3 Employees per Department

Given a table Employees with columns:

• EmployeeID

• EmployeeName

• Salary

• DepartmentID

Write a query to find the top 3 highest-paid employees in each department.
Output columns: DepartmentID, EmployeeName, Salary, and EmployeeRank.

---

Question 2: Latest Order per Customer

Given a table Orders with columns:

• OrderID

• CustomerID

• OrderDate

• Amount

Write a query to find the latest order (by date) for each customer.
Output columns: CustomerID, OrderID, OrderDate, Amount.

---

RANK() Questions:

Question 1: Rank Students by Exam Scores

Given a table Students with columns:

• StudentID

• StudentName

• ExamScore

Write a query to rank students based on their exam scores. Students with equal scores should have the same rank, and ranks should skip numbers after ties (1,1,3...).
Output columns: StudentID, StudentName, ExamScore, Rank.

---

Question 2: Product Sales Rank

Given a table Products with columns:

• ProductID

• ProductName

• Category

• TotalSales

Write a query to rank products within each category based on their total sales, handling ties appropriately.
Output columns: Category, ProductName, TotalSales, SalesRank.

---

DENSE_RANK() Questions:

Question 1: Movie Ratings

Given a table Movies with columns:

• MovieID

• MovieName

• Rating

Write a query to assign ranks to movies based on their ratings. Movies with identical ratings must share the same rank without gaps in the ranking (1,1,2...).
Output columns: MovieID, MovieName, Rating, Rank.

---

Question 2: Customer Spending Rank

Given a table Transactions with columns:

• TransactionID

• CustomerID

• SpendAmount

Write a query to rank customers based on total spending, ensuring customers with equal spend have the same rank (dense ranking).
Output columns: CustomerID, TotalSpend, Rank.

---

LAG() Questions:

Question 1: Monthly Revenue Comparison

Given a table MonthlyRevenue with columns:

• Month (e.g., '2024-01', '2024-02', ...)

• Revenue

Write a query to show each month’s revenue alongside the previous month's revenue.
Output columns: Month, Revenue, PreviousMonthRevenue.

---

Question 2: Employee Salary Changes

Given a table EmployeeSalaries with columns:

• EmployeeID

• Salary

• EffectiveDate

Write a query to list each employee’s salary and the salary they had previously (chronologically).
Output columns: EmployeeID, EffectiveDate, Salary, PreviousSalary.

---

LEAD() Questions:

Question 1: Next Flight Schedule

Given a table Flights with columns:

• FlightID

• Airline

• DepartureTime

Write a query to list each flight along with the next flight’s departure time for the same airline.
Output columns: FlightID, Airline, DepartureTime, NextFlightDepartureTime.

---

Question 2: Stock Price Next-Day Comparison

Given a table Stocks with columns:

• StockID

• StockDate

• ClosingPrice

Write a query to show the closing price of each stock alongside the next day's closing price.
Output columns: StockID, StockDate, ClosingPrice, NextDayClosingPrice.

---

Use these structured examples to practice each function, allowing you to master complex SQL concepts effectively.

2. Partitioning (PARTITION BY)

Partitioning splits the data into subsets for analysis within window functions.

Question 1: Rank Employees by Salary Within Departments

Table: Employees
Columns: EmployeeID, DepartmentID, EmployeeName, Salary

Task:
Assign a rank to each employee within their department based on their salary, with the highest paid ranked first.

Expected Output:
DepartmentID, EmployeeName, Salary, RankInDepartment

Question 2 : Calculate Average Order Value per Customer Over Time

Table: Orders
Columns: OrderID, CustomerID, OrderDate, Amount

Task:
Calculate a rolling average order amount per customer, ordered by date.

Expected Output:
CustomerID, OrderDate, Amount, AverageToDate

Question 3: Find Customers with Increasing Spend Over Time

Table: Transactions
Columns: TransactionID, CustomerID, TransactionDate, Amount

Task:
For each customer, compare the amount spent in each transaction to their previous transaction. Show only rows where spending increased.

Expected Output:
CustomerID, TransactionDate, Amount, PreviousAmount, Increased (Yes/No)

Question 4 : Flag First Purchase per Customer

Table: Purchases
Columns: PurchaseID, CustomerID, PurchaseDate, Item

Task:
For the purchases table, flag the first purchase made by each customer chronologically.

Expected Output:
CustomerID, PurchaseDate, Item, IsFirstPurchase (Yes/No)

3. Common Table Expressions (CTE)

A CTE is a temporary named query used within another query.

Question 1: Top 3 Customers per Month

Tables: Orders(OrderID, CustomerID, OrderDate, Amount)
Create a CTE to calculate total monthly spend per customer. Then use ROW_NUMBER() in the CTE to return the top 3 spending customers per month

Question 2 : Sales Over Threshold

Table: Sales(SaleID, SaleDate, Region, Amount)
Create a CTE to calculate average daily sales per region, and return the days where the amount exceeded 150% of the region's daily average.

Question 3: Second Highest Transaction per Customer

Table: Transactions(TransactionID, CustomerID, Amount)
Use a CTE and ROW_NUMBER() to return each customer’s second highest transaction amount.

Question 4 : Filtering with CTEs

Table: Employees(EmployeeID, DepartmentID, Salary)
Create a CTE to get employees with above-average salaries per department. Then return only those earning more than twice the average in their department.

4. Joins

Joins combine rows from two or more tables based on related columns.

Types:

• INNER JOIN, LEFT JOIN, RIGHT JOIN, FULL JOIN, CROSS JOIN

Question 1: Customers Without Orders

Tables: Customers(CustomerID, Name), Orders(OrderID, CustomerID, OrderDate)
Use a LEFT JOIN to find customers who never placed an order.

---

Question 2: Employee and Manager Names

Table: Employees(EmployeeID, Name, ManagerID)
Join the Employees table to itself to get each employee along with their manager’s name.

---

Question 3: Products Not Sold

Tables: Products(ProductID, Name), OrderDetails(OrderID, ProductID)
Find all products that were never sold using a LEFT JOIN and IS NULL.

---

Question 4: Highest Sale per Region

Tables: Sales(SaleID, Region, Amount), Regions(RegionID, RegionName)
Join both tables and return the highest sale amount per region.

---

Question 5: Total Revenue per Category

Tables: Products(ProductID, CategoryID), Categories(CategoryID, Name), Sales(ProductID, Quantity, Price)
Join and calculate total revenue per product category.

---

Question 6: Students and Courses Enrolled

Tables: Students(StudentID, Name), Enrollments(StudentID, CourseID), Courses(CourseID, Title)
Return each student’s name and the list of courses they are enrolled in.

---

Question 7: Orders with Customer & Product Info

Tables: Orders(OrderID, CustomerID, OrderDate), OrderItems(OrderID, ProductID, Quantity), Products(ProductID, Name, Price)
Join all tables to get complete details for each order including customer and product names and total cost.

5. Subqueries

Subqueries are nested queries within another SQL statement.

Types :

• IN, EXISTS

Question 1: Customers With Above-Average Spend

Table: Orders(CustomerID, Amount)
Find all customers whose total spend is above the average total spend of all customers.

---

Question 2: Second Highest Salary

Table: Employees(EmployeeID, Salary)
Write a query to return the second highest distinct salary using a subquery.

---

Question 3: Products with No Orders

Tables: Products(ProductID), OrderItems(ProductID)
Use a subquery to return products that do not appear in any orders.

---

Question 4: Highest Paid Employee per Department

Table: Employees(EmployeeID, DepartmentID, Salary)
Use a correlated subquery to return employees earning the highest salary within their department.

---

Question 5: Customers With More Than One Order

Table: Orders(CustomerID)
Use a subquery to return customers who placed more than one order.

---

Question 6: Products More Expensive Than Average

Table: Products(ProductID, Price)
Return all products that cost more than the average price of all products.

---

Question 7: Employees With Salaries in Top 10%

Table: Employees(EmployeeID, Salary)
Use a subquery to find employees whose salary is in the top 10% of all salaries.

---

Question 8: Customers Who Ordered All Products

Tables: Customers, Orders, Products
Return customers who have ordered every product at least once. Requires NOT EXISTS and set logic.

---

Question 9: Cities With No Orders

Tables: Customers(City), Orders(CustomerID)
Find cities where no customers have placed an order.

---

Question 10: Departments With Only One Employee

Table: Employees(EmployeeID, DepartmentID)
Use a subquery to return departments that have only one employee.

6. Aggregations and Grouping

Aggregation groups data to calculate metrics such as sum, average, min, max, count.

Question 1: Total Revenue Per Product

Table: OrderItems(ProductID, Quantity, Price)
Group by ProductID and calculate total revenue as Quantity * Price.

---

Question 2: Average Order Value per Customer

Table: Orders(CustomerID, OrderID, Amount)
Group by CustomerID and return their average order value.

---

Question 3: Number of Orders per Month

Table: Orders(OrderDate)
Group orders by month and count them.

---

Question 4: Highest-Spending Customer

Table: Orders(CustomerID, Amount)
Use GROUP BY and ORDER BY to find the customer who spent the most.

---

Question 5: Product Count per Category

Table: Products(ProductID, CategoryID)
Group by CategoryID and count the number of products in each.

---

Question 6: Orders Per Customer Per Year

Table: Orders(CustomerID, OrderDate)
Group by CustomerID and YEAR(OrderDate) to get order counts per year.

---

Question 7: Daily Average Sales by Store

Table: Sales(StoreID, SaleDate, Amount)
Group by StoreID and SaleDate and get average daily sales.

---

Question 8: Most Popular Product Each Month

Tables: Orders(OrderID, OrderDate), OrderItems(OrderID, ProductID)
Group by month and ProductID, and return the product with the highest order count per month.

---

Question 9: Customers with Highest Order Frequency

Table: Orders(CustomerID, OrderDate)
Group by CustomerID and count the number of orders. Return the top 5.

---

Question 10: Categories with No Sales

Tables: Products(ProductID, CategoryID), OrderItems(ProductID)
Group products and orders to identify categories that had zero sales.

MOST ASKED SQL THEORETICAL QUESTIONS

What types of joins do you know?

• (INNER) JOIN – returns only those records that satisfy a defined join condition in both (or all) tables. It's a default SQL join.

• LEFT (OUTER) JOIN – returns all records from the left table and those records from the right table that satisfy a defined join condition.

• RIGHT (OUTER) JOIN – returns all records from the right table and those records from the left table that satisfy a defined join condition.

• FULL (OUTER) JOIN – returns all records from both (or all) tables. It can be considered as a combination of left and right joins.

What is a primary key?

A column (or multiple columns) of a table to which the PRIMARY KEY constraint was imposed to ensure unique and non-null values in that column. In other words, a primary key is a combination of the NOT NULL and UNIQUE constraints. The primary key uniquely identifies each record of the table. Each table should contain a primary key and can't contain more than one primary key.

What is a foreign key?

A column (or multiple columns) of a table to which the FOREIGN KEY constraint was imposed to link this column to the primary key in another table (or several tables). The purpose of foreign keys is to keep connected various tables of a database.

What aggregate functions do you know?

• AVG() – returns the average value

• SUM() – returns the sum of values

• MIN() – returns the minimum value

• MAX() – returns the maximum value

• COUNT() – returns the number of rows, including those with null values

• FIRST() – returns the first value from a column

• LAST()– returns the last value from a column

What is the difference between LEFT JOIN and LEFT OUTER JOIN?

There is no difference between LEFT JOIN and LEFT OUTER JOIN. They are interchangeable. SQL allows the OUTER keyword to be optional, so LEFT JOIN is simply a shorthand for LEFT OUTER JOIN. Both return all records from the left table and the matching records from the right table.

What is normalization in SQL, and why use it?

Normalization is a process of database design that includes organizing and restructuring data in a way to reduce data redundancy, dependency, duplication, and inconsistency. This leads to enhanced data integrity, more tables within the database, more efficient data access and security control, and greater query flexibility.

Some other resources for top 10 question : Link

Credits :

Datacamp : Link

SimpilLearn : Link