Veeraj's Substack Master every SQL Interview People underestimate SQL Interview. Infact, SQL is the most important skill especially for a career in data. \VEERAJ KANTILAL GADDA MAY 05, 2025 O92 DOD G4 Share 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 Al, 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Intelligence interviews or any Data Engineering interviews. This requires solving coding questions on a shared screen ina 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, | 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 5 Advanced SQL 50: Needs premium Most asked SQL questions : Link 800+ SOL 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: Department ID, 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: e StudentID ¢ StudentName © FxamScore 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: Student ID, 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', e 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 © EttectiveDate 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. Ourput columns: F1ightTD, Airline, DepartureTime, NextF1ightDepartureTime. 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.. 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, Department ID, EmployeeName, Salary Task: Assign a rank to each employee wi in their department based on their salary, with the highest paid ranked first. Expected Output: Department ID, EmployeeName, Salary, RankInDepartment Question 2 : Calculate Average Order Value per Customer Over Time Table: Orders Columns: Order ID, 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 : e IN, EXISTS Question 1: Customers With Above-Average Spend Table: Order's(CuslomerID, 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 (Product ID), 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, Department ID) 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, cc Question 1: Total Revenue Per Product Table: OrderItems(ProductID, Quantity, Price) Group by Product ID 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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.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? Acolumn (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? Acolumn (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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 : SimpilLearn : LinkTop 20 Most Asked SQL Interview Questions for Data Science/Analytics Role Q ui © Here are list of mostly asked SQL interview Questions with Answer. 1. Explain the difference between SQL and NoSQL. + SQL (Structured Query Language): Designed for relational databases, excels at handling structured data with predefined schemas, strong data integrity, and ACID properties. * NoSQL (Not Only SQL): For unstructured, semi-structured, and highly scalable data. Offers flexibility in schema design, high scalability, and horizontal scaling. Examples: MongoDB, Cassandra, Redis. 2. What are the different types of joins in SQL? Inner Join: Returns rows where there’s a match in both tables. Left Join: Returns all rows from the left table and matching rows from the right table. Right Join: Returns all rows from the right table and matching rows from the left table.1 Full Join: Returns all rows2 from both tables, whether or not there's a match.3. What is the difference between WHERE and HAVING clauses? + WHERE: Filters rows before the grouping operation (e.g., GROUP BY ). + HAVING: Filters rows after the grouping operation, typically used with aggregate functions (e.g., SUM, AVG, COUNT). 4. What are primary keys and foreign keys? ¢ Primary Key: Uniquely identifies each row in a table. Must be unique and not null. + Foreign Key: A column in one table that references the primary key of another table, creating a relationship between the tables. 5. Explain the concept of normalization. The process of organizing data within database tables to minimize redundancy and improve data integrity. It involves breaking down a single table into multiple tables, often following normal forms (1NF, 2NF, 3NF). 6. What are aggregate functions in SQL? Functions that perform calculations on a set of values and return a single value. Common examples: * SUM: Calculates the sum of a column. * AVG: Calculates the average of a column. * COUNT: Counts the number of rows. * MIN: Finds the minimum value in a column. * MAX: Finds the maximum value in a column.7. Whatis a subquery? A query nested within another query. It can be used to: + Filter data based on conditions in another query. + Retrieve data that meets specific criteria within a larger query. 8. How do you write a SQL query to find the second highest salary? SQL SELECT Salary FROM Employee ORDER BY Salary DESC LIMIT 1 OFFSET 1; 9. What are indexes in SQL and why are they important? Data structures that improve the speed of data retrieval by creating a sorted order for specific columns. They are crucial for optimizing query performance, especially on large tables. 10. What is a view in SQL? A virtual table that is created from the result of a SQL query. It provides a simplified way to access complex queries or frequently used data.11. Explain the difference between DELETE and TRUNCATE commands. + DELETE: Removes specific rows from a table. It’s a DML (Data Manipulation Language) statement, and you can usually roll it back. * TRUNCATE: Removes all rows from a table. It’s a DDL (Data Definition Language) statement, and it’s generally faster but cannot be rolled back easily. 12. What is a transaction in SQL? A sequence of operations that are treated as a single unit. If any operation within a transaction fails, the entire transaction is rolled back, ensuring data consistency. 13. How do you handle missing values in SQL? * Use the IS NULL or IS NOT NULL operator to identify and filter rows with null values. + Use the COALESCE or NVL function to replace null values with a default value. 14. How do you write a SQL query to find the number of orders placed in each month? SQL SELECT MONTH(OrderDate) AS Month, COUNT(*) AS OrderCount FROM Orders GROUP BY MONTH (OrderDate) ;15. What are window functions in SQL? Functions that perform calculations across a set of rows related to the current row. Examples: + RANK: Assigns a rank to each row within a partition. + ROW_NUMBER: Assigns a unique number to each row within a partition. + LAG/LEAD: Accesses data from preceding or succeeding rows. 16. How do you optimize SQL queries? + Use indexes effectively. + *Avoid using SELECT ; instead, specify the required columns. + Minimize the use of subqueries. * Use appropriate join types. * Analyze query plans using tools like EXPLAIN. 17. What is a database trigger? A special type of stored procedure that automatically executes when an event occurs on a specific table (e.g., insert, update, delete). 18. What are temporary tables in SQL? Tables that exist only for the duration of a session or a specific transaction. They are useful for storing intermediate results or temporary data.19. How do you write a SQL query to find the customers who have placed more than 10 orders? SQL SELECT CustomerID FROM Orders GROUP BY CustomerID HAVING COUNT(*) > 10; 20. What are some of the common SQL interview questions related to data science/analytics? * How would you use SQL to analyze customer behavior? * How would you use SQL to identify trends and patterns in data? ¢ How would you use SQL to clean and prepare data for analysis? + How would you use SQL to join multiple tables for analysis? ¢ How would you use SQL to perform time series analysis? Remember: This is not an exhaustive list, and specific questions may vary depending on the role and company. Focus on understanding the underlying concepts and practicing writing SQL queries to solve real-world problems. Thanks, Chandan