In this post we are going to cover real-world manager scenarios: upserts with MERGE, point-in-time lookups, reconciling two systems, Pareto analysis, medians, running balances and debugging a wrong report. These are 7 real SQL interview questions, asked the way they come up on the job, and the last one is the scenario every data engineer meets sooner or later: a stakeholder says your report is wrong.
This is the last part of the series. These questions are less about one function and more about how you think: what grain you compare at, where the boundary of a date range sits, and how you prove a number. Senior interviews are full of them, because this is the work.
Every query below is complete, so you can copy it, run it on the sample data and get exactly the output shown under it.
Part 10 of the SQL Server RealTime Questions Answers series on TechBrothersIT.
- The short answer
- Let's create some sample data
- Question 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?
- Question 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?
- Question 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?
- Question 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?
- Question 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?
- Question 6: Finance needs a running balance for each account and the first day it went negative. How do you write it?
- Scenario 7: A stakeholder says your SQL report is wrong. Walk me through how you debug it, step by step.
- The full SQL script
The short answer
Real-world SQL is a handful of patterns: MERGE to upsert only what changed, half-open validity ranges for point-in-time lookups, FULL OUTER JOIN + COALESCE to reconcile two systems, running totals for Pareto and balances, and PERCENTILE_CONT for medians. When a report is wrong, rebuild it step by step and check the row count and the total after every join and filter.
Each question below is one of these patterns, with the trap that usually comes with it.
Let's create some sample data
We use the TB Shop database: departments, employees, customers, products, orders, order items and payments, plus four extra tables for this part: stg_products (a staging feed), salary_history, erp_daily_sales and account_txn. The script is written for PostgreSQL, and it runs on SQL Server, MySQL 8 or Snowflake with small changes.
DROP TABLE IF EXISTS payments;
DROP TABLE IF EXISTS order_items;
DROP TABLE IF EXISTS orders;
DROP TABLE IF EXISTS products;
DROP TABLE IF EXISTS customers;
DROP TABLE IF EXISTS employees;
DROP TABLE IF EXISTS departments;
CREATE TABLE departments (dept_id INT PRIMARY KEY, dept_name VARCHAR(20));
INSERT INTO departments VALUES (10, 'Sales'), (20, 'Engineering'), (30, 'HR'), (40, 'Finance');
CREATE TABLE employees (
emp_id INT PRIMARY KEY,
name VARCHAR(20),
dept_id INT, -- NULL = not assigned yet
manager_id INT, -- NULL = top of the company
salary INT,
hire_date DATE
);
INSERT INTO employees VALUES
(1, 'Aisha', NULL, NULL, 150000, '2018-03-01'),
(2, 'Omar', 20, 1, 120000, '2019-06-15'),
(3, 'Lina', 20, 2, 90000, '2021-01-10'),
(4, 'Ravi', 20, 2, 90000, '2022-04-05'),
(5, 'Mei', 20, 2, 75000, '2024-09-01'),
(6, 'Sara', 10, 1, 95000, '2020-02-20'),
(7, 'Daniel', 10, 6, 105000, '2021-07-01'),
(8, 'Fatima', 10, 6, 60000, '2023-11-12'),
(9, 'John', 30, 1, 70000, '2019-09-30'),
(10,'Zara', NULL, 9, 55000, '2026-01-05');
CREATE TABLE customers (
customer_id INT PRIMARY KEY,
name VARCHAR(20),
city VARCHAR(20),
region VARCHAR(10), -- NULL = unknown
email VARCHAR(40)
);
INSERT INTO customers VALUES
(1, 'Sara Khan', 'Toronto', 'East', 'sara@mail.com'),
(2, 'Omar Ali', 'Dallas', 'West', 'omar@mail.com'),
(3, 'Lina Park', 'Seattle', 'West', 'lina@mail.com'),
(4, 'Mei Chen', 'Boston', 'East', 'mei@mail.com'),
(5, 'Ravi Patel', 'Austin', NULL, 'ravi@mail.com'),
(6, 'Chen Wu', 'Denver', 'West', 'chen@mail.com'),
(7, 'Sara K.', 'Toronto', 'East', 'sara@mail.com');
CREATE TABLE products (product_id INT PRIMARY KEY, product VARCHAR(20), category VARCHAR(15), price DECIMAL(10,2));
INSERT INTO products VALUES
(1, 'Laptop', 'Electronics', 1200.00),
(2, 'Monitor', 'Electronics', 300.00),
(3, 'Keyboard', 'Accessories', 50.00),
(4, 'Mouse', 'Accessories', 25.00),
(5, 'Desk', 'Furniture', 400.00),
(6, 'Chair', 'Furniture', 250.00);
CREATE TABLE orders (
order_id INT PRIMARY KEY,
customer_id INT,
order_date DATE,
status VARCHAR(10),
amount DECIMAL(10,2)
);
INSERT INTO orders VALUES
(1001, 1, '2026-01-05', 'Delivered', 1250.00),
(1002, 2, '2026-01-18', 'Delivered', 300.00),
(1003, 4, '2026-02-02', 'Delivered', 650.00),
(1004, 1, '2026-02-14', 'Cancelled', 50.00),
(1005, 3, '2026-02-20', 'Delivered', 1200.00),
(1006, 4, '2026-03-03', 'Shipped', 275.00),
(1007, 5, '2026-03-15', 'Delivered', 400.00),
(1008, 2, '2026-04-01', 'Delivered', 75.00),
(1009, 4, '2026-04-22', 'Delivered', 250.00),
(1010, 1, '2026-05-09', 'Shipped', 300.00),
(1011, 3, '2026-05-30', 'Cancelled', 400.00),
(1012, 7, '2026-06-11', 'Delivered', 50.00);
CREATE TABLE order_items (order_id INT, product_id INT, qty INT, unit_price DECIMAL(10,2));
INSERT INTO order_items VALUES
(1001, 1, 1, 1200.00), (1001, 3, 1, 50.00),
(1002, 2, 1, 300.00),
(1003, 5, 1, 400.00), (1003, 6, 1, 250.00),
(1004, 3, 1, 50.00),
(1005, 1, 1, 1200.00),
(1006, 6, 1, 250.00), (1006, 4, 1, 25.00),
(1007, 5, 1, 400.00),
(1008, 4, 3, 25.00),
(1009, 6, 1, 250.00),
(1010, 2, 1, 300.00),
(1011, 5, 1, 400.00),
(1012, 3, 1, 50.00);
CREATE TABLE payments (payment_id INT PRIMARY KEY, order_id INT, paid_amount DECIMAL(10,2));
INSERT INTO payments VALUES
(1, 1001, 1250.00), (2, 1002, 300.00), (3, 1003, 400.00), (4, 1003, 250.00), (5, 1005, 1200.00);
DROP TABLE IF EXISTS stg_products;
DROP TABLE IF EXISTS salary_history;
DROP TABLE IF EXISTS erp_daily_sales;
DROP TABLE IF EXISTS account_txn;
-- tonight's supplier feed: Monitor has a new price, Keyboard is unchanged, Webcam is new
CREATE TABLE stg_products (product_id INT PRIMARY KEY, product VARCHAR(20), category VARCHAR(15), price DECIMAL(10,2));
INSERT INTO stg_products VALUES
(2, 'Monitor', 'Electronics', 280.00),
(3, 'Keyboard', 'Accessories', 50.00),
(7, 'Webcam', 'Electronics', 90.00);
-- salary history as validity ranges: valid_to is EXCLUSIVE (the day the next row starts); 9999-12-31 = current
CREATE TABLE salary_history (emp_id INT, salary INT, valid_from DATE, valid_to DATE);
INSERT INTO salary_history VALUES
(2, 110000, '2019-06-15', '2025-04-01'), (2, 120000, '2025-04-01', '9999-12-31'),
(3, 80000, '2021-01-10', '2024-04-01'), (3, 85000, '2024-04-01', '2025-04-01'), (3, 90000, '2025-04-01', '9999-12-31'),
(4, 82000, '2022-04-05', '2025-04-01'), (4, 90000, '2025-04-01', '9999-12-31'),
(5, 75000, '2024-09-01', '9999-12-31');
-- daily sales totals as the ERP (finance) reports them
CREATE TABLE erp_daily_sales (sales_date DATE PRIMARY KEY, erp_total DECIMAL(10,2));
INSERT INTO erp_daily_sales VALUES
('2026-01-05', 1250.00), ('2026-01-18', 300.00), ('2026-02-02', 650.00), ('2026-02-20', 1200.00),
('2026-03-03', 300.00), ('2026-03-15', 400.00), ('2026-04-01', 75.00), ('2026-04-22', 250.00),
('2026-05-09', 300.00), ('2026-06-12', 50.00);
-- bank account transactions: deposits are positive, withdrawals negative
CREATE TABLE account_txn (txn_id INT PRIMARY KEY, account_id VARCHAR(10), txn_date DATE, amount DECIMAL(10,2));
INSERT INTO account_txn VALUES
(1, 'A-100', '2026-03-01', 500.00), (2, 'A-100', '2026-03-05', -200.00), (3, 'A-100', '2026-03-09', -250.00),
(4, 'A-200', '2026-03-01', 300.00), (5, 'A-200', '2026-03-06', -350.00), (6, 'A-200', '2026-03-08', 400.00),
(7, 'A-200', '2026-03-10', -500.00),
(8, 'A-300', '2026-03-02', 200.00), (9, 'A-300', '2026-03-04', -150.00), (10, 'A-300', '2026-03-04', -80.00);Here are the employees. Omar, Lina, Ravi and Mei are in Engineering (department 20), and Aisha and Zara have no department.
| emp_id | name | dept_id | manager_id | salary | hire_date |
|---|---|---|---|---|---|
| 1 | Aisha | NULL | NULL | 150000 | 2018-03-01 |
| 2 | Omar | 20 | 1 | 120000 | 2019-06-15 |
| 3 | Lina | 20 | 2 | 90000 | 2021-01-10 |
| 4 | Ravi | 20 | 2 | 90000 | 2022-04-05 |
| 5 | Mei | 20 | 2 | 75000 | 2024-09-01 |
| 6 | Sara | 10 | 1 | 95000 | 2020-02-20 |
| 7 | Daniel | 10 | 6 | 105000 | 2021-07-01 |
| 8 | Fatima | 10 | 6 | 60000 | 2023-11-12 |
| 9 | John | 30 | 1 | 70000 | 2019-09-30 |
| 10 | Zara | NULL | 9 | 55000 | 2026-01-05 |
And the orders. Orders 1004 and 1011 are Cancelled, and Ravi Patel (customer 5) placed order 1007.
| order_id | customer_id | order_date | status | amount |
|---|---|---|---|---|
| 1001 | 1 | 2026-01-05 | Delivered | 1250.00 |
| 1002 | 2 | 2026-01-18 | Delivered | 300.00 |
| 1003 | 4 | 2026-02-02 | Delivered | 650.00 |
| 1004 | 1 | 2026-02-14 | Cancelled | 50.00 |
| 1005 | 3 | 2026-02-20 | Delivered | 1200.00 |
| 1006 | 4 | 2026-03-03 | Shipped | 275.00 |
| 1007 | 5 | 2026-03-15 | Delivered | 400.00 |
| 1008 | 2 | 2026-04-01 | Delivered | 75.00 |
| 1009 | 4 | 2026-04-22 | Delivered | 250.00 |
| 1010 | 1 | 2026-05-09 | Shipped | 300.00 |
| 1011 | 3 | 2026-05-30 | Cancelled | 400.00 |
| 1012 | 7 | 2026-06-11 | Delivered | 50.00 |
Question 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?
MERGE does it in one statement: match on the key, update when matched and something actually changed, insert when not matched.
Tonight's supplier feed has three products: the Monitor with a new price, the Keyboard unchanged, and a new Webcam.
| product_id | product | category | price |
|---|---|---|---|
| 2 | Monitor | Electronics | 280.00 |
| 3 | Keyboard | Accessories | 50.00 |
| 7 | Webcam | Electronics | 90.00 |
Here is the products table before the load. The Monitor is 300.00.
| product_id | product | category | price |
|---|---|---|---|
| 1 | Laptop | Electronics | 1200.00 |
| 2 | Monitor | Electronics | 300.00 |
| 3 | Keyboard | Accessories | 50.00 |
| 4 | Mouse | Accessories | 25.00 |
| 5 | Desk | Furniture | 400.00 |
| 6 | Chair | Furniture | 250.00 |
Now the MERGE. We compare the columns with IS DISTINCT FROM, because a <> test misses a change from or to NULL.
MERGE INTO products AS t
USING stg_products AS s
ON t.product_id = s.product_id
WHEN MATCHED AND (t.price IS DISTINCT FROM s.price
OR t.product IS DISTINCT FROM s.product
OR t.category IS DISTINCT FROM s.category) THEN
UPDATE SET product = s.product,
category = s.category,
price = s.price
WHEN NOT MATCHED THEN
INSERT (product_id, product, category, price)
VALUES (s.product_id, s.product, s.category, s.price);The statement runs and changes the data. The check query below shows the result.
SELECT * FROM products ORDER BY product_id;| product_id | product | category | price |
|---|---|---|---|
| 1 | Laptop | Electronics | 1200.00 |
| 2 | Monitor | Electronics | 280.00 |
| 3 | Keyboard | Accessories | 50.00 |
| 4 | Mouse | Accessories | 25.00 |
| 5 | Desk | Furniture | 400.00 |
| 6 | Chair | Furniture | 250.00 |
| 7 | Webcam | Electronics | 90.00 |
The Monitor is now 280.00, the Webcam is inserted as product 7 at 90.00, and the Keyboard is matched but not touched. Skipping unchanged rows avoids pointless row versions, triggers and change data capture events.
Two follow-ups. The staging key must be unique, or MERGE fails when two source rows hit one target row. And dialects: PostgreSQL needs version 15 for MERGE, or use INSERT ... ON CONFLICT. MySQL uses ON DUPLICATE KEY UPDATE. SQL Server ends the MERGE with a semicolon, and needs HOLDLOCK on the target for concurrent loads.
IS DISTINCT FROM treats NULL as a value. In SQL Server it needs version 2022 or later.
Question 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?
Store history as validity ranges: one row per salary, with valid_from and valid_to. The current row runs to 9999-12-31, and valid_to is exclusive: the old row ends on the day the new one starts.
| emp_id | salary | valid_from | valid_to |
|---|---|---|---|
| 2 | 110000 | 2019-06-15 | 2025-04-01 |
| 2 | 120000 | 2025-04-01 | 9999-12-31 |
| 3 | 80000 | 2021-01-10 | 2024-04-01 |
| 3 | 85000 | 2024-04-01 | 2025-04-01 |
| 3 | 90000 | 2025-04-01 | 9999-12-31 |
| 4 | 82000 | 2022-04-05 | 2025-04-01 |
| 4 | 90000 | 2025-04-01 | 9999-12-31 |
| 5 | 75000 | 2024-09-01 | 9999-12-31 |
The lookup is a half-open range: valid_from <= date and valid_to > date.
SELECT e.name, h.salary, h.valid_from, h.valid_to
FROM employees e
JOIN salary_history h ON h.emp_id = e.emp_id
WHERE e.dept_id = 20
AND h.valid_from <= CAST('2025-03-31' AS DATE)
AND h.valid_to > CAST('2025-03-31' AS DATE)
ORDER BY e.emp_id;| name | salary | valid_from | valid_to |
|---|---|---|---|
| Omar | 110000 | 2019-06-15 | 2025-04-01 |
| Lina | 85000 | 2024-04-01 | 2025-04-01 |
| Ravi | 82000 | 2022-04-05 | 2025-04-01 |
| Mei | 75000 | 2024-09-01 | 9999-12-31 |
Exactly one row per person: Omar 110000, Lina 85000, Ravi 82000 and Mei 75000.
Now the classic bug. Let's use BETWEEN on 2025-04-01, the day the raises started, and count the rows each person matches.
SELECT e.name, COUNT(*) AS rows_found
FROM employees e
JOIN salary_history h ON h.emp_id = e.emp_id
WHERE e.dept_id = 20
AND CAST('2025-04-01' AS DATE) BETWEEN h.valid_from AND h.valid_to
GROUP BY e.emp_id, e.name
ORDER BY e.emp_id;| name | rows_found |
|---|---|
| Omar | 2 |
| Lina | 2 |
| Ravi | 2 |
| Mei | 1 |
Omar, Lina and Ravi each match 2 rows, because BETWEEN includes both ends, and 2025-04-01 is both the old valid_to and the new valid_from. Joined to payroll, their cost would double.
So use half-open ranges, and always test the boundary day. If the source only has an effective date, build valid_to with LEAD(valid_from) OVER (PARTITION BY emp_id ORDER BY valid_from). The same pattern joins facts to a type 2 dimension.
Question 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?
Aggregate both sides to the same grain first, one total per day, then FULL OUTER JOIN on the date. A full join keeps the days that exist on only one side, which an inner join would hide. Here are the ERP totals.
| sales_date | erp_total |
|---|---|
| 2026-01-05 | 1250.00 |
| 2026-01-18 | 300.00 |
| 2026-02-02 | 650.00 |
| 2026-02-20 | 1200.00 |
| 2026-03-03 | 300.00 |
| 2026-03-15 | 400.00 |
| 2026-04-01 | 75.00 |
| 2026-04-22 | 250.00 |
| 2026-05-09 | 300.00 |
| 2026-06-12 | 50.00 |
The warehouse side comes from our orders, without cancelled ones. COALESCE takes the date from whichever side has it and treats a missing total as zero, and the WHERE clause keeps only the problems.
WITH wh AS (
SELECT order_date AS sales_date, SUM(amount) AS wh_total
FROM orders
WHERE status <> 'Cancelled'
GROUP BY order_date
)
SELECT COALESCE(e.sales_date, w.sales_date) AS sales_date,
e.erp_total, w.wh_total,
COALESCE(e.erp_total, 0) - COALESCE(w.wh_total, 0) AS difference
FROM erp_daily_sales e
FULL OUTER JOIN wh w ON w.sales_date = e.sales_date
WHERE e.sales_date IS NULL OR w.sales_date IS NULL
OR e.erp_total <> w.wh_total
ORDER BY sales_date;| sales_date | erp_total | wh_total | difference |
|---|---|---|---|
| 2026-03-03 | 300.00 | 275.00 | 25.00 |
| 2026-06-11 | NULL | 50.00 | -50.00 |
| 2026-06-12 | 50.00 | NULL | 50.00 |
Three rows come back. On 2026-03-03 the ERP says 300.00 and the warehouse has 275.00, a difference of 25.00. And 2026-06-11 and 2026-06-12 have the same 50.00, on different days.
That pair is the classic time zone problem: a late evening order in one system is the next morning in the other. Run this check after every load.
There is no FULL OUTER JOIN in MySQL. UNION a LEFT JOIN and a RIGHT JOIN instead.
Question 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?
That is a Pareto analysis. Total revenue per customer, then a running total from the biggest customer down, divided by the grand total. Both are SUM OVER: one with an ORDER BY, one with an empty window.
WITH rev AS (
SELECT c.name, o.customer_id, SUM(o.amount) AS revenue
FROM orders o
JOIN customers c ON c.customer_id = o.customer_id
WHERE o.status <> 'Cancelled'
GROUP BY c.name, o.customer_id
), r AS (
SELECT name, customer_id, revenue,
SUM(revenue) OVER (ORDER BY revenue DESC, customer_id
ROWS UNBOUNDED PRECEDING) AS running_revenue,
SUM(revenue) OVER () AS total_revenue
FROM rev
)
SELECT name, revenue, running_revenue,
CAST(100.0 * running_revenue / total_revenue AS DECIMAL(5,2)) AS running_pct,
CASE WHEN running_revenue - revenue < 0.8 * total_revenue
THEN 'Yes' ELSE 'No' END AS top_80
FROM r
ORDER BY revenue DESC, customer_id;| name | revenue | running_revenue | running_pct | top_80 |
|---|---|---|---|---|
| Sara Khan | 1550.00 | 1550.00 | 32.63 | Yes |
| Lina Park | 1200.00 | 2750.00 | 57.89 | Yes |
| Mei Chen | 1175.00 | 3925.00 | 82.63 | Yes |
| Ravi Patel | 400.00 | 4325.00 | 91.05 | No |
| Omar Ali | 375.00 | 4700.00 | 98.95 | No |
| Sara K. | 50.00 | 4750.00 | 100.00 | No |
Sara Khan alone is 32.63 percent. Add Lina Park and we are at 57.89. Add Mei Chen and it is 82.63.
Now the trap. Filter on running_pct <= 80 and you get only 2 customers, who make 57.89 percent, not 80. The right rule: keep a customer while the running total before them is still below 80 percent. That is the top_80 column, and it gives three customers.
So three of the six customers with orders bring in 82.63 percent of revenue.
Add a tie-breaker to the ORDER BY, here customer_id, and use ROWS, not the default RANGE frame, so customers with the same revenue don't jump together.
Question 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?
The average is pulled by outliers. The median is the middle value, so one big salary can't drag it. In SQL it is PERCENTILE_CONT(0.5) WITHIN GROUP (ORDER BY salary), used with GROUP BY like any aggregate.
SELECT COALESCE(d.dept_name, 'Unassigned') AS department,
COUNT(*) AS employees,
CAST(AVG(e.salary) AS DECIMAL(10,2)) AS avg_salary,
PERCENTILE_CONT(0.5) WITHIN GROUP (ORDER BY e.salary) AS median,
PERCENTILE_DISC(0.5) WITHIN GROUP (ORDER BY e.salary) AS median_disc,
PERCENTILE_CONT(0.9) WITHIN GROUP (ORDER BY e.salary) AS p90
FROM employees e
LEFT JOIN departments d ON d.dept_id = e.dept_id
GROUP BY d.dept_name
ORDER BY department;| department | employees | avg_salary | median | median_disc | p90 |
|---|---|---|---|---|---|
| Engineering | 4 | 93750.00 | 90000 | 90000 | 111000 |
| HR | 1 | 70000.00 | 70000 | 70000 | 70000 |
| Sales | 3 | 86666.67 | 95000 | 95000 | 103000 |
| Unassigned | 2 | 102500.00 | 102500 | 55000 | 140500 |
Engineering's average is 93750.00, but the median is 90000. The 90th percentile, 111000, shows what the top of the band looks like.
Now CONT versus DISC. PERCENTILE_CONT interpolates, so the Unassigned group, with two people, gets a median of 102500, a salary nobody earns. PERCENTILE_DISC returns a real value from the data, the lower of the two: 55000.
Dialects differ here:
- PostgreSQL, Snowflake and Oracle support it as a grouped aggregate, like above.
- SQL Server has it only as a window function: add
OVER (PARTITION BY dept_id)and DISTINCT. - MySQL has neither: number the rows with ROW_NUMBER and pick the middle.
Question 6: Finance needs a running balance for each account and the first day it went negative. How do you write it?
The running balance is SUM of the amount OVER, PARTITION BY account, ORDER BY date and transaction ID. The partition restarts the total for every account, and the transaction ID breaks ties on the same day.
SELECT account_id, txn_date, txn_id, amount,
SUM(amount) OVER (PARTITION BY account_id
ORDER BY txn_date, txn_id
ROWS UNBOUNDED PRECEDING) AS balance
FROM account_txn
ORDER BY account_id, txn_date, txn_id;| account_id | txn_date | txn_id | amount | balance |
|---|---|---|---|---|
| A-100 | 2026-03-01 | 1 | 500.00 | 500.00 |
| A-100 | 2026-03-05 | 2 | -200.00 | 300.00 |
| A-100 | 2026-03-09 | 3 | -250.00 | 50.00 |
| A-200 | 2026-03-01 | 4 | 300.00 | 300.00 |
| A-200 | 2026-03-06 | 5 | -350.00 | -50.00 |
| A-200 | 2026-03-08 | 6 | 400.00 | 350.00 |
| A-200 | 2026-03-10 | 7 | -500.00 | -150.00 |
| A-300 | 2026-03-02 | 8 | 200.00 | 200.00 |
| A-300 | 2026-03-04 | 9 | -150.00 | 50.00 |
| A-300 | 2026-03-04 | 10 | -80.00 | -30.00 |
Account A-100 never goes below zero: it ends at 50.00. A-200 drops to -50.00 on 2026-03-06. And A-300 has two withdrawals on 2026-03-04: after the first it is at 50.00, after the second it is -30.00.
For the first negative day, we compute the balance in a CTE first, because a window function can't go in WHERE. Then we keep the negative rows and take the MIN date per account.
WITH b AS (
SELECT account_id, txn_date,
SUM(amount) OVER (PARTITION BY account_id
ORDER BY txn_date, txn_id
ROWS UNBOUNDED PRECEDING) AS balance
FROM account_txn
)
SELECT account_id, MIN(txn_date) AS first_negative_date,
MIN(balance) AS lowest_balance
FROM b
WHERE balance < 0
GROUP BY account_id
ORDER BY account_id;| account_id | first_negative_date | lowest_balance |
|---|---|---|
| A-200 | 2026-03-06 | -150.00 |
| A-300 | 2026-03-04 | -30.00 |
A-200 first went negative on 2026-03-06, with a lowest balance of -150.00. A-300 went negative on 2026-03-04, at -30.00. A-100 is not in the list.
Without the transaction ID and ROWS, the default RANGE frame sums same-day transactions together. And ask whether the business means the intraday or the end-of-day balance.
Scenario 7: A stakeholder says your SQL report is wrong. Walk me through how you debug it, step by step.
First, reproduce it: same filters, same date range, and the exact number they expect. Then rebuild the query one step at a time, and check the row count and the total after every join and filter.
Here is that check for a first quarter revenue report. Each CTE adds one step, and the UNION ALL prints the row count and the total after each one.
WITH q1 AS (SELECT * FROM orders
WHERE order_date >= CAST('2026-01-01' AS DATE)
AND order_date < CAST('2026-04-01' AS DATE)
AND status <> 'Cancelled'),
s2 AS (SELECT q1.*, c.region FROM q1
JOIN customers c ON c.customer_id = q1.customer_id),
s3 AS (SELECT * FROM s2 WHERE region IN ('East', 'West')),
s4 AS (SELECT s3.* FROM s3
JOIN payments p ON p.order_id = s3.order_id)
SELECT 1 AS step_no, 'orders in Q1' AS check_point,
COUNT(*) AS row_count, SUM(amount) AS total FROM q1
UNION ALL SELECT 2, '+ join customers', COUNT(*), SUM(amount) FROM s2
UNION ALL SELECT 3, '+ region filter', COUNT(*), SUM(amount) FROM s3
UNION ALL SELECT 4, '+ join payments', COUNT(*), SUM(amount) FROM s4
ORDER BY step_no;| step_no | check_point | row_count | total |
|---|---|---|---|
| 1 | orders in Q1 | 6 | 4075.00 |
| 2 | + join customers | 6 | 4075.00 |
| 3 | + region filter | 5 | 3675.00 |
| 4 | + join payments | 5 | 4050.00 |
Step 1 has 6 orders and 4075.00. The customer join keeps all 6. The region filter drops one: Ravi's region is NULL, so the total falls to 3675.00.
Then the payments join. Still 5 rows, but the total goes up to 4050.00. Two bugs are hiding each other. Look at the payments:
| payment_id | order_id | paid_amount |
|---|---|---|
| 1 | 1001 | 1250.00 |
| 2 | 1002 | 300.00 |
| 3 | 1003 | 400.00 |
| 4 | 1003 | 250.00 |
| 5 | 1005 | 1200.00 |
Order 1006 has no payment, so the inner join drops it. Order 1003 was paid in two parts, so it is counted twice. A matching row count proves nothing, so check the totals and the key uniqueness too. The usual suspects:
- Duplicates from a join
- NULLs dropped by a filter or inner join
- The wrong status filter
- Date boundaries and time zones
Then explain it in the stakeholder's numbers: which orders, how much, and why. Fix it, and add a reconciliation check to the pipeline, so you find the next one before they do.
Key points to remember
- MERGE: update only what changed (IS DISTINCT FROM), insert what is new
- Point in time: half-open ranges, valid_from <= d < valid_to
- Reconcile: same grain, FULL OUTER JOIN, show only mismatches
- Running totals for Pareto and balances; PERCENTILE_CONT for medians
- Wrong report: check row count and total after every step
The full SQL script
Here is everything in one script: the sample data and every query from this post, in order. Copy it and run it top to bottom.
Show the full SQL script (sample data and every query)
-- ========================================================================
-- SQL Interview Q&A #10 - Real-world manager scenarios
-- TechBrothersIT. Sample data is invented for teaching.
-- Standard SQL, tested on PostgreSQL 16 (and cross-checked on DuckDB). Runs top to bottom in one go.
-- Run it in an empty database or schema: other videos reuse table names
-- like dim_customer, and their leftover tables can block the DROPs below.
-- PostgreSQL example: CREATE SCHEMA sql_q10; SET search_path TO sql_q10;
-- ========================================================================
-- Step 0: create and load the sample data
DROP TABLE IF EXISTS payments;
DROP TABLE IF EXISTS order_items;
DROP TABLE IF EXISTS orders;
DROP TABLE IF EXISTS products;
DROP TABLE IF EXISTS customers;
DROP TABLE IF EXISTS employees;
DROP TABLE IF EXISTS departments;
CREATE TABLE departments (dept_id INT PRIMARY KEY, dept_name VARCHAR(20));
INSERT INTO departments VALUES (10, 'Sales'), (20, 'Engineering'), (30, 'HR'), (40, 'Finance');
CREATE TABLE employees (
emp_id INT PRIMARY KEY,
name VARCHAR(20),
dept_id INT, -- NULL = not assigned yet
manager_id INT, -- NULL = top of the company
salary INT,
hire_date DATE
);
INSERT INTO employees VALUES
(1, 'Aisha', NULL, NULL, 150000, '2018-03-01'),
(2, 'Omar', 20, 1, 120000, '2019-06-15'),
(3, 'Lina', 20, 2, 90000, '2021-01-10'),
(4, 'Ravi', 20, 2, 90000, '2022-04-05'),
(5, 'Mei', 20, 2, 75000, '2024-09-01'),
(6, 'Sara', 10, 1, 95000, '2020-02-20'),
(7, 'Daniel', 10, 6, 105000, '2021-07-01'),
(8, 'Fatima', 10, 6, 60000, '2023-11-12'),
(9, 'John', 30, 1, 70000, '2019-09-30'),
(10,'Zara', NULL, 9, 55000, '2026-01-05');
CREATE TABLE customers (
customer_id INT PRIMARY KEY,
name VARCHAR(20),
city VARCHAR(20),
region VARCHAR(10), -- NULL = unknown
email VARCHAR(40)
);
INSERT INTO customers VALUES
(1, 'Sara Khan', 'Toronto', 'East', 'sara@mail.com'),
(2, 'Omar Ali', 'Dallas', 'West', 'omar@mail.com'),
(3, 'Lina Park', 'Seattle', 'West', 'lina@mail.com'),
(4, 'Mei Chen', 'Boston', 'East', 'mei@mail.com'),
(5, 'Ravi Patel', 'Austin', NULL, 'ravi@mail.com'),
(6, 'Chen Wu', 'Denver', 'West', 'chen@mail.com'),
(7, 'Sara K.', 'Toronto', 'East', 'sara@mail.com');
CREATE TABLE products (product_id INT PRIMARY KEY, product VARCHAR(20), category VARCHAR(15), price DECIMAL(10,2));
INSERT INTO products VALUES
(1, 'Laptop', 'Electronics', 1200.00),
(2, 'Monitor', 'Electronics', 300.00),
(3, 'Keyboard', 'Accessories', 50.00),
(4, 'Mouse', 'Accessories', 25.00),
(5, 'Desk', 'Furniture', 400.00),
(6, 'Chair', 'Furniture', 250.00);
CREATE TABLE orders (
order_id INT PRIMARY KEY,
customer_id INT,
order_date DATE,
status VARCHAR(10),
amount DECIMAL(10,2)
);
INSERT INTO orders VALUES
(1001, 1, '2026-01-05', 'Delivered', 1250.00),
(1002, 2, '2026-01-18', 'Delivered', 300.00),
(1003, 4, '2026-02-02', 'Delivered', 650.00),
(1004, 1, '2026-02-14', 'Cancelled', 50.00),
(1005, 3, '2026-02-20', 'Delivered', 1200.00),
(1006, 4, '2026-03-03', 'Shipped', 275.00),
(1007, 5, '2026-03-15', 'Delivered', 400.00),
(1008, 2, '2026-04-01', 'Delivered', 75.00),
(1009, 4, '2026-04-22', 'Delivered', 250.00),
(1010, 1, '2026-05-09', 'Shipped', 300.00),
(1011, 3, '2026-05-30', 'Cancelled', 400.00),
(1012, 7, '2026-06-11', 'Delivered', 50.00);
CREATE TABLE order_items (order_id INT, product_id INT, qty INT, unit_price DECIMAL(10,2));
INSERT INTO order_items VALUES
(1001, 1, 1, 1200.00), (1001, 3, 1, 50.00),
(1002, 2, 1, 300.00),
(1003, 5, 1, 400.00), (1003, 6, 1, 250.00),
(1004, 3, 1, 50.00),
(1005, 1, 1, 1200.00),
(1006, 6, 1, 250.00), (1006, 4, 1, 25.00),
(1007, 5, 1, 400.00),
(1008, 4, 3, 25.00),
(1009, 6, 1, 250.00),
(1010, 2, 1, 300.00),
(1011, 5, 1, 400.00),
(1012, 3, 1, 50.00);
CREATE TABLE payments (payment_id INT PRIMARY KEY, order_id INT, paid_amount DECIMAL(10,2));
INSERT INTO payments VALUES
(1, 1001, 1250.00), (2, 1002, 300.00), (3, 1003, 400.00), (4, 1003, 250.00), (5, 1005, 1200.00);
DROP TABLE IF EXISTS stg_products;
DROP TABLE IF EXISTS salary_history;
DROP TABLE IF EXISTS erp_daily_sales;
DROP TABLE IF EXISTS account_txn;
-- tonight's supplier feed: Monitor has a new price, Keyboard is unchanged, Webcam is new
CREATE TABLE stg_products (product_id INT PRIMARY KEY, product VARCHAR(20), category VARCHAR(15), price DECIMAL(10,2));
INSERT INTO stg_products VALUES
(2, 'Monitor', 'Electronics', 280.00),
(3, 'Keyboard', 'Accessories', 50.00),
(7, 'Webcam', 'Electronics', 90.00);
-- salary history as validity ranges: valid_to is EXCLUSIVE (the day the next row starts); 9999-12-31 = current
CREATE TABLE salary_history (emp_id INT, salary INT, valid_from DATE, valid_to DATE);
INSERT INTO salary_history VALUES
(2, 110000, '2019-06-15', '2025-04-01'), (2, 120000, '2025-04-01', '9999-12-31'),
(3, 80000, '2021-01-10', '2024-04-01'), (3, 85000, '2024-04-01', '2025-04-01'), (3, 90000, '2025-04-01', '9999-12-31'),
(4, 82000, '2022-04-05', '2025-04-01'), (4, 90000, '2025-04-01', '9999-12-31'),
(5, 75000, '2024-09-01', '9999-12-31');
-- daily sales totals as the ERP (finance) reports them
CREATE TABLE erp_daily_sales (sales_date DATE PRIMARY KEY, erp_total DECIMAL(10,2));
INSERT INTO erp_daily_sales VALUES
('2026-01-05', 1250.00), ('2026-01-18', 300.00), ('2026-02-02', 650.00), ('2026-02-20', 1200.00),
('2026-03-03', 300.00), ('2026-03-15', 400.00), ('2026-04-01', 75.00), ('2026-04-22', 250.00),
('2026-05-09', 300.00), ('2026-06-12', 50.00);
-- bank account transactions: deposits are positive, withdrawals negative
CREATE TABLE account_txn (txn_id INT PRIMARY KEY, account_id VARCHAR(10), txn_date DATE, amount DECIMAL(10,2));
INSERT INTO account_txn VALUES
(1, 'A-100', '2026-03-01', 500.00), (2, 'A-100', '2026-03-05', -200.00), (3, 'A-100', '2026-03-09', -250.00),
(4, 'A-200', '2026-03-01', 300.00), (5, 'A-200', '2026-03-06', -350.00), (6, 'A-200', '2026-03-08', 400.00),
(7, 'A-200', '2026-03-10', -500.00),
(8, 'A-300', '2026-03-02', 200.00), (9, 'A-300', '2026-03-04', -150.00), (10, 'A-300', '2026-03-04', -80.00);
-- Step 1: MERGE the staging feed into products: update what changed, insert what is new
MERGE INTO products AS t
USING stg_products AS s
ON t.product_id = s.product_id
WHEN MATCHED AND (t.price IS DISTINCT FROM s.price
OR t.product IS DISTINCT FROM s.product
OR t.category IS DISTINCT FROM s.category) THEN
UPDATE SET product = s.product,
category = s.category,
price = s.price
WHEN NOT MATCHED THEN
INSERT (product_id, product, category, price)
VALUES (s.product_id, s.product, s.category, s.price);
-- check the result
SELECT * FROM products ORDER BY product_id;
-- Step 2: Point-in-time lookup: each engineer's salary on 2025-03-31
SELECT e.name, h.salary, h.valid_from, h.valid_to
FROM employees e
JOIN salary_history h ON h.emp_id = e.emp_id
WHERE e.dept_id = 20
AND h.valid_from <= CAST('2025-03-31' AS DATE)
AND h.valid_to > CAST('2025-03-31' AS DATE)
ORDER BY e.emp_id;
-- Step 3: The boundary bug: BETWEEN on the day the raises started
SELECT e.name, COUNT(*) AS rows_found
FROM employees e
JOIN salary_history h ON h.emp_id = e.emp_id
WHERE e.dept_id = 20
AND CAST('2025-04-01' AS DATE) BETWEEN h.valid_from AND h.valid_to
GROUP BY e.emp_id, e.name
ORDER BY e.emp_id;
-- Step 4: Reconcile ERP daily totals with the warehouse: show only the mismatches
WITH wh AS (
SELECT order_date AS sales_date, SUM(amount) AS wh_total
FROM orders
WHERE status <> 'Cancelled'
GROUP BY order_date
)
SELECT COALESCE(e.sales_date, w.sales_date) AS sales_date,
e.erp_total, w.wh_total,
COALESCE(e.erp_total, 0) - COALESCE(w.wh_total, 0) AS difference
FROM erp_daily_sales e
FULL OUTER JOIN wh w ON w.sales_date = e.sales_date
WHERE e.sales_date IS NULL OR w.sales_date IS NULL
OR e.erp_total <> w.wh_total
ORDER BY sales_date;
-- Step 5: Pareto: which customers make up 80 percent of revenue?
WITH rev AS (
SELECT c.name, o.customer_id, SUM(o.amount) AS revenue
FROM orders o
JOIN customers c ON c.customer_id = o.customer_id
WHERE o.status <> 'Cancelled'
GROUP BY c.name, o.customer_id
), r AS (
SELECT name, customer_id, revenue,
SUM(revenue) OVER (ORDER BY revenue DESC, customer_id
ROWS UNBOUNDED PRECEDING) AS running_revenue,
SUM(revenue) OVER () AS total_revenue
FROM rev
)
SELECT name, revenue, running_revenue,
CAST(100.0 * running_revenue / total_revenue AS DECIMAL(5,2)) AS running_pct,
CASE WHEN running_revenue - revenue < 0.8 * total_revenue
THEN 'Yes' ELSE 'No' END AS top_80
FROM r
ORDER BY revenue DESC, customer_id;
-- Step 6: Median and percentiles per department (ordered-set aggregates)
SELECT COALESCE(d.dept_name, 'Unassigned') AS department,
COUNT(*) AS employees,
CAST(AVG(e.salary) AS DECIMAL(10,2)) AS avg_salary,
PERCENTILE_CONT(0.5) WITHIN GROUP (ORDER BY e.salary) AS median,
PERCENTILE_DISC(0.5) WITHIN GROUP (ORDER BY e.salary) AS median_disc,
PERCENTILE_CONT(0.9) WITHIN GROUP (ORDER BY e.salary) AS p90
FROM employees e
LEFT JOIN departments d ON d.dept_id = e.dept_id
GROUP BY d.dept_name
ORDER BY department;
-- Step 7: Running balance per account
SELECT account_id, txn_date, txn_id, amount,
SUM(amount) OVER (PARTITION BY account_id
ORDER BY txn_date, txn_id
ROWS UNBOUNDED PRECEDING) AS balance
FROM account_txn
ORDER BY account_id, txn_date, txn_id;
-- Step 8: The first day each account went negative
WITH b AS (
SELECT account_id, txn_date,
SUM(amount) OVER (PARTITION BY account_id
ORDER BY txn_date, txn_id
ROWS UNBOUNDED PRECEDING) AS balance
FROM account_txn
)
SELECT account_id, MIN(txn_date) AS first_negative_date,
MIN(balance) AS lowest_balance
FROM b
WHERE balance < 0
GROUP BY account_id
ORDER BY account_id;
-- Step 9: Debug a report: row count AND total after every step
WITH q1 AS (SELECT * FROM orders
WHERE order_date >= CAST('2026-01-01' AS DATE)
AND order_date < CAST('2026-04-01' AS DATE)
AND status <> 'Cancelled'),
s2 AS (SELECT q1.*, c.region FROM q1
JOIN customers c ON c.customer_id = q1.customer_id),
s3 AS (SELECT * FROM s2 WHERE region IN ('East', 'West')),
s4 AS (SELECT s3.* FROM s3
JOIN payments p ON p.order_id = s3.order_id)
SELECT 1 AS step_no, 'orders in Q1' AS check_point,
COUNT(*) AS row_count, SUM(amount) AS total FROM q1
UNION ALL SELECT 2, '+ join customers', COUNT(*), SUM(amount) FROM s2
UNION ALL SELECT 3, '+ region filter', COUNT(*), SUM(amount) FROM s3
UNION ALL SELECT 4, '+ join payments', COUNT(*), SUM(amount) FROM s4
ORDER BY step_no;
All 10 parts are listed on the SQL Server RealTime Questions Answers page on this blog.
The sample data on this page is invented for teaching. Every output table is the real result of the query above it, run on PostgreSQL 16 over that sample data, and the portable queries were also checked on a second engine. Where SQL Server syntax is different, the post says so.