SQL Server RealTime Questions Answers

SQL Interview Q&A · All 10 parts, 70 questions

This page lists every post in the SQL Interview Questions and Answers: Real-Time Scenarios series. There are 10 parts and 70 questions, each one asked the way it comes up in a real job or a real interview: a manager needs a number, a report is wrong, a query is slow.

Each part has a video and a written post with the short answer to say first, the complete SQL query, and its real output on a small sample database you can create yourself. Click a part to read it, or click a question to jump straight to its answer.

Part 1: SQL Fundamentals That Trip People Up

Watch Part 1 on YouTube

1. A developer calculates a 10 percent tax in the SELECT list with the alias tax, filters WHERE tax greater than 50, and gets the error 'column tax does not exist'. In what order does SQL actually run a SELECT, and how do you fix the query?
2. Your manager wants the customers who spent more than 1,000, not counting cancelled orders. A teammate wrote WHERE SUM(amount) greater than 1000 and got an error. When do you use WHERE, and when do you use HAVING?
3. Three reports count customers and get 7, 6 and 2. What is the difference between COUNT(*), COUNT(column) and COUNT(DISTINCT column), and which one answers the business question?
4. A query with WHERE region = NULL returns no rows, and an order balance (amount minus paid amount) comes back NULL. How do NULLs behave in comparisons and arithmetic, and how do you fix both?
5. You need one list of the customers who ordered in January or February. Should you use UNION or UNION ALL, and what is the difference?
6. A teammate needs to empty a staging table before tonight's load. Should they use DELETE, TRUNCATE or DROP, and what does each one really do?
7. The report's count of customers outside the East region is one short of the CRM. The query filters region not equal to 'East'. Why is a customer missing, and how do you fix it?

Part 2: Joins in Real-Time Scenarios

Watch Part 2 on YouTube

1. HR wants every department with its employees, including the department with nobody in it and the employees with no department yet. What is the difference between INNER JOIN, LEFT JOIN and FULL OUTER JOIN, and which one gives this list?
2. Marketing wants to email the customers who have never placed an order. How do you find them in SQL?
3. You joined 7 customers to their orders and got 13 rows back. Why did the row count go up, and when does it become a bug?
4. A manager wants every customer with their shipped orders, if they have any, but your LEFT JOIN returns only 2 customers. What is the difference between putting the filter in ON and in WHERE?
5. HR asks for each employee with their manager's name, and then for the employees who earn more than their manager. How do you write both queries?
6. The monthly sales report skips months with no sales for a category, so the chart has gaps. How do you build a full category by month grid that shows 0 for empty months?
7. Finance says revenue went up after you joined orders to payments: 4,050 instead of 3,400. Why did it happen, and how do you fix it?

Part 3: GROUP BY and Aggregation

Watch Part 3 on YouTube

1. The CRM has the same person registered twice under one email, so every customer count is one too high. How do you find duplicate emails and show the customer rows behind them?
2. A dashboard tile needs total, delivered, shipped and cancelled order counts in a single row. How do you write it in one query?
3. A product manager wants one row per category with each month's sales as its own column. How do you pivot rows into columns in SQL?
4. Four of ten employees have no bonus value yet (NULL). Should the average bonus be AVG(bonus) or AVG(COALESCE(bonus, 0)), and why do they give different answers?
5. Leadership asks which departments pay above the company average salary. How do you write it, and where does the filter go?
6. Finance wants each category's share of total revenue as a percentage. How do you calculate percent of total, and what are the two common traps?
7. Sales wants the customers who bought BOTH a Laptop and a Monitor. Why does product = 'Laptop' AND product = 'Monitor' return nothing, and what is the right query?

Part 4: Subqueries, CTEs and Recursive Queries

Watch Part 4 on YouTube

1. A teammate's query repeats the same spend calculation in two nested subqueries. What is the difference between a subquery and a CTE, and when would you rewrite it as a CTE?
2. HR wants the employees who earn more than the average of their own department. How do you write it with a correlated subquery?
3. You need the customers who have at least one Shipped order. Would you use IN or EXISTS, and what is the real difference?
4. You look for employees who manage nobody with NOT IN against manager_id, and the query returns zero rows. Why, and how do you fix it?
5. HR wants the full org chart: every employee, their level and the reporting path from the CEO. How do you build it in SQL?
6. A monthly revenue chart is missing May because nothing was delivered that month. How do you generate every month so empty months still show 0?
7. A manager wants one customer summary with total spent, number of orders, last order date and favorite category, including customers who never ordered. How do you build it so it stays readable?

Part 5: Ranking Window Functions

Watch Part 5 on YouTube

