SQL interview questions for data and AI roles in 2026 test whether you can get the right answer from messy tables, explain why a query is slow, and keep data safe when an application or an LLM writes the SQL. Knowing that LEFT JOIN keeps unmatched rows is the entry ticket; interviewers now probe NULL logic, window frames, time zones, isolation levels, upserts, vector search in PostgreSQL and text-to-SQL guardrails. This guide collects 60 high-value questions with model answers, worked queries and live-coding problems.
How to use this guide
Questions are numbered continuously and grouped by topic. Type every query yourself and run it against a small local database before reading the explanation. What interviewers commonly look for at each level:
- Data analysts and freshers: join semantics, NULL behaviour, GROUP BY rules, window functions for ranking and running totals, and clean date filtering.
- Data engineers: deduplication, gaps and islands, sessionisation, upserts, SCD Type 2, partitioning, EXPLAIN plans and transaction behaviour under concurrency.
- AI and ML engineers: pgvector queries with filters, hybrid search in SQL, row-level security, and how to constrain and evaluate SQL generated by an LLM.
SQL is written in ANSI style where possible. PostgreSQL-specific syntax is labelled, because PostgreSQL is the most common engine in live-coding rounds and is the database behind pgvector. If you are interviewing for a warehouse-heavy role, check the dialect notes for Snowflake, BigQuery, Databricks or SQL Server separately. For the broader pipeline and modelling questions, see the companion data engineering interview questions guide; for statistics and experimentation, see the data science interview questions guide.
- Joins and NULL semantics (Q1βQ7)
- Aggregation and GROUP BY traps (Q8βQ11)
- Window functions (Q12βQ18)
- CTEs, recursive CTEs and LATERAL (Q19βQ22)
- Classic patterns (Q23βQ29)
- Dates, times and time zones (Q30βQ32)
- Query performance (Q33βQ38)
- Transactions and isolation (Q39βQ41)
- MERGE, upserts and SCD Type 2 (Q42βQ44)
- SQL for AI work (Q45βQ49)
- Live-coding problems (Q50βQ54)
- Real-world scenarios (Q55βQ60)
- Key takeaways
- Interview preparation checklist
- FAQ
Joins and NULL semantics
1. In what logical order is a SELECT statement evaluated, and why does it matter?
Answer: The logical order is FROM and joins, then WHERE, GROUP BY, HAVING, window functions, SELECT expressions, DISTINCT, ORDER BY, and finally LIMIT/OFFSET (or FETCH FIRST). The optimiser may physically execute things differently, but results must match this order.
It explains most "why does this error?" moments. You cannot reference a SELECT alias in WHERE, because the alias does not exist yet. You cannot filter on a window function in WHERE, because windows are computed after it. ORDER BY can use aliases because it runs last. And DISTINCT is applied after window functions, so SELECT DISTINCT ..., ROW_NUMBER() OVER (...) rarely removes anything.
2. Why does a LEFT JOIN sometimes behave like an INNER JOIN?
Answer: Because a filter on the right-hand table was placed in WHERE instead of ON. Unmatched left rows have NULLs in the right-hand columns, and a condition such as o.status = 'PAID' evaluates to UNKNOWN for them, so WHERE discards them.
-- loses customers with no paid orders
SELECT c.customer_id, o.order_id
FROM customers c
LEFT JOIN orders o ON o.customer_id = c.customer_id
WHERE o.status = 'PAID';
-- keeps every customer
SELECT c.customer_id, o.order_id
FROM customers c
LEFT JOIN orders o
ON o.customer_id = c.customer_id
AND o.status = 'PAID';Rule of thumb: conditions on the preserved (left) table go in WHERE; conditions that decide which right-hand rows match go in ON. The exception is deliberate: WHERE o.order_id IS NULL after a left join is an anti-join.
3. What are semi-joins and anti-joins, and why is NOT IN dangerous?
Answer: A semi-join returns left rows that have at least one match, without duplicating them; an anti-join returns left rows with no match. Write semi-joins with EXISTS or IN, and anti-joins with NOT EXISTS or a left join plus IS NULL.
-- semi-join: customers with at least one order
SELECT c.* FROM customers c
WHERE EXISTS (SELECT 1 FROM orders o
WHERE o.customer_id = c.customer_id);
-- anti-join: safe even if orders.customer_id has NULLs
SELECT c.* FROM customers c
WHERE NOT EXISTS (SELECT 1 FROM orders o
WHERE o.customer_id = c.customer_id);NOT IN breaks when the subquery returns a single NULL. x NOT IN (1, 2, NULL) means x <> 1 AND x <> 2 AND x <> NULL, and the last term is UNKNOWN, so the whole predicate is never TRUE and the query returns zero rows. In PostgreSQL there is also a performance reason: the planner can turn NOT EXISTS into a hash anti-join, while NOT IN over a large subquery cannot be planned that way.
Interview tip: Joining instead of using EXISTS for a semi-join duplicates customers with many orders. Saying this unprompted shows you think about grain.
4. Explain three-valued logic. How do you compare two values that may both be NULL?
Answer: SQL predicates return TRUE, FALSE or UNKNOWN. Any comparison with NULL, including NULL = NULL, is UNKNOWN. WHERE, HAVING and join conditions keep only TRUE rows; CHECK constraints reject only FALSE, so a NULL passes a check. NOT UNKNOWN is still UNKNOWN, and TRUE OR UNKNOWN is TRUE.
To compare nullable values, use the standard IS [NOT] DISTINCT FROM, which treats two NULLs as equal:
-- detect real changes between staging and target
SELECT s.customer_id
FROM staging s
JOIN dim_customer d ON d.customer_id = s.customer_id
WHERE s.email IS DISTINCT FROM d.email
OR s.phone IS DISTINCT FROM d.phone;With plain <>, a change from NULL to a value would be missed. PostgreSQL, SQLite and recent SQL Server versions support IS DISTINCT FROM; MySQL uses the <=> null-safe equality operator instead.
5. How do NULLs affect aggregates, sorting and unique constraints?
Answer: Aggregates other than COUNT(*) ignore NULLs. AVG(score) is the average of non-NULL scores, not of all rows; if missing means zero, write AVG(COALESCE(score, 0)) and say so. Over an empty set, COUNT returns 0 but SUM, AVG, MIN and MAX return NULL, which is why dashboards show blanks for days with no sales. Wrap in COALESCE(SUM(x), 0) where a zero is correct.
Sorting differs by engine: PostgreSQL and Oracle treat NULL as larger than any value (last in ascending order), while SQL Server, MySQL and SQLite put NULLs first. Use explicit NULLS FIRST/NULLS LAST where supported. For uniqueness, the standard allows many NULLs in a unique column because NULLs are not equal; SQL Server's unique constraint allows only one. PostgreSQL 15 and later let you declare UNIQUE NULLS NOT DISTINCT when you want at most one NULL.
6. Revenue doubled on a dashboard after someone added a payments table to the query. What went wrong?
Answer: A fan-out, specifically the "chasm trap": two independent one-to-many tables joined to the same parent. An order with two items and two payments produces four rows, so both item revenue and payment totals are inflated.
What I would check:
- The grain of each table: one row per order, per item, per payment?
- Row counts before and after each join.
- Whether
SUM(DISTINCT ...)was used as a "fix"; it silently drops legitimately equal amounts.
The fix is to aggregate each child to the parent's grain first, then join:
WITH items AS (
SELECT order_id, SUM(qty * unit_price) AS item_total
FROM order_items GROUP BY order_id
), pays AS (
SELECT order_id, SUM(amount) AS paid_total
FROM payments GROUP BY order_id
)
SELECT o.order_id, i.item_total,
COALESCE(p.paid_total, 0) AS paid_total
FROM orders o
JOIN items i ON i.order_id = o.order_id
LEFT JOIN pays p ON p.order_id = o.order_id;Production consideration: Add a data test that the output has exactly one row per order_id. Fan-outs are rarely caught by eye.
7. Two systems should hold the same transactions but totals differ. How do you reconcile them in SQL?
Answer: Compare at row level, not at total level. EXCEPT in both directions shows rows present in one source and not the other; a full outer join on the business key shows missing rows and value mismatches together.
SELECT COALESCE(a.txn_id, b.txn_id) AS txn_id,
a.amount AS core_amount,
b.amount AS ledger_amount,
CASE WHEN a.txn_id IS NULL THEN 'missing_in_core'
WHEN b.txn_id IS NULL THEN 'missing_in_ledger'
ELSE 'amount_mismatch' END AS issue
FROM core_txn a
FULL OUTER JOIN ledger_txn b ON b.txn_id = a.txn_id
WHERE a.txn_id IS NULL OR b.txn_id IS NULL
OR a.amount IS DISTINCT FROM b.amount;Use UNION ALL when stacking sources, never plain UNION: UNION removes duplicate rows, which can hide exactly the double-postings you are looking for. Before blaming data, align the definitions: cut-off time and time zone, reversals, and which statuses count.
Aggregation and GROUP BY traps
8. Can you select a column that is not in GROUP BY?
Answer: In standard SQL, only if it is aggregated or functionally dependent on the grouping columns. PostgreSQL implements the dependency rule for primary keys: if you GROUP BY c.customer_id and that is the primary key of customers, you may select c.name. MySQL with ONLY_FULL_GROUP_BY disabled, and SQLite, accept non-grouped columns and return a value from an arbitrary row in the group, which is a source of silently wrong reports.
When you want "the value from the latest row" per group, do not rely on that behaviour. Use a window function and filter, DISTINCT ON in PostgreSQL, or a LATERAL subquery (Q22).
9. What do GROUPING SETS, ROLLUP and CUBE do?
Answer: They compute several groupings in one pass. ROLLUP(region, city) produces (region, city), (region) and the grand total. CUBE(a, b) produces every combination. GROUPING SETS lists exactly the combinations you want.
SELECT region, city,
SUM(amount) AS revenue,
GROUPING(city) AS is_city_subtotal
FROM sales
GROUP BY ROLLUP (region, city)
ORDER BY region, city;Subtotal rows show NULL in the rolled-up column. Use GROUPING() to tell a subtotal NULL from a genuine NULL city, otherwise the report labels a data-quality problem as a subtotal. This is supported in PostgreSQL, SQL Server, Oracle, Snowflake, BigQuery and Spark SQL; MySQL supports WITH ROLLUP only.
10. What is wrong with averaging a ratio column, and how does the FILTER clause help?
Answer: The average of per-group ratios is not the overall ratio. If store A converts 1 of 2 visitors and store B converts 100 of 1,000, averaging the two rates gives 30 percent, but the true conversion is about 10 percent. Compute ratios as a sum over a sum at the grain you report.
SELECT store_id,
COUNT(*) FILTER (WHERE converted) * 1.0
/ NULLIF(COUNT(*), 0) AS conversion_rate,
COUNT(*) FILTER (WHERE channel = 'app')
AS app_visits
FROM visits
GROUP BY store_id;FILTER (WHERE ...) is standard SQL supported by PostgreSQL and SQLite; elsewhere use SUM(CASE WHEN ... THEN 1 ELSE 0 END). NULLIF(denominator, 0) turns division by zero into NULL rather than an error. Multiplying by 1.0 avoids integer division in PostgreSQL and SQL Server.
11. How do you compute a median or a percentile?
Answer: With ordered-set aggregates: PERCENTILE_CONT(0.5) WITHIN GROUP (ORDER BY x) interpolates between the two middle values; PERCENTILE_DISC(0.5) returns an actual value from the data. Use DISC for things that must be real values (a delivery slot), CONT for continuous measures (latency).
-- PostgreSQL, SQL Server (as window), Oracle, Snowflake
SELECT city,
PERCENTILE_CONT(0.5) WITHIN GROUP
(ORDER BY delivery_minutes) AS p50,
PERCENTILE_CONT(0.95) WITHIN GROUP
(ORDER BY delivery_minutes) AS p95
FROM deliveries
GROUP BY city;Dialect notes: PostgreSQL does not allow ordered-set aggregates as window functions, while SQL Server only offers PERCENTILE_CONT as a window function. BigQuery uses APPROX_QUANTILES or PERCENTILE_CONT as an analytic function.
Window functions
12. How is a window function different from GROUP BY, and how do you filter on one?
Answer: GROUP BY collapses rows into one per group; a window function computes a value across related rows and keeps every row. Because windows are computed after WHERE and HAVING, you filter on them in an outer query:
SELECT * FROM (
SELECT t.*,
ROW_NUMBER() OVER (PARTITION BY account_id
ORDER BY txn_ts DESC,
txn_id DESC) AS rn
FROM transactions t
) x
WHERE rn = 1;Snowflake, BigQuery, Databricks and DuckDB support QUALIFY rn = 1 to skip the wrapper; PostgreSQL and SQL Server do not. Note the second ordering column: without a unique tiebreaker, the "latest" row can change between runs.
13. Explain ROWS, RANGE and GROUPS frames. Why does LAST_VALUE often return the current row?
Answer: The frame decides which rows inside the partition a window aggregate sees. ROWS counts physical rows. RANGE works on the value of the ORDER BY column, so rows with equal values (peers) are included together. GROUPS counts peer groups. When a window has ORDER BY but no frame, the default is RANGE BETWEEN UNBOUNDED PRECEDING AND CURRENT ROW.
That default is why LAST_VALUE(x) OVER (ORDER BY ts) returns the current row's value (or its last peer): the frame ends at the current row. Specify the full frame:
SELECT order_id, status, status_ts,
LAST_VALUE(status) OVER (
PARTITION BY order_id ORDER BY status_ts
ROWS BETWEEN UNBOUNDED PRECEDING
AND UNBOUNDED FOLLOWING
) AS final_status
FROM order_status_history;The same default makes running totals jump on tied timestamps under RANGE. Using FIRST_VALUE with a descending order is often clearer than LAST_VALUE. GROUPS and RANGE with offsets are available in PostgreSQL 11 and later.
14. Compute a 7-day moving average when some days have no data.
Answer: ROWS BETWEEN 6 PRECEDING AND CURRENT ROW averages the last seven rows, which spans more than seven days if days are missing. Either join to a complete date spine first (Q21) or use a value-based RANGE frame:
-- PostgreSQL 11+: RANGE with an interval offset
SELECT sales_date, revenue,
AVG(revenue) OVER (
ORDER BY sales_date
RANGE BETWEEN INTERVAL '6 days' PRECEDING
AND CURRENT ROW
) AS avg_7d
FROM daily_sales;The two are not identical. The RANGE version averages only the days that exist, so a missing day is ignored rather than counted as zero. If a missing day really means zero sales, the date spine with COALESCE(revenue, 0) is correct. Ask the interviewer which interpretation the business wants; that question is part of the answer.
15. How do you use LAG and LEAD, and how do you forward-fill missing values?
Answer: LAG(x, n, default) reads a value n rows earlier in the window; LEAD reads later. Typical uses: change since last reading, time between events, and the next status.
Forward-filling ("carry the last known price") needs the last non-NULL value. Some engines support LAG(x) IGNORE NULLS or LAST_VALUE(x IGNORE NULLS); PostgreSQL has not traditionally supported IGNORE NULLS, so check your version. A portable trick is to build a group number that increments at each non-NULL value:
WITH g AS (
SELECT sensor_id, reading_ts, temp_c,
COUNT(temp_c) OVER (PARTITION BY sensor_id
ORDER BY reading_ts) AS grp
FROM readings
)
SELECT sensor_id, reading_ts,
MAX(temp_c) OVER (PARTITION BY sensor_id, grp)
AS temp_filled
FROM g;COUNT(temp_c) ignores NULLs, so every NULL row shares the group of the last non-NULL row, and the group's only non-NULL value is the one to carry forward.
16. Show each product's share of its category's revenue, and split customers into spending deciles.
Answer: A window aggregate without ORDER BY covers the whole partition, which gives you the denominator on every row. NTILE(10) distributes ordered rows into ten buckets of near-equal size.
SELECT category, product_id, revenue,
revenue * 1.0 / SUM(revenue) OVER
(PARTITION BY category) AS share_of_category
FROM product_revenue;
SELECT customer_id, total_spend,
NTILE(10) OVER (ORDER BY total_spend DESC)
AS spend_decile
FROM customer_spend;NTILE splits by row count, so customers with identical spend can land in different deciles. If ties must stay together, use PERCENT_RANK() or CUME_DIST() and bucket the result yourself.
17. How do you compute a running count of distinct customers?
Answer: Most engines, including PostgreSQL, reject COUNT(DISTINCT ...) OVER (ORDER BY ...). Flag each customer's first appearance, then take a running sum of the flag:
WITH firsts AS (
SELECT customer_id, MIN(order_date) AS first_date
FROM orders GROUP BY customer_id
)
SELECT first_date,
COUNT(*) AS new_customers,
SUM(COUNT(*)) OVER (ORDER BY first_date)
AS cumulative_customers
FROM firsts
GROUP BY first_date;Note SUM(COUNT(*)) OVER (...): a window function over an aggregate, valid because windows run after grouping.
18. What makes window queries slow or non-deterministic?
Answer: Each distinct PARTITION BY/ORDER BY combination usually needs its own sort. Five windows with five different orderings mean five sorts of the data. Reuse one definition with the standard WINDOW clause where you can, and an index matching (partition_cols, order_cols) can let PostgreSQL skip the sort entirely. Sorts that exceed work_mem spill to disk; EXPLAIN ANALYZE shows this as an external merge.
SELECT account_id, txn_ts, amount,
SUM(amount) OVER w AS running_balance,
COUNT(*) OVER w AS txn_number
FROM transactions
WINDOW w AS (PARTITION BY account_id
ORDER BY txn_ts, txn_id
ROWS UNBOUNDED PRECEDING);Non-determinism comes from ties. ROW_NUMBER, LAG and FIRST_VALUE over a non-unique ordering can give different answers on reruns or on another node. Always end the ordering with a unique key in pipelines.
CTEs, recursive CTEs and LATERAL
19. When would you use a CTE, a subquery or a temporary table?
Answer: CTEs are for readability and for referencing the same intermediate result several times; subqueries are fine for small, local logic; temporary tables are for large intermediate results you want to index, analyse or reuse across several statements.
The PostgreSQL detail interviewers probe: before version 12, every CTE was an optimisation fence, materialised and not pushed into. Since version 12, a non-recursive, side-effect-free CTE referenced once is inlined like a subquery. You can force either behaviour with WITH x AS MATERIALIZED (...) or NOT MATERIALIZED. Materialising helps when an expensive CTE is referenced several times; inlining helps when outer filters should reach the base table's indexes.
20. Write a recursive CTE that returns an org hierarchy with depth and path, safely.
Answer: A recursive CTE has an anchor member, UNION ALL, and a recursive member that joins back to the CTE. Track depth and the path, and stop on cycles, because bad data (an employee who is their own manager's manager) otherwise loops until a limit or timeout.
-- PostgreSQL: path as an array for cycle checks
WITH RECURSIVE org AS (
SELECT emp_id, manager_id, name, 1 AS depth,
ARRAY[emp_id] AS path
FROM employees
WHERE manager_id IS NULL
UNION ALL
SELECT e.emp_id, e.manager_id, e.name,
o.depth + 1, o.path || e.emp_id
FROM employees e
JOIN org o ON e.manager_id = o.emp_id
WHERE e.emp_id <> ALL (o.path)
AND o.depth < 20
)
SELECT * FROM org ORDER BY path;PostgreSQL 14 and later also offer the standard CYCLE and SEARCH clauses. In engines without arrays, build a delimited string path. SQL Server spells it WITH without RECURSIVE and has a default recursion limit of 100.
21. How do you generate a date spine and report zero for days with no activity?
Answer: Generate every date in the range, then left join facts to it. PostgreSQL has generate_series; a recursive CTE is the portable version.
-- PostgreSQL
SELECT d::date AS day,
COALESCE(SUM(o.amount), 0) AS revenue
FROM generate_series(DATE '2026-09-01',
DATE '2026-09-30',
INTERVAL '1 day') AS d
LEFT JOIN orders o
ON o.order_date = d::date
GROUP BY d
ORDER BY d;
-- portable recursive version
WITH RECURSIVE days (day) AS (
SELECT DATE '2026-09-01'
UNION ALL
SELECT CAST(day + INTERVAL '1' DAY AS DATE)
FROM days
WHERE day < DATE '2026-09-30'
)
SELECT day FROM days;Most teams keep a permanent calendar table with fiscal periods, holidays and week numbers. That is better than generating dates in every query, especially when Indian fiscal years (April to March) or regional holiday calendars matter.
22. What is a LATERAL join, and when should you use it to get the top N rows per group?
Answer: LATERAL (standard SQL, supported in PostgreSQL, MySQL 8, Snowflake and others; SQL Server uses CROSS APPLY) lets a subquery in FROM reference columns of tables to its left. It runs, logically, once per outer row.
-- latest 3 orders per customer
SELECT c.customer_id, o.order_id, o.order_ts
FROM customers c
CROSS JOIN LATERAL (
SELECT order_id, order_ts
FROM orders
WHERE orders.customer_id = c.customer_id
ORDER BY order_ts DESC
LIMIT 3
) o;With an index on orders (customer_id, order_ts DESC), each lookup reads only three index entries, which beats a ROW_NUMBER window that must sort the whole orders table when there are few customers and many orders. When you need every customer's rank anyway, or the table is scanned regardless, the window version is simpler. Use LEFT JOIN LATERAL ... ON true to keep customers with no orders. PostgreSQL's DISTINCT ON (customer_id) is the shortest way to get exactly one row per group.
Classic patterns
23. Delete exact duplicate rows from a PostgreSQL table that has no primary key.
Answer: Every PostgreSQL row has a hidden physical locator, ctid, which distinguishes otherwise identical rows. Keep one per duplicate set and delete the rest, then add the constraint that should have existed.
-- PostgreSQL-specific
DELETE FROM payments p
USING (
SELECT ctid,
ROW_NUMBER() OVER (
PARTITION BY txn_ref, amount, paid_at
ORDER BY ctid) AS rn
FROM payments
) d
WHERE p.ctid = d.ctid AND d.rn > 1;
ALTER TABLE payments
ADD CONSTRAINT payments_txn_ref_uk UNIQUE (txn_ref);ctid changes when a row is updated or the table is rewritten, so use it only inside one statement, never as an identifier. On a large table, copying the distinct rows into a new table and swapping is often faster and produces less bloat than a mass delete.
24. Find the missing numbers in an invoice sequence.
Answer: Compare each number with the next one using LEAD; a difference greater than one is a gap.
SELECT invoice_no + 1 AS gap_start,
next_no - 1 AS gap_end
FROM (
SELECT invoice_no,
LEAD(invoice_no) OVER (ORDER BY invoice_no)
AS next_no
FROM invoices
) t
WHERE next_no - invoice_no > 1;This returns ranges rather than one row per missing number, which stays small even for large gaps. Partition by series (branch, financial year) where numbering restarts. Remember that database sequences are not gap-free by design: rolled-back transactions consume values. If auditors need gap-free invoice numbers, allocate them from a counter row updated inside the transaction, accepting the serialisation cost.
25. Turn a log of device status readings into intervals of continuous status.
Answer: This is gaps and islands on a changing value rather than on consecutive dates. Mark rows where the status differs from the previous one, take a running sum of the marks to number the islands, then aggregate.
WITH marked AS (
SELECT device_id, reading_ts, status,
CASE WHEN status = LAG(status) OVER (
PARTITION BY device_id ORDER BY reading_ts)
THEN 0 ELSE 1 END AS is_change
FROM device_status
), islands AS (
SELECT m.*,
SUM(is_change) OVER (PARTITION BY device_id
ORDER BY reading_ts)
AS island_id
FROM marked m
)
SELECT device_id, status,
MIN(reading_ts) AS from_ts,
MAX(reading_ts) AS to_ts,
COUNT(*) AS readings
FROM islands
GROUP BY device_id, island_id, status
ORDER BY device_id, from_ts;The first row per device has a NULL LAG; the comparison is UNKNOWN, so the CASE falls through to 1 and starts the first island, which is what you want. If status itself can be NULL, compare with IS NOT DISTINCT FROM.
26. Sessionise clickstream events with a 30-minute inactivity timeout.
Answer: Same technique: a new session starts when the gap since the user's previous event exceeds 30 minutes (or there is no previous event). A running sum of new-session flags gives the session number.
-- PostgreSQL interval syntax
WITH flagged AS (
SELECT user_id, event_ts, page,
CASE WHEN event_ts - LAG(event_ts) OVER (
PARTITION BY user_id ORDER BY event_ts)
<= INTERVAL '30 minutes'
THEN 0 ELSE 1 END AS new_session
FROM events
)
SELECT user_id, event_ts, page,
SUM(new_session) OVER (PARTITION BY user_id
ORDER BY event_ts
ROWS UNBOUNDED PRECEDING)
AS session_no
FROM flagged;Discuss the edges: whether a session should also break at midnight or on a campaign change, how to handle events that arrive late (sessions in already-published tables change), and that bots with constant activity create endless sessions, so a maximum session length is often added.
27. How do you pivot and unpivot in SQL?
Answer: Portable pivoting is conditional aggregation: one aggregate per output column.
SELECT store_id,
SUM(CASE WHEN channel = 'store' THEN amount END)
AS store_sales,
SUM(CASE WHEN channel = 'app' THEN amount END)
AS app_sales,
SUM(CASE WHEN channel = 'web' THEN amount END)
AS web_sales
FROM sales
GROUP BY store_id;
-- unpivot with LATERAL VALUES (PostgreSQL)
SELECT s.store_id, v.channel, v.amount
FROM store_channel_sales s
CROSS JOIN LATERAL (VALUES
('store', s.store_sales),
('app', s.app_sales),
('web', s.web_sales)) AS v(channel, amount);SQL Server, Oracle, Snowflake, BigQuery and Databricks have PIVOT/UNPIVOT operators; PostgreSQL has crosstab in the tablefunc extension. All of them need the output columns known in advance. A dynamic set of columns means generating SQL, or pivoting in the BI tool or in Python instead.
28. Find overlapping bookings, then merge overlapping intervals.
Answer: Two intervals overlap when each starts before the other ends: a.start_ts < b.end_ts AND b.start_ts < a.end_ts (half-open intervals). To merge, sort by start and start a new group whenever an interval begins after the maximum end seen so far:
WITH ordered AS (
SELECT room_id, start_ts, end_ts,
MAX(end_ts) OVER (
PARTITION BY room_id ORDER BY start_ts, end_ts
ROWS BETWEEN UNBOUNDED PRECEDING
AND 1 PRECEDING) AS prev_max_end
FROM bookings
), grouped AS (
SELECT o.*,
SUM(CASE WHEN prev_max_end >= start_ts
THEN 0 ELSE 1 END)
OVER (PARTITION BY room_id
ORDER BY start_ts, end_ts) AS grp
FROM ordered o
)
SELECT room_id, MIN(start_ts) AS merged_start,
MAX(end_ts) AS merged_end
FROM grouped
GROUP BY room_id, grp;Using MAX rather than LAG(end_ts) matters: a long booking can cover several later short ones. PostgreSQL also has range types and an exclusion constraint (EXCLUDE USING gist (room_id WITH =, during WITH &&), with the btree_gist extension) that prevents overlaps at write time, which is better than finding them later.
29. Which customers bought every product in a given set?
Answer: This is relational division. The readable version counts distinct matches and compares with the size of the set:
SELECT o.customer_id
FROM orders o
JOIN required_products r ON r.product_id = o.product_id
GROUP BY o.customer_id
HAVING COUNT(DISTINCT o.product_id) =
(SELECT COUNT(*) FROM required_products);The classic alternative is a double NOT EXISTS: customers for whom there is no required product they have not bought. The counting version breaks if required_products contains duplicates, so COUNT(DISTINCT) on both sides is the safe habit. An empty required set is another edge case: logically every customer qualifies, but the join version returns nobody.
If you want to practise these patterns on realistic pipelines rather than toy tables, Cloudsoft's HORIZON Data Engineering & AI program covers SQL, modelling and pipeline work alongside the data foundations AI systems need.
Dates, times and time zones
30. What is the difference between timestamp and timestamptz in PostgreSQL?
Answer: timestamp (without time zone) stores a wall-clock reading with no zone; timestamptz stores an absolute instant, internally in UTC, and converts to the session's TimeZone setting on display. Despite the name, timestamptz does not store the original zone.
-- instant to Indian wall-clock time
SELECT created_at AT TIME ZONE 'Asia/Kolkata'
FROM orders; -- timestamptz in, timestamp out
-- wall-clock reading interpreted in a zone
SELECT TIMESTAMP '2026-10-07 09:00'
AT TIME ZONE 'America/New_York';
-- timestamp in, timestamptz outUse timestamptz for events. Use timestamp or date for things that are genuinely local, such as a store's opening time, and store the zone name in a separate column when you need it. Use region names like Asia/Kolkata, not fixed offsets, so daylight saving rules are applied.
31. A daily sales report for India must count orders by IST date, but timestamps are in UTC. How do you write it, and why is the obvious version slow?
Answer: IST is UTC+05:30, so an IST day runs from 18:30 UTC the previous day. Group by the IST date, but filter with a range on the raw column so an index or partition pruning can be used.
-- slow: function on the column in WHERE
WHERE (created_at AT TIME ZONE 'Asia/Kolkata')::date
= DATE '2026-10-06'
-- sargable: compare the raw column with constants
SELECT (created_at AT TIME ZONE 'Asia/Kolkata')::date
AS ist_date,
SUM(amount) AS revenue
FROM orders
WHERE created_at >= TIMESTAMPTZ '2026-10-06 00:00+05:30'
AND created_at < TIMESTAMPTZ '2026-10-07 00:00+05:30'
GROUP BY 1;What I would check:
- Whether the column really holds UTC instants, or local time stored in a
timestampcolumn by an application. - The session time zone of the BI tool and the ETL job; they often differ.
- Whether finance's cut-off is midnight IST or a business cut-off such as a settlement time.
Production consideration: Store a derived ist_date (or business_date) column at load time when most reports use it, and partition on it.
32. What goes wrong with date arithmetic across daylight saving changes and month ends?
Answer: India has no daylight saving time, but GCC teams in Hyderabad and Bengaluru routinely report for US, UK, European and Australian business units that do. In those zones a local day can have 23 or 25 hours.
- In PostgreSQL, adding
INTERVAL '1 day'to atimestamptzkeeps the same wall-clock time in the session zone across a DST change; addingINTERVAL '24 hours'adds exactly 24 hours. Choose deliberately. - Group local days with
date_trunc('day', ts, 'Europe/London')(the three-argument form exists in PostgreSQL 12 and later) or by converting withAT TIME ZONEfirst. Grouping by UTC date shifts transactions near midnight into the wrong local day. - Month arithmetic: in PostgreSQL,
DATE '2026-01-31' + INTERVAL '1 month'gives 28 February. Adding a month and then subtracting one does not return the start date. BETWEEN '2026-09-01' AND '2026-09-30'on a timestamp column excludes everything after midnight on 30 September. Use half-open ranges:>=start and<next day.
Query performance
33. How do you read EXPLAIN and EXPLAIN ANALYZE output?
Answer: EXPLAIN shows the plan the optimiser chose, with estimated costs and row counts. EXPLAIN ANALYZE actually runs the query and adds real timings and row counts per node; in PostgreSQL 18 it also includes buffer statistics by default (earlier versions need BUFFERS). Because it runs the statement, wrap data-changing statements in a transaction you roll back.
What to look for, in order:
- The node where most time is spent (times are cumulative, so subtract children).
- Estimated rows versus actual rows. An estimate off by orders of magnitude is the root cause of most bad plans: the wrong join algorithm or join order follows.
- Sequential scans on large tables returning few rows, which suggests a missing or unusable index.
- Sorts or hashes spilling to disk ("external merge", multiple hash batches).
- Nested loops with a large outer side, and
loopscounts in the thousands. - Rows removed by filter: the scan read far more than it kept.
34. What does sargable mean? Give examples of predicates that block index use.
Answer: A sargable predicate (Search ARGument ABLE) can be answered by an index seek or range scan because the indexed column appears bare on one side. Common non-sargable patterns and their rewrites:
| Blocks the index | Sargable rewrite |
|---|---|
WHERE EXTRACT(YEAR FROM order_date) = 2026 | order_date >= DATE '2026-01-01' AND order_date < DATE '2027-01-01' |
WHERE LOWER(email) = 'a@x.in' | Expression index on LOWER(email), or a case-insensitive column type or collation |
WHERE amount * 1.18 > 1000 | amount > 1000 / 1.18 |
WHERE phone = 9876543210 on a text column | Compare with a string literal; implicit casts on the column side prevent index use |
WHERE name LIKE '%kumar' | Trigram index (pg_trgm) or full-text search; a B-tree only helps prefixes |
WHERE COALESCE(region, 'NA') = 'South' | region = 'South' (NULLs never equal 'South' anyway) |
In PostgreSQL, a B-tree index supports LIKE 'abc%' only with the C collation or a text_pattern_ops operator class.
35. How do you design a composite index? What are covering and partial indexes?
Answer: For a B-tree on (a, b, c), put equality-filtered columns first, then the range or sort column. An index on (customer_id, order_ts) serves WHERE customer_id = ? ORDER BY order_ts DESC LIMIT 10 with no sort. Traditionally, a filter on only b could not use that index well; PostgreSQL 18 added skip scan, which helps when the first column has few distinct values, but column order still matters.
-- covering: answer from the index alone
CREATE INDEX orders_cust_ts_idx
ON orders (customer_id, order_ts DESC)
INCLUDE (status, amount);
-- partial: index only the rows queries touch
CREATE INDEX orders_open_idx
ON orders (created_at)
WHERE status IN ('NEW', 'PENDING');INCLUDE (PostgreSQL 11 and later, SQL Server) adds payload columns so the query can use an index-only scan; in PostgreSQL that also depends on the visibility map being current, which vacuum maintains. Partial indexes stay small when queries always target a small subset, such as open tickets. Every index slows writes and takes space, so justify each one with a query.
36. Explain nested loop, hash and merge joins. When does each win?
Answer:
- Nested loop: for each outer row, look up matching inner rows. Excellent when the outer side is small and the inner side has an index on the join key. Disastrous when the planner wrongly thinks the outer side is small.
- Hash join: build a hash table on the smaller input, then probe it with the larger. The usual choice for large, unsorted equi-joins. Needs memory; spills to disk when the build side exceeds it.
- Merge join: both inputs sorted on the key, then walked together. Good when inputs are already sorted (from an index or an earlier step) and large, and its memory use stays low.
In PostgreSQL, a sudden switch to a nested loop with a huge loop count usually traces back to a bad row estimate (Q33), not to the join algorithm itself.
37. When does table partitioning help, and when does it hurt?
Answer: Partitioning splits one logical table into pieces by a key, most often a date range. It helps when queries filter on the partition key, because the planner prunes partitions it does not need, and when data has a lifecycle: dropping or detaching an old monthly partition is instant compared with deleting millions of rows.
-- PostgreSQL declarative partitioning
CREATE TABLE events (
event_id bigint NOT NULL,
event_ts timestamptz NOT NULL,
user_id bigint,
payload jsonb,
PRIMARY KEY (event_id, event_ts)
) PARTITION BY RANGE (event_ts);
CREATE TABLE events_2026_10 PARTITION OF events
FOR VALUES FROM ('2026-10-01') TO ('2026-11-01');It hurts when queries do not filter on the key (every partition is scanned, plus planning overhead), when there are thousands of tiny partitions, or when the key is chosen for one report and fights every other query. In PostgreSQL, primary keys and unique constraints must include the partition key, which surprises people.
38. Why is OFFSET pagination slow on deep pages, and what is keyset pagination?
Answer: OFFSET 100000 LIMIT 50 still reads and discards the first 100,000 rows, so each page gets slower, and rows inserted between requests shift pages, causing duplicates or skipped rows. Keyset (seek) pagination remembers the last row's sort key and asks for rows after it:
SELECT order_id, created_at, amount
FROM orders
WHERE (created_at, order_id) < (:last_ts, :last_id)
ORDER BY created_at DESC, order_id DESC
LIMIT 50;With an index on (created_at, order_id) every page costs the same. The row-value comparison is standard SQL and works in PostgreSQL and MySQL; elsewhere expand it to created_at < :last_ts OR (created_at = :last_ts AND order_id < :last_id). The same technique is how you should page through results an AI tool fetches from a database.
Transactions and isolation
39. Explain the isolation levels and the anomalies each one prevents. What is different in PostgreSQL?
Answer: The standard defines four levels by the anomalies they forbid:
| Level | Dirty read | Non-repeatable read | Phantom |
|---|---|---|---|
| Read Uncommitted | Possible | Possible | Possible |
| Read Committed | Prevented | Possible | Possible |
| Repeatable Read | Prevented | Prevented | Possible |
| Serializable | Prevented | Prevented | Prevented |
PostgreSQL uses MVCC and implements three distinct levels: Read Uncommitted behaves as Read Committed (the default); Repeatable Read is snapshot isolation and also prevents phantoms; Serializable adds Serializable Snapshot Isolation, which detects dangerous patterns such as write skew and aborts one transaction with SQLSTATE 40001. Applications using Repeatable Read or Serializable must retry the whole transaction on that error.
Real-world example: Write skew: two on-call doctors each check "is someone else on call?" and both go off call. Each transaction is valid alone; together they break the rule. Only Serializable, or an explicit lock on the rows that encode the rule, prevents it.
40. How do you prevent lost updates, and how do you build a job queue in SQL?
Answer: A lost update happens when two sessions read a value, compute in the application and write back, and one overwrites the other. Options, from simplest:
- Make the update atomic in SQL:
UPDATE wallets SET balance = balance - 500 WHERE wallet_id = 42 AND balance >= 500, then check the affected row count. - Pessimistic locking:
SELECT ... FOR UPDATEinside the transaction before computing. - Optimistic locking: a
versioncolumn;UPDATE ... SET version = version + 1 WHERE id = ? AND version = ?and retry when zero rows change.
For a queue of work items processed by several workers, PostgreSQL's FOR UPDATE SKIP LOCKED lets each worker claim different rows without blocking:
UPDATE jobs SET status = 'RUNNING',
started_at = now()
WHERE job_id IN (
SELECT job_id FROM jobs
WHERE status = 'QUEUED'
ORDER BY created_at
LIMIT 10
FOR UPDATE SKIP LOCKED
)
RETURNING job_id, payload;This is a common pattern for document-embedding backlogs in AI pipelines: workers pull batches of chunks to embed without a separate message broker.
41. What causes deadlocks, and how do you prevent them?
Answer: A deadlock occurs when transaction A holds a lock B needs while B holds a lock A needs. PostgreSQL detects it after deadlock_timeout and aborts one transaction with SQLSTATE 40P01; other engines behave similarly.
Prevention: acquire locks in a consistent order (for example, always update accounts in ascending account_id order in a transfer), keep transactions short, avoid user interaction or network calls inside a transaction, and index foreign keys so that deletes on a parent do not lock or scan the child broadly. Batch updates that touch overlapping rows in different orders are a classic source; sorting the batch by key before applying it often removes the deadlocks entirely. Retry on 40P01 as you would on a serialisation failure.
MERGE, upserts and SCD Type 2
42. Compare INSERT ... ON CONFLICT with MERGE in PostgreSQL.
Answer: INSERT ... ON CONFLICT (key) DO UPDATE (PostgreSQL 9.5 and later, also SQLite) is an insert that falls back to an update when a unique constraint would be violated. It needs a unique index on the conflict target and behaves predictably under concurrent inserts of the same key: one session inserts, the other updates.
INSERT INTO product_price (sku, price, updated_at)
VALUES ('SKU-1', 499.00, now())
ON CONFLICT (sku) DO UPDATE
SET price = EXCLUDED.price,
updated_at = EXCLUDED.updated_at
WHERE product_price.price
IS DISTINCT FROM EXCLUDED.price;MERGE is the standard statement (PostgreSQL 15 and later; version 17 added RETURNING and WHEN NOT MATCHED BY SOURCE). It joins a source to a target and can insert, update and delete in one statement, which suits batch synchronisation. Under concurrent inserts it follows normal isolation rules and can still fail with a unique violation, so PostgreSQL's documentation points to ON CONFLICT for that case. Both fail if the source contains two rows for one target key ("cannot affect row a second time"), so deduplicate the source first.
43. Write the SQL to maintain an SCD Type 2 dimension.
Answer: Each version of a customer gets its own row with valid_from, valid_to and an is_current flag. In one transaction: close the current row when tracked attributes changed, then insert new versions for changed and brand-new keys.
BEGIN;
UPDATE dim_customer d
SET valid_to = s.extract_ts, is_current = FALSE
FROM stg_customer s
WHERE d.customer_id = s.customer_id
AND d.is_current
AND (d.city IS DISTINCT FROM s.city
OR d.segment IS DISTINCT FROM s.segment);
INSERT INTO dim_customer
(customer_id, city, segment,
valid_from, valid_to, is_current)
SELECT s.customer_id, s.city, s.segment,
s.extract_ts, TIMESTAMP '9999-12-31', TRUE
FROM stg_customer s
LEFT JOIN dim_customer d
ON d.customer_id = s.customer_id AND d.is_current
WHERE d.customer_id IS NULL;
COMMIT;The insert works because, after the update, changed customers no longer have a current row, so the anti-join picks them up together with new customers. Points interviewers test: IS DISTINCT FROM so NULL changes are detected; a hash of tracked columns when there are many; deduplicating staging so one key has one row; a partial unique index on (customer_id) WHERE is_current so a bug cannot create two current rows; and idempotency, since rerunning the same batch must change nothing. UPDATE ... FROM is PostgreSQL syntax; other engines use MERGE or a correlated update.
44. How do you join facts to an SCD Type 2 dimension "as of" the event time?
Answer: Join on the business key and on the event time falling inside the version's validity window, using half-open intervals so boundaries match exactly one version:
SELECT f.order_id, f.order_ts, d.segment
FROM fact_orders f
JOIN dim_customer d
ON d.customer_id = f.customer_id
AND f.order_ts >= d.valid_from
AND f.order_ts < d.valid_to;Better still, resolve the surrogate key of the correct version at load time and store it on the fact row, so reports join on a single integer. The as-of pattern also matters for ML features: training data must use attribute values known at the time of each event, not today's values, or the model learns from information it will not have in production.
Interview tip: If a fact predates the first version of a customer (a late-arriving dimension), the inner join drops it. Mention an "unknown member" row or a left join plus a repair process.
SQL for AI work
45. Write a pgvector query that returns the nearest document chunks for a user's question.
Answer: Store embeddings in a vector(n) column, order by a distance operator and always use LIMIT, because an approximate index (HNSW or IVFFlat) is used only for ORDER BY distance LIMIT k queries in ascending order. The operators are <-> (L2), <=> (cosine distance), <#> (negative inner product) and <+> (L1). The index's operator class must match the operator you query with.
-- PostgreSQL + pgvector
CREATE INDEX chunks_embedding_hnsw
ON doc_chunks USING hnsw
(embedding vector_cosine_ops);
SELECT chunk_id, doc_id, content,
embedding <=> :query_vec AS distance
FROM doc_chunks
WHERE tenant_id = :tenant_id
ORDER BY embedding <=> :query_vec
LIMIT 8;Cosine similarity is 1 - distance. Common mistakes: ordering by a similarity expression descending (the index cannot be used), querying with a different operator from the index's operator class, and mixing embeddings from two models in one column. The full build, from schema to re-embedding, is in our pgvector RAG tutorial.
46. Your filtered vector search returns only three results when you asked for eight. Why, and how do you combine vector and keyword search in SQL?
Answer: With an approximate index, PostgreSQL walks the index for the nearest candidates (governed by hnsw.ef_search, default 40) and applies the WHERE filter afterwards. If the tenant or department filter is selective, most candidates are discarded and fewer than k rows survive. Fixes: pgvector 0.8.0 and later support iterative index scans (SET hnsw.iterative_scan = strict_order or relaxed_order) that keep scanning until enough rows pass; raise ef_search; build partial indexes or partitions per large tenant; or, for very selective filters, let a B-tree on the filter column drive an exact search over the few matching rows.
Hybrid search fuses a vector ranking with a full-text ranking, commonly by reciprocal rank fusion (RRF):
WITH v AS (
SELECT chunk_id, ROW_NUMBER() OVER
(ORDER BY embedding <=> :qv) AS r
FROM doc_chunks WHERE tenant_id = :t
ORDER BY embedding <=> :qv LIMIT 40
), k AS (
SELECT chunk_id, ROW_NUMBER() OVER (ORDER BY
ts_rank(tsv, q) DESC) AS r
FROM doc_chunks,
plainto_tsquery('english', :qtext) q
WHERE tenant_id = :t AND tsv @@ q
ORDER BY ts_rank(tsv, q) DESC LIMIT 40
)
SELECT chunk_id,
COALESCE(1.0 / (60 + v.r), 0)
+ COALESCE(1.0 / (60 + k.r), 0) AS rrf
FROM v FULL OUTER JOIN k USING (chunk_id)
ORDER BY rrf DESC
LIMIT 8;The constant 60 is the conventional RRF damping value. Keyword search catches exact identifiers (policy numbers, error codes) that embeddings blur. See hybrid search and reranking for when to add a reranker on top, and the vector database interview questions for index internals.
47. An LLM will generate SQL against your database. Which database-level controls do you put in place?
Answer: Assume the generated SQL can be wrong or adversarial (prompt injection through a question or through data), and enforce safety in the database, not in the prompt.
-- PostgreSQL: a dedicated, least-privilege role
-- (authenticate via your secrets manager)
CREATE ROLE ai_reader LOGIN NOSUPERUSER
NOCREATEDB NOCREATEROLE NOBYPASSRLS;
GRANT USAGE ON SCHEMA analytics TO ai_reader;
GRANT SELECT ON analytics.v_sales_summary,
analytics.v_store_kpis TO ai_reader;
ALTER ROLE ai_reader SET statement_timeout = '15s';
ALTER ROLE ai_reader
SET idle_in_transaction_session_timeout = '30s';
ALTER ROLE ai_reader
SET default_transaction_read_only = on;
ALTER ROLE ai_reader SET work_mem = '32MB';
-- superuser-only setting; session cannot raise it
ALTER ROLE ai_reader SET temp_file_limit = '1GB';- Privileges are the boundary. Grant
SELECTon curated views only, not base tables, and never use broad roles such aspg_read_all_data. Role-level settings such asdefault_transaction_read_only,statement_timeoutandwork_memare only defaults that a session can change withSETorset_config(), so they are backstops, not security controls on their own; the validator must block those calls. - Resource limits. Statement timeout,
work_mem,temp_file_limit(which only a superuser can set, so the session cannot raise it), a connection limit for the role, and a row cap applied by wrapping the query (SELECT * FROM (...) q LIMIT 1000) or by the fetch size. - Run against a replica or a warehouse with its own compute, so a runaway query cannot hurt the transactional system.
- Validate before executing. Parse the SQL with a real parser (for example the Python library
sqlglot), allow a singleSELECTstatement only, reject unlisted tables and functions such aspg_sleep,dblinkor file-access functions, and runEXPLAINto reject plans whose estimated cost is too high. - Audit. Log the question, the SQL, the user and the row count for every execution.
Our text-to-SQL agent project walks through the full application around these controls; this answer is the database layer an interviewer expects you to know by heart.
48. How does row-level security work in PostgreSQL, and how do you use it for an AI assistant serving many users?
Answer: Row-level security (RLS) attaches policies to a table; every query is rewritten to include the policy's condition. For an assistant, set the end user's identity in the session and let the policy filter rows, so even a generated query with no WHERE clause sees only permitted data.
ALTER TABLE sales ENABLE ROW LEVEL SECURITY;
ALTER TABLE sales FORCE ROW LEVEL SECURITY;
CREATE POLICY branch_isolation ON sales
FOR SELECT TO ai_reader
USING (branch_id = current_setting(
'app.branch_id', true)::int);
-- per request, inside the transaction:
BEGIN;
SET LOCAL app.branch_id = '1042';
-- run the validated, generated query here
COMMIT;Details that matter: superusers and roles with BYPASSRLS skip policies, and table owners skip them unless FORCE ROW LEVEL SECURITY is set, so the AI role must be neither. Use SET LOCAL inside a transaction so the setting cannot leak to the next request on a pooled connection. With current_setting(..., true), a missing setting returns NULL and the policy matches nothing, which fails closed. Make sure the generated SQL cannot itself change app.branch_id: reject SET statements and calls to set_config in the validator, or derive identity from something the query cannot alter. Views used by the role should be created with security_invoker (PostgreSQL 15 and later) so policies apply as the querying role. The same mechanism isolates tenants in a pgvector chunks table. For identity design beyond the database, see AI agent identity and access.
49. How do you evaluate the SQL that an LLM generates?
Answer: Judge results, not SQL text. Two queries can differ textually and be equivalent, or look almost identical and be wrong. Build a test set of real questions, each with a reviewed reference query, and measure execution accuracy: run both against a fixed snapshot and compare result sets.
-- zero rows from both directions = same result set
(SELECT * FROM generated_result
EXCEPT ALL
SELECT * FROM reference_result)
UNION ALL
(SELECT * FROM reference_result
EXCEPT ALL
SELECT * FROM generated_result);- Compare as multisets (
EXCEPT ALLkeeps duplicates; plainEXCEPTwould hide a duplicated row) and ignore order unless the question asks for it. Normalise column names, and round floats to an agreed precision. - Use more than one database state. A wrong query can match the reference by luck on one snapshot, for example when a missing filter happens to exclude nothing; seed edge cases such as cancelled orders and NULL regions.
- Score separately: does it parse, does it execute within limits, is the result correct, did it refuse when it should have (questions outside the certified metrics).
- Categorise failures: wrong table, wrong join grain (fan-out), wrong filter, wrong date range or time zone, wrong aggregation. Each category points to a different fix in the semantic layer or prompt.
- Rerun the whole set on every model, prompt, view or schema change, and track the trend.
Public benchmarks such as Spider and BIRD use execution accuracy too, but your own schema and definitions are what matter. For evaluation methodology, see the LLM evaluation interview questions.
Live-coding problems
These are the kind of problems set in 30 to 45 minute live rounds. Talk through the grain and edge cases before typing.
50. Funnel: what share of users who viewed a product added it to the cart within 7 days?
Answer: Take each user's first view per product, look for a later add-to-cart of the same product within 7 days, and divide.
WITH views AS (
SELECT user_id, product_id, MIN(event_ts) AS view_ts
FROM events WHERE event_type = 'view'
GROUP BY user_id, product_id
)
SELECT COUNT(*) AS viewer_product_pairs,
AVG(CASE WHEN EXISTS (
SELECT 1 FROM events c
WHERE c.user_id = v.user_id
AND c.product_id = v.product_id
AND c.event_type = 'add_to_cart'
AND c.event_ts >= v.view_ts
AND c.event_ts < v.view_ts
+ INTERVAL '7 days'
) THEN 1.0 ELSE 0 END) AS conversion
FROM views v
WHERE v.view_ts < CURRENT_TIMESTAMP
- INTERVAL '7 days';Discuss: the denominator is (user, product) pairs, not users; carts added before the first view do not count; and views from the last 7 days have not had a full window yet, which is why the final filter excludes them; without it the rate looks artificially low.
51. Find customers whose monthly spend increased for three consecutive months.
Answer: Aggregate to month, compare with previous months using LAG, and make sure the months are really consecutive.
WITH m AS (
SELECT customer_id,
DATE_TRUNC('month', order_ts) AS mon,
SUM(amount) AS spend
FROM orders GROUP BY 1, 2
), l AS (
SELECT m.*,
LAG(spend, 1) OVER w AS s1,
LAG(spend, 2) OVER w AS s2,
LAG(spend, 3) OVER w AS s3,
LAG(mon, 3) OVER w AS mon3
FROM m
WINDOW w AS (PARTITION BY customer_id ORDER BY mon)
)
SELECT DISTINCT customer_id
FROM l
WHERE spend > s1 AND s1 > s2 AND s2 > s3
AND mon3 = mon - INTERVAL '3 months';Three increases need four months of data. The mon3 check stops a customer with a gap (January, March, April, June) from qualifying, a bug most candidates miss. DATE_TRUNC is PostgreSQL, Snowflake and Databricks syntax; BigQuery uses DATE_TRUNC(d, MONTH).
52. Flag possible duplicate payments: same card and amount within five minutes.
Answer: Compare each payment with the previous one on the same card and amount. LAG avoids a self-join and its quadratic cost on busy cards.
SELECT payment_id, card_id, amount, paid_at,
prev_id AS possible_duplicate_of
FROM (
SELECT p.*,
LAG(payment_id) OVER w AS prev_id,
LAG(paid_at) OVER w AS prev_at
FROM payments p
WINDOW w AS (PARTITION BY card_id, amount
ORDER BY paid_at, payment_id)
) t
WHERE paid_at - prev_at <= INTERVAL '5 minutes';Variants an interviewer may add: "similar" amounts (then you need a self-join with a range condition on time and amount, bounded by the time window so it stays cheap), and excluding legitimate repeats such as metro card top-ups. Say how you would validate the rule with the fraud or operations team before alerting on it.
53. For each day, count distinct users active in the previous 30 days (rolling MAU).
Answer: Distinct counts cannot be summed across days, and COUNT(DISTINCT) is not allowed as a window function in most engines. Join each calendar day to the activity in its trailing window and count distinct users per day:
WITH d AS (
SELECT DISTINCT user_id,
CAST(event_ts AS DATE) AS act_date
FROM events
)
SELECT cal.day,
COUNT(DISTINCT d.user_id) AS rolling_30d_users
FROM calendar cal
JOIN d
ON d.act_date > cal.day - 30
AND d.act_date <= cal.day
WHERE cal.day BETWEEN DATE '2026-09-01'
AND DATE '2026-09-30'
GROUP BY cal.day
ORDER BY cal.day;Reducing events to one row per user per day first is the key optimisation. Explain the cost: each daily activity row is joined to 30 calendar days. At large scale, teams precompute per-user last-active dates, or use mergeable approximate sketches (HyperLogLog) per day so windows can be combined cheaply, accepting a small error.
54. Return the top-selling product in each category, keeping ties, with its share of category revenue.
Answer: Rank and total in the same pass, then filter.
WITH pr AS (
SELECT p.category, p.product_id,
SUM(oi.qty * oi.unit_price) AS revenue
FROM order_items oi
JOIN products p ON p.product_id = oi.product_id
GROUP BY p.category, p.product_id
), ranked AS (
SELECT pr.*,
RANK() OVER (PARTITION BY category
ORDER BY revenue DESC) AS rnk,
SUM(revenue) OVER (PARTITION BY category)
AS category_revenue
FROM pr
)
SELECT category, product_id, revenue,
ROUND(100.0 * revenue / category_revenue, 1)
AS pct_of_category
FROM ranked
WHERE rnk = 1;RANK keeps ties as the question requires; ROW_NUMBER would drop one silently. The category total must be computed before filtering, which is why it sits in the same CTE as the rank. Mention that returns and cancellations should be excluded or netted, depending on the business definition.
Real-world scenarios
55. A report query that ran in two seconds now takes two minutes. Nothing in the code changed. How do you investigate?
Answer: The plan changed, the data changed, or the environment changed. Find out which before tuning.
What I would check:
- Run
EXPLAIN (ANALYZE, BUFFERS)and compare with a known-good plan if one was captured (auto_explainorpg_stat_statementshistory help here). - Estimated versus actual rows. A large data load after which statistics were not refreshed is a frequent cause; run
ANALYZEon the tables involved. - Prepared statements: PostgreSQL may switch to a generic plan after several executions, which can be poor for skewed parameter values. Test with
plan_cache_modeor literal values. - Bloat: a table or index grown far beyond its live data because vacuum is blocked (see Q57).
- Locks:
pg_stat_activityandpg_locksshow whether the query is waiting rather than working. - Resource contention: a new batch job on the same database, or a replica falling behind.
Production consideration: Capture plans and query statistics continuously so the next regression can be compared with a baseline instead of being reconstructed from memory.
56. A nightly MERGE into the customer table started failing with "cannot affect row a second time". What happened?
Answer: The source now contains more than one row for some target key, so the statement would update the same target row twice. The standard requires an error rather than picking one arbitrarily. Typical causes: an upstream change began sending full history instead of a snapshot, a retry appended a batch twice, or a join in the staging query fanned out.
What I would check:
SELECT customer_id, COUNT(*) FROM stg_customer GROUP BY 1 HAVING COUNT(*) > 1to measure the duplicates.- Whether duplicates are identical (a replayed batch) or different versions (history or fan-out).
- The staging query's joins and the load job's retry logic.
Fix the source to one row per key with a deterministic rule (latest updated_at, then a sequence number) before the MERGE, and add a uniqueness test on staging that fails the pipeline with a clear message.
Production consideration: Do not "fix" it by removing the update branch or swallowing the error; that turns a loud failure into silent wrong data.
57. A PostgreSQL table keeps growing and queries slow down, although row counts are stable. Monitoring shows a BI connection that has been "idle in transaction" for two days.
Answer: Under MVCC, updates and deletes leave dead row versions that vacuum removes only when no running transaction might still need them. A transaction left open for days pins that horizon, so dead rows accumulate across the database, tables and indexes bloat, and scans read far more pages.
What I would check:
pg_stat_activityfor the oldestxact_startand its state, user and application.- Dead tuple counts and last autovacuum times in
pg_stat_user_tables. - Replication slots and long queries on replicas with
hot_standby_feedback, which hold the horizon in the same way.
Terminate the session after confirming with its owner, let autovacuum catch up, and reclaim space with a rebuild tool if bloat is severe. Prevent recurrence with idle_in_transaction_session_timeout for BI and AI roles, and point heavy analytics at a replica or warehouse.
Production consideration: Alert on transaction age, not just on query duration. An idle transaction uses no CPU and is invisible to most dashboards.
58. Your text-to-SQL assistant produced a query that cross-joined two large tables and slowed the shared warehouse for everyone. What do you change?
Answer: This is a missing guardrail, not just a bad generation. Contain the blast radius first, then improve generation.
What I would check:
- Which limits were absent: statement timeout, resource quota or dedicated compute for the assistant's role, row cap.
- Whether the validator ran
EXPLAINand rejected high-cost plans, and whether it rejected joins without a join condition. - Whether the model saw base tables at all; it should see curated views with documented join paths.
- The question that triggered it, and whether similar questions exist in the evaluation set.
Then: give the assistant its own warehouse or resource pool with a budget, keep the timeouts from Q47, add a cost check before execution, expose pre-joined views at the right grain so the model rarely writes multi-table joins, and add the failing question to the test set from Q49.
Production consideration: Ask the user to confirm before running queries whose estimated cost is above a threshold, and log every rejection. Rejections are a useful signal about which views are missing.
59. A RAG assistant built on pgvector returned a chunk from another customer's documents. How do you investigate and fix it?
Answer: Treat it as a security incident: contain, find the path, fix it in the database, and prove the fix.
What I would check:
- The exact query that ran. Was the tenant filter present? Was it built by string concatenation, or missing on one code path such as the keyword branch of hybrid search?
- Whether the chunks were stored with the correct
tenant_idat ingestion; a bulk re-embedding job may have written rows without it. - Whether RLS was enabled and forced on the chunks table, and which role the application connected as (an owner or
BYPASSRLSrole skips policies). - Caches: a semantic cache keyed only on the question text can serve one tenant's answer to another.
Fix by enforcing isolation with RLS policies on the chunks table (Q48), making tenant_id NOT NULL, scoping caches by tenant, and adding automated tests that query as tenant A and assert zero rows from tenant B. For the wider design, see multi-tenant AI SaaS.
Production consideration: Notify according to your incident and privacy obligations; under India's DPDP Act, a personal data breach has reporting duties. Keep the logs that show which data was exposed to whom.
60. A GCC team is migrating reports from SQL Server or Oracle to PostgreSQL. Which SQL differences cause wrong results rather than errors?
Answer: Syntax errors are easy; the dangerous differences are the ones that run and return different numbers.
| Difference | Why it bites |
|---|---|
| Empty string | Oracle treats '' as NULL; PostgreSQL does not, so IS NULL filters change meaning |
| Integer division | PostgreSQL and SQL Server truncate 1/2 to 0; Oracle returns 0.5 |
| NULL sort order | SQL Server sorts NULLs first; PostgreSQL sorts them last in ascending order, which changes "top N" outputs |
| Case sensitivity | SQL Server collations are often case-insensitive; PostgreSQL comparisons are case-sensitive by default, so joins on codes stop matching |
| Dates | Oracle DATE includes a time; SQL Server DATETIME rounds fractions; time-zone handling differs |
| String concatenation with NULL | Oracle ignores NULLs in ||; in PostgreSQL 'a' || NULL is NULL |
What I would check:
- An inventory of reports ranked by business criticality.
- Automated output comparison: run each report on both systems for the same snapshot and diff the results with the
EXCEPT ALLtechnique from Q49. - A dialect translation tool (for example
sqlglot) for the first pass, followed by human review of every flagged construct above.
Production consideration: Run both systems in parallel for at least one month-end close before switching finance users over, because month-end logic is where the rarer paths execute.
Key takeaways
- Most wrong SQL results come from three sources: NULL logic, join fan-out and time zones. Check grain and row counts at every join.
- Window functions solve dedup, top-N, running totals, gaps and islands, and sessionisation; know your frame, and end every ordering with a unique key.
- Write sargable predicates and half-open date ranges, and read EXPLAIN ANALYZE by comparing estimated with actual rows.
- Know what your engine does at each isolation level, and retry on serialisation failures and deadlocks.
- Upserts and SCD Type 2 must be idempotent and must start from a deduplicated source.
- For AI work, put safety in the database: least-privilege roles, curated views, RLS, timeouts and cost checks. Evaluate generated SQL by comparing result sets.
Interview preparation checklist
- Install PostgreSQL locally (or in Docker) and load a small orders, customers and events dataset you can reason about by hand.
- Write from memory: anti-join with
NOT EXISTS, top-N per group three ways, running total, 7-day moving average, gaps and islands, sessionisation and pivot. - Deliberately create a fan-out and a
NOT INNULL trap, and explain the wrong results aloud. - Run
EXPLAIN ANALYZEbefore and after adding a composite index; make a predicate non-sargable and watch the plan change. - Open two
psqlsessions and reproduce a lost update, a deadlock and a serialisation failure. - Implement an SCD Type 2 load, rerun it and prove it is idempotent.
- Build a pgvector table with an HNSW index, run filtered queries and try iterative scans.
- Create a read-only AI role with RLS and timeouts, then try to break it with your own generated queries.
- Write ten business questions with reference SQL and score a text-to-SQL prompt against them.
- Practise explaining a query's grain and edge cases before writing it; live rounds reward that.
FAQ
Which SQL topics are most important for data and AI interviews?
Joins and NULL behaviour, GROUP BY rules, window functions, CTEs, date handling and query performance come up in most rounds. Data engineering roles add upserts, SCD Type 2 and transactions. AI roles add vector search in PostgreSQL, row-level security and safe text-to-SQL design.
Which database should I practise on for SQL interviews?
PostgreSQL is a sound default because it is free, follows the standard closely and supports window functions, CTEs, MERGE, row-level security and pgvector. Then learn the dialect differences of the warehouse your target employer uses, such as Snowflake, BigQuery, Databricks SQL or SQL Server.
How should I prepare for a live SQL coding round?
Practise on a real database rather than on paper, and say the grain of each table and the edge cases before you type. Interviewers look for correct results on NULLs, ties and missing dates, and for clear reasoning when the first attempt is wrong.
Do data analysts need advanced SQL?
Analysts need strong window functions, conditional aggregation, date handling and the judgement to spot fan-out and NULL errors. Index design, isolation levels and upserts matter less for analyst roles but help in interviews for analytics engineering positions.
Is SQL still relevant now that LLMs can write queries?
Yes. LLM-generated SQL still has to be reviewed, tested, secured and tuned, and that work needs someone who can read a query and see a wrong join or a non-sargable filter. Text-to-SQL systems also need curated views, permissions and evaluation sets that SQL-literate engineers design.
What SQL do AI and ML engineers need?
Enough to build training and evaluation datasets without leakage, write point-in-time joins, query vector embeddings in PostgreSQL with pgvector, and design safe database access for agents and assistants. Window functions and performance basics are expected too.
How long does it take to get interview-ready in SQL?
It depends on your starting point. Someone who already writes basic queries can usually cover the patterns in this guide in a few weeks of daily practice; a fresher should plan for longer and spend most of the time solving problems on real data.
Can a fresher get a data role with strong SQL alone?
Strong SQL is often the deciding skill for entry-level analyst and data roles, but pair it with Python, one visualisation or BI tool and a project that shows you can turn raw data into a reliable answer. That combination is easier to defend in interviews than SQL on its own.
Ready to go from interview-level SQL to production data pipelines and the data foundations behind enterprise AI? Cloudsoft's HORIZON data engineering and AI course covers SQL, data modelling, pipelines and AI data work in classroom sessions in Ameerpet or live online. If you want a broader path across AI, ML, cloud and security, explore the APEX AI, ML, Cloud and Cyber Security program. Call +91 96660 19191 to book a free demo.
