SQL interviews for banking, accounting, lending, payments, and personal-finance analytics roles usually test more than whether you can write a SELECT. Interviewers want to see whether you understand duplicate rows, missing values, ties, time-based calculations, and the difference between a query that is logically correct and one that performs well on a large transaction table.
The examples below use PostgreSQL unless a different dialect is stated. SQL Server and MySQL use different pagination and top-row syntax, so name your database before presenting a dialect-specific answer.
Fundamental SQL interview questions
1. What is the difference between WHERE and HAVING?
WHERE filters individual rows before grouping. HAVING filters groups after aggregation. Use WHERE for conditions such as an account being active, and HAVING for conditions such as a customer having at least five transactions.
-- PostgreSQL
SELECT customer_id, COUNT(*) AS transaction_count
FROM transactions
WHERE status = 'posted'
GROUP BY customer_id
HAVING COUNT(*) >= 5
ORDER BY transaction_count DESC;Moving status = 'posted' into HAVING would change the meaning: the count would include other statuses before the group was tested. Also, GROUP BY does not sort the result; the explicit ORDER BY does that.
#1 Best Overall
2. How does COUNT(*) differ from COUNT(column)?
COUNT(*) counts rows. COUNT(column) counts only rows where that column is not NULL. This matters in financial data, where a customer may have a row but no recorded phone number, tax identifier, or settlement date.
-- PostgreSQL
SELECT
COUNT(*) AS all_customers,
COUNT(phone_number) AS customers_with_phone
FROM customers;SUM, MIN, and MAX generally ignore null input values. If a missing amount should be treated as zero, make that business rule explicit with COALESCE; do not assume that null and zero mean the same thing.
3. Why does col = NULL fail?
NULL represents an unknown or missing value. It is not zero, an empty string, or false. Comparisons involving it usually produce UNKNOWN under SQL’s three-valued logic, so neither col = NULL nor col <> NULL finds the intended rows.
-- PostgreSQL
SELECT account_id
FROM accounts
WHERE closed_at IS NULL;Use IS NULL and IS NOT NULL. Be especially careful with NOT IN: if its subquery returns a null, the predicate can evaluate to unknown and exclude rows unexpectedly. For an anti-join, NOT EXISTS is usually safer.
-- PostgreSQL
SELECT c.customer_id
FROM customers AS c
WHERE NOT EXISTS (
SELECT 1
FROM loans AS l
WHERE l.customer_id = c.customer_id
);4. What is the difference between INNER JOIN and LEFT JOIN?
An INNER JOIN returns only rows with a match on both sides. A LEFT JOIN retains every row from the left table and supplies nulls for missing right-side matches.
-- PostgreSQL
SELECT c.customer_id, p.payment_id
FROM customers AS c
LEFT JOIN payments AS p
ON p.customer_id = c.customer_id
AND p.status = 'settled';Putting the payment condition in the ON clause preserves customers with no settled payment. This version does not:
-- PostgreSQL: changes the practical result to inner-join behavior
SELECT c.customer_id, p.payment_id
FROM customers AS c
LEFT JOIN payments AS p
ON p.customer_id = c.customer_id
WHERE p.status = 'settled';The WHERE clause removes the null-extended rows for customers without a matching settled payment.
5. UNION or UNION ALL?
UNION combines result sets and removes duplicate rows. UNION ALL keeps duplicates and generally avoids the work of deduplicating them. Both queries must return the same number of columns with compatible corresponding data types.
Recommended Free Tools
-- PostgreSQL
SELECT email FROM customers
UNION ALL
SELECT email FROM newsletter_subscribers;If the business question is “show every source record,” UNION ALL is correct. If the question is “show each email once,” use UNION or an explicit deduplication strategy. An ORDER BY for the combined result normally belongs at the end.
Ranking, duplicates, and grouped results
6. How do you find the second-highest salary or balance?
First clarify whether ties count. A second distinct salary is different from the employee occupying position two after individual rows are sorted.
For the second distinct salary:
-- PostgreSQL
SELECT MAX(salary) AS second_highest_salary
FROM employees
WHERE salary < (
SELECT MAX(salary)
FROM employees
);To return every employee who earns that second distinct salary, use DENSE_RANK():
-- PostgreSQL
WITH ranked AS (
SELECT
employee_id,
salary,
DENSE_RANK() OVER (ORDER BY salary DESC) AS salary_rank
FROM employees
)
SELECT employee_id, salary
FROM ranked
WHERE salary_rank = 2;| Function | Behavior when values tie | Typical use |
|---|---|---|
ROW_NUMBER() |
Every row receives a different number | Keep exactly one record per group |
RANK() |
Ties share a rank, leaving gaps | Competition-style ranking |
DENSE_RANK() |
Ties share a rank, with no gaps | Second-highest distinct value |
7. How do you return the top three transactions per customer?
Use a window function in a CTE or derived table, then filter its result outside that query block. Include a deterministic tie-breaker if you use ROW_NUMBER().
The Tool Desk
Outbyte PC Repair FREERepair Windows errors before they cause bigger problemsFix Now →Outbyte Driver Updater FREEFix the driver behind crashes, sound loss and screen glitchesFind Drivers →-- PostgreSQL
WITH ranked AS (
SELECT
transaction_id,
customer_id,
amount,
ROW_NUMBER() OVER (
PARTITION BY customer_id
ORDER BY amount DESC, transaction_id
) AS rn
FROM transactions
)
SELECT transaction_id, customer_id, amount
FROM ranked
WHERE rn <= 3;This returns exactly three rows per customer when at least three exist. If every transaction tied at the third amount should be included, replace ROW_NUMBER() with DENSE_RANK(). Without a unique secondary ordering column, which tied row receives a given row number is not guaranteed.
8. What is the difference between aggregate and window functions?
An aggregate query collapses rows into groups. A window function calculates across related rows while retaining each detail row.
-- PostgreSQL: one result row per department
SELECT department_id, AVG(salary) AS department_average
FROM employees
GROUP BY department_id;-- PostgreSQL: every employee row remains visible
SELECT
employee_id,
department_id,
salary,
AVG(salary) OVER (PARTITION BY department_id) AS department_average
FROM employees;This distinction is useful in financial reporting: a grouped query can produce one monthly total, while a window query can show each payment alongside its customer’s monthly total or average.
9. How do you calculate a running total?
Order the rows, partition by the account or customer when necessary, and specify a row-based frame when each transaction should update the total separately.
What’s actually slowing this PC down?
Pick the symptom - the matching free tool is one click away.
-- PostgreSQL
SELECT
account_id,
transaction_id,
transaction_date,
amount,
SUM(amount) OVER (
PARTITION BY account_id
ORDER BY transaction_date, transaction_id
ROWS BETWEEN UNBOUNDED PRECEDING AND CURRENT ROW
) AS running_balance_change
FROM transactions;The transaction_id tie-breaker matters when two transactions have the same timestamp. Without an explicit frame, some databases use a peer-sensitive default that can give rows sharing an ordering value the same cumulative result.
10. Why can LAST_VALUE() return the wrong-looking value?
LAST_VALUE() works over the current window frame, not automatically over the entire partition. With a default ordered frame, it may return the current row’s value or the last peer’s value rather than the final event for the customer.
Rank #4
-- PostgreSQL
SELECT
customer_id,
event_time,
status,
LAST_VALUE(status) OVER (
PARTITION BY customer_id
ORDER BY event_time, event_id
ROWS BETWEEN UNBOUNDED PRECEDING
AND UNBOUNDED FOLLOWING
) AS final_status
FROM account_events;11. How do you remove duplicates while retaining one record?
Define what “retain” means, rank records within each duplicate group, inspect the rows marked for removal, and only then perform a destructive operation.
-- PostgreSQL: identify duplicate records first
WITH duplicates AS (
SELECT
record_id,
ROW_NUMBER() OVER (
PARTITION BY email
ORDER BY created_at, record_id
) AS rn
FROM users
)
SELECT record_id
FROM duplicates
WHERE rn > 1;The ordering keeps the earliest record, with record_id resolving a timestamp tie. “First row” has no reliable meaning without an ORDER BY. In production, run the identifying query as a reviewable SELECT, use a transaction where supported, and confirm foreign-key implications before deleting.
Do these 3 things before closing this tab:
1Repair Windows errors before they cause bigger problems2Fix the driver behind crashes, sound loss and screen glitches3Clear out junk files and repair common Windows errors12. When should you use EXISTS instead of a join?
Use EXISTS when the question is whether at least one related row exists. It does not multiply the outer row when several matches are present.
-- PostgreSQL
SELECT c.customer_id
FROM customers AS c
WHERE EXISTS (
SELECT 1
FROM orders AS o
WHERE o.customer_id = c.customer_id
AND o.status = 'paid'
);An ordinary join could return the same customer several times if that customer has multiple paid orders. SELECT 1 is merely a convention: EXISTS checks for a returned row, not the selected value.
Pagination interview questions
Pagination syntax is dialect-specific, and every paginated query needs a stable ORDER BY. Without it, the database does not promise which rows appear on a page.
| Database | Example for rows 41–60 |
|---|---|
| PostgreSQL | LIMIT 20 OFFSET 40 |
| MySQL | LIMIT 40, 20 |
| SQL Server | OFFSET 40 ROWS FETCH NEXT 20 ROWS ONLY |
-- PostgreSQL
SELECT product_id, product_name
FROM products
ORDER BY product_id
LIMIT 20 OFFSET 40;-- MySQL
SELECT product_id, product_name
FROM products
ORDER BY product_id
LIMIT 40, 20;-- SQL Server
SELECT product_id, product_name
FROM products
ORDER BY product_id
OFFSET 40 ROWS FETCH NEXT 20 ROWS ONLY;SQL Server also supports TOP:
-- SQL Server
SELECT TOP (20) product_id, product_name
FROM products
ORDER BY product_id;TOP without ORDER BY returns an undefined subset. For large, frequently changing tables, keyset pagination can avoid scanning and skipping a large offset:
Outdated Drivers Are Slowing You Down
One free scan finds every outdated or missing driver and matches the right update for your exact hardware.Free scan · exact hardware matchWindows Errors? Fix Them Before They Spread
Repair common Windows errors and clear accumulated junk for a smoother, more stable PC - no reinstall needed.Free scan · no reinstall-- PostgreSQL
SELECT product_id, product_name
FROM products
WHERE product_id > :last_seen_id
ORDER BY product_id
FETCH FIRST 20 ROWS ONLY;Seek pagination requires a suitable cursor, usually the last ordered key or a composite key. It is fast and stable for next-page navigation, but it is not a drop-in replacement when users need arbitrary page numbers.
Independent reader supportYour contribution helps us test, update, and keep practical guides available for everyone.Common wrong answers interviewers notice
- “The database returns rows in insertion order.” It does not promise that. Use
ORDER BY. - “DISTINCT fixes duplicate joins.” It can hide row multiplication without correcting the join condition or explaining the data problem.
- “ROW_NUMBER() finds the second-highest salary.” It may split tied salaries. Use
DENSE_RANK()when ties should share a rank. - “A LEFT JOIN with a right-table filter in WHERE is still a left join.” That filter removes unmatched null rows.
- “NOT IN and NOT EXISTS are always equivalent.” Nulls in the subquery can make
NOT INproduce surprising results. - “LIMIT, TOP, and FETCH are interchangeable.” They are dialect-specific forms with different syntax and behavior.
- “Adding an index always makes a query faster.” The optimizer weighs selectivity, statistics, table size, ordering, predicates, and estimated cost. Extra indexes also add write and storage overhead.
How to answer query-performance questions
Separate correctness from performance. First demonstrate that the query returns the required rows, including edge cases such as nulls and ties. Then explain how you would validate its cost on representative data.
PostgreSQL
-- PostgreSQL
EXPLAIN
SELECT *
FROM transactions
WHERE customer_id = 42;EXPLAIN ANALYZE executes the query and reports runtime information, so use it carefully, especially with data-modifying statements.
-- PostgreSQL: executes the SELECT
EXPLAIN ANALYZE
SELECT *
FROM transactions
WHERE customer_id = 42;SQL Server
- Open a Database Engine Query in SQL Server Management Studio.
- Enter the query.
- Select Query → Display Estimated Execution Plan to inspect the plan without executing the query.
- Use an actual execution plan when runtime behavior and resource usage are needed.
A strong index answer mentions the columns used in filters, joins, and ordering, but avoids claiming that the optimizer must use a particular index. Confirm the choice with an execution plan, current statistics, realistic row counts, and representative parameter values.
Free tools Windows power users keep installed
One-click scans. No signup required.
FAQ
Which SQL topics should I prioritize for a finance data analyst interview?
Prioritize joins, GROUP BY and HAVING, NULL behavior, conditional aggregation, window functions, duplicate handling, date-based reporting, anti-joins, and execution-plan basics. Practice explaining how the query behaves when a customer has no transactions or when multiple transactions share the same timestamp.
How should I handle SQL dialect differences in an interview?
State the database first, such as PostgreSQL, MySQL, or SQL Server. Then use that database’s syntax for pagination, date functions, null handling, JSON, and ranking. If the interviewer has not specified a dialect, give portable logic and call out the syntax that would change.
Should I use ROW_NUMBER or DENSE_RANK for top-N questions?
Use ROW_NUMBER when the requirement is exactly N rows per group and you have a deterministic tie-breaker. Use DENSE_RANK when all rows tied at the cutoff should be included. Explain this choice rather than presenting one function as universally correct.
What is the safest way to troubleshoot a slow SQL query?
Verify the result first, then inspect the execution plan using tools such as PostgreSQL EXPLAIN or SQL Server’s estimated and actual execution plans. Check row estimates, scans, joins, sorting, statistics, indexes, parameters, and the amount of data processed. Test with representative data instead of assuming an index will solve the problem.
Quick wins for a faster PC:
Scan for outdated or missing drivers - takes under a minuteDriver Scan →Clear out junk files and repair common Windows errorsFree Scan →The Bottom Line
The strongest SQL interview answers explain both the query and its edge cases. Name the dialect, define how nulls and ties should behave, use explicit ordering, protect LEFT JOIN semantics, and distinguish EXISTS from a row-multiplying join. For performance questions, validate the result with an execution plan rather than making blanket claims about indexes.
Quick Recap
Product prices and availability are accurate as of the date/time indicated and are subject to change. Any price and availability information displayed on Amazon at the time of purchase will apply.