1. Two engineers earn the same salary. What is the difference between ROW_NUMBER, RANK and DENSE_RANK, and what does each one do on that tie?
2. HR asks for the second highest salary in the company. How do you write it, and what happens when there is no second salary?
3. HR now wants the third highest salary in each department. How do you write it, and why does filtering on RANK = 3 lose a department?
4. The sales director wants the top 2 products by revenue in each region. How do you write a top N per group query?
5. Customer service wants each customer's most recent order with all of its details. How do you get the latest row per customer?
6. Marketing wants customers split into four spending groups for a campaign. How do you do it with NTILE, and how are uneven groups handled?
7. The report shows the highest-paid employee in each department, but two people in Sales share the top salary and one is missing. Why is ROW_NUMBER = 1 the bug, and how do you fix it?

Part 6: Running Totals, LAG and LEAD

Watch Part 6 on YouTube

1. Finance wants a running total of revenue by order date. How do you calculate it without a self join or a subquery?
2. Your running total of payments shows the same 2,200 on two rows. What is the difference between ROWS and RANGE, and how do you fix the running total?
3. A manager wants month-over-month revenue change. How do you use LAG and LEAD, and what is the trap when a month has no sales?
4. The business wants a 3-month moving average of revenue. How do you write it, and what happens in the first two months?
5. Finance wants each category's percent of total revenue in one query, with no subquery. How do you do it with SUM() OVER ()?
6. You use LAST_VALUE to show the lowest-paid person in each department, but every row just shows itself. Why does LAST_VALUE seem not to work, and what is the fix?
7. Leadership wants one monthly report with revenue, the previous month, the change and the percent growth. How do you write it so a zero month can't break it?

Part 7: Duplicates and Data Cleaning

Watch Part 7 on YouTube

1. The web signup form was submitted more than once, so the signups table has repeated rows. How do you find duplicates across name, email and city?
2. You need to delete the duplicate signups but keep one row of each. How do you do it safely?
3. A staging table has several versions of each customer, two of them on the same day. How do you keep only the latest version of each record?
4. A raw customer file has extra spaces, mixed-case emails, empty strings and NULLs. How do you clean it in SQL before loading?
5. The source and the warehouse both have 12 orders, but the totals don't match: 5,200 against 5,230. How do you find exactly which rows differ?
6. A report that joins orders to customers silently loses 80 dollars of revenue. How do you find the orphan records, and how does a foreign key prevent them?
7. The nightly load ran twice and some order lines are now in the warehouse two times. How do you find and remove the double-loaded rows, and make the load safe to re-run?

Part 8: Dates, Gaps and Islands

Watch Part 8 on YouTube

1. For each customer, the business wants the first order date, the last order date, the days between them and the days since the last order. How do you write it?
2. A user is expected to log in every day, but some days are missing. How do you find the missing dates in a sequence?
3. Product wants each user's runs of consecutive login days. How do you solve this gaps and islands problem?
4. Gamification wants each user's longest login streak. How do you find it, and how do you handle two streaks of the same length?
5. Web analytics wants clicks grouped into sessions, where a new session starts after 30 minutes without activity. How do you sessionize events in SQL?
6. Marketing wants the customers who ordered in consecutive months, for example three months in a row. How do you find them?
7. Leadership asks: how many January customers came back in February? How do you calculate month-over-month retention, and what must you agree on first?

Part 9: Performance and Indexes

Watch Part 9 on YouTube

1. Customer service looks up one customer's orders in a 20,000-row table and the query reads every row. What is an index, and how does it change the execution plan?
2. An interviewer asks about clustered and non-clustered indexes in SQL Server. What is the difference, and how is PostgreSQL different?
3. A report filters on the year of order_date and is slow even though order_date has an index. What is a sargable predicate, and how do you rewrite the filter?
4. You have an index on (customer_id, order_date). Why does it help a filter on customer_id but not a filter on order_date alone?
5. A query returns 10 rows but still has to read the table. What is a covering index, and why does SELECT * hurt performance?
6. You are handed a slow query and its execution plan. How do you read the plan, and what are the first three things you look for?
7. A report query that ran in 2 seconds now takes 2 minutes, and nobody changed the code. What do you do, step by step?

Part 10: Real-World Manager Scenarios

Watch Part 10 on YouTube

1. Every night a staging table arrives with changed and new products. How do you update only the rows that changed and insert the new ones in one statement?
2. HR asks what each engineer's salary was on March 31, 2025. How do you do a point-in-time lookup with validity ranges, and what is the BETWEEN trap?
3. The ERP and the warehouse report different daily sales totals. How do you reconcile the two systems and show only the days that don't match?
4. Leadership wants to know which customers make up 80 percent of revenue. How do you do a Pareto analysis in SQL, and what is the trap at 80 percent?
5. HR wants the typical salary per department, but one big salary pulls the average up. How do you calculate the median and other percentiles per group?
6. Finance needs a running balance for each account and the first day it went negative. How do you write it?
7. A stakeholder says your SQL report is wrong. Walk me through how you debug it, step by step.

Each part has a matching video on the TechBrothersIT YouTube channel, in the "SQL Interview Questions and Answers (Real-Time Scenarios)" playlist.