10 Data Analyst SQL Interview Questions with Checked PostgreSQL Examples
Quick Overview
Practice analyst SQL with checked fixtures for rankings, duplicate records, missing months, streaks, anti-joins, cancellation rates, and medians.
SQL interview answers should begin with the output grain and the rules for ties, NULLs, missing periods, and empty inputs. A query that executes successfully can still answer the wrong business question.
These ten questions use original, synthetic fixtures. Each SQL block is a complete PostgreSQL 17 query with its own VALUES data; it does not need application tables. The examples are independent of the protected solutions behind the linked practice prompts. Use the SQL and data manipulation question bank for additional practice.
1. How do you find the second-highest distinct salary?
Decide whether you are ranking salary values or employees. Tied employees at the highest salary do not create a second distinct value.
WITH employees(salary) AS (
VALUES (300), (300), (200), (NULL)
)
SELECT MAX(salary) AS second_highest
FROM employees
WHERE salary < (SELECT MAX(salary) FROM employees);
The result is one row containing 200. An aggregate without grouping still returns a row when no salary qualifies; that row contains NULL. A plain ORDER BY ... OFFSET 1 over employees would count duplicate salary rows, and a filtered query without an outer aggregate could return no row instead of the requested NULL.
For an arbitrary distinct position, consider DENSE_RANK after excluding NULLs. Rehearse the differences with the public ROW_NUMBER, RANK, and DENSE_RANK question.
2. How do you identify duplicates and choose a survivor?
First define identity. Equal email strings, equal rows, and records for the same person are different duplicate rules. This fixture treats only matching non-NULL email strings as duplicates, preserves unknown emails separately, and keeps the earliest created_at, then lowest ID.
WITH customers(id, email, created_at) AS (
VALUES
(1, 'a@example.test', TIMESTAMP '2026-01-02'),
(2, 'a@example.test', TIMESTAMP '2026-01-01'),
(3, NULL, TIMESTAMP '2026-01-01'),
(4, NULL, TIMESTAMP '2026-01-02')
), ranked AS (
SELECT id, email,
ROW_NUMBER() OVER (
PARTITION BY email, CASE WHEN email IS NULL THEN id ELSE 0 END
ORDER BY created_at, id
) AS rn
FROM customers
)
SELECT id, email, rn
FROM ranked
WHERE rn > 1
ORDER BY id;
The preview returns ID 1 as the duplicate candidate; ID 2 survives even though its identifier is higher. IDs 3 and 4 remain distinct. This query identifies candidates without deleting them. A real cleanup also needs rules for dependent records, concurrent writes, and validating the survivor set.
Agree on whitespace, case, and email-normalization policy before changing the comparison. The CASE term is needed because window partitions put every NULL email in one group, while ordinary equality never matches NULL; ranking with one rule and deleting with the other would apply inconsistent identity rules.
3. How do you return the top N products in each category?
The fixture requests at most two rows per category, with deterministic selection when sales tie. It already has one row per product; order-line data would need aggregation first.
WITH products(category, product_id, sales) AS (
VALUES ('A', 1, 10), ('A', 2, 10), ('A', 3, 9), ('B', 4, 8)
), ranked AS (
SELECT category, product_id, sales,
ROW_NUMBER() OVER (
PARTITION BY category
ORDER BY sales DESC, product_id
) AS rn
FROM products
WHERE sales IS NOT NULL
)
SELECT category, product_id, sales
FROM ranked
WHERE rn <= 2
ORDER BY category, rn;
Product IDs 1, 2, and 4 qualify. RANK can include ties at a competitive position; DENSE_RANK selects distinct sales levels. Do not add a unique ID to those rank orderings if equal sales should remain peers. Use a final display order separately.
The public top-products-per-category question adds the reporting assumptions you must clarify before ranking.
4. How do you calculate a daily running total?
Aggregate to daily grain before taking the cumulative sum. Here amount is signed net activity in a synthetic reporting ledger, so the negative entry reduces the first day's total.
WITH orders(order_id, order_date, amount) AS (
VALUES (1, DATE '2026-01-01', 10),
(2, DATE '2026-01-01', -3),
(3, DATE '2026-01-03', 7)
), daily AS (
SELECT order_date, SUM(amount) AS daily_amount
FROM orders
GROUP BY order_date
)
SELECT order_date, daily_amount,
SUM(daily_amount) OVER (
ORDER BY order_date
ROWS BETWEEN UNBOUNDED PRECEDING AND CURRENT ROW
) AS running_total
FROM daily
ORDER BY order_date;
January 1 and January 3 have daily amounts 7 and 7, with running totals 7 and 14. January 2 is absent; add a calendar when the report needs a row for every day.
At transaction grain, use a stable event ID to order tied timestamps. PostgreSQL's default ordered window frame includes peers, which can make tied rows share a cumulative value. See the window-function documentation and the running-balance article.
5. How do you calculate month-over-month growth when a month is empty?
LAG means the previous available row, not automatically the previous calendar month. This example assumes ingestion is complete for January through March. Its calendar supplies the inactive February row before calculating growth.
WITH orders(order_date, amount) AS (
VALUES (DATE '2026-01-10', 100::numeric),
(DATE '2026-03-10', 150::numeric)
), calendar(month) AS (
VALUES (DATE '2026-01-01'), (DATE '2026-02-01'), (DATE '2026-03-01')
), totals AS (
SELECT DATE_TRUNC('month', order_date)::date AS month,
SUM(amount) AS revenue
FROM orders
GROUP BY 1
), monthly AS (
SELECT c.month, COALESCE(t.revenue, 0) AS revenue
FROM calendar c LEFT JOIN totals t USING (month)
), compared AS (
SELECT month, revenue, LAG(revenue) OVER (ORDER BY month) AS previous
FROM monthly
)
SELECT month, revenue, previous,
ROUND(100.0 * (revenue - previous) / NULLIF(previous, 0), 2) AS growth_pct
FROM compared
ORDER BY month;
February is -100.00%; March's growth from a zero denominator is NULL. January has no prior observation. Do not replace those undefined comparisons with zero growth. Zero-filling is a business assumption about complete coverage, not a repair for an unknown or failed ingestion period.
6. How do you find the longest consecutive login streak?
An event is not a day. Normalize timestamps to the agreed business timezone, exclude missing dates, and deduplicate days before constructing islands.
WITH logins(user_id, login_date) AS (
VALUES (1, DATE '2026-01-01'), (1, DATE '2026-01-01'),
(1, DATE '2026-01-02'), (1, DATE '2026-01-04'),
(2, DATE '2026-01-05')
), days AS (
SELECT DISTINCT user_id, login_date FROM logins
WHERE login_date IS NOT NULL
), islands AS (
SELECT user_id, login_date,
login_date - ROW_NUMBER() OVER (
PARTITION BY user_id ORDER BY login_date
)::integer AS island
FROM days
), streaks AS (
SELECT user_id, island, COUNT(*) AS days
FROM islands
GROUP BY user_id, island
)
SELECT user_id, MAX(days) AS longest_streak
FROM streaks
GROUP BY user_id
ORDER BY user_id;
User 1 has a two-day streak; user 2 has one day. Users with no login rows are absent, so join to a user population when the contract requires an explicit zero. Requests for the current streak, boundaries, or every tied longest streak change the final selection.
7. How do you compare employees with their managers?
Use separate aliases for the two roles of the same relation. The manager identifier must resolve to at most one manager record.
WITH employees(id, name, salary, manager_id) AS (
VALUES (1, 'Manager', 100, NULL),
(2, 'Ana', 120, 1),
(3, 'Bo', NULL, 1),
(4, 'Cy', 200, 99)
)
SELECT e.id, e.name
FROM employees e
JOIN employees m ON m.id = e.manager_id
WHERE e.salary > m.salary
ORDER BY e.id;
Only Ana qualifies. Unknown salary does not pass the comparison, and the unmatched manager reference for Cy does not produce a comparison row. Report broken references separately if data quality is part of the task. A left join followed by the same pay comparison still excludes unmatched rows.
Earning more than a manager is a reporting condition, not proof of a compensation-policy violation.
8. How do you find customers with no orders?
NOT EXISTS expresses absence without letting a NULL customer key elsewhere in the order table invalidate the result.
WITH customers(customer_id) AS (VALUES (1), (2), (3)),
orders(order_id, customer_id) AS (VALUES (10, 1), (11, NULL))
SELECT c.customer_id
FROM customers c
WHERE NOT EXISTS (
SELECT 1 FROM orders o WHERE o.customer_id = c.customer_id
)
ORDER BY c.customer_id;
The result is customers 2 and 3. A naive NOT IN against this nullable order key can produce no qualifying rows. PostgreSQL documents the distinction in its subquery expressions reference.
For “no orders during a period,” put the eligible time interval inside the correlated subquery. Do not confuse never-ordered customers with recently inactive customers. Compare actual plans instead of claiming that one anti-join spelling is always faster.
9. How do you report status counts and a cancellation rate?
State the denominator. This fixture counts all observed orders, including pending orders, and preserves customers with no orders. Each order appears once rather than once per status-history event.
WITH customers(customer_id) AS (VALUES (1), (2), (3)),
orders(order_id, customer_id, status) AS (
VALUES (10, 1, 'completed'), (11, 1, 'cancelled'),
(12, 1, 'pending'), (13, 2, 'completed')
)
SELECT c.customer_id,
COUNT(o.order_id) AS total_orders,
SUM(CASE WHEN o.status = 'completed' THEN 1 ELSE 0 END) AS completed,
SUM(CASE WHEN o.status = 'cancelled' THEN 1 ELSE 0 END) AS cancelled,
ROUND(100.0 * SUM(CASE WHEN o.status = 'cancelled' THEN 1 ELSE 0 END)
/ NULLIF(COUNT(o.order_id), 0), 2) AS cancellation_pct
FROM customers c
LEFT JOIN orders o USING (customer_id)
GROUP BY c.customer_id
ORDER BY c.customer_id;
Customer 1 has three orders and a 33.33% rate. Customer 2 has one order and 0.00%. Customer 3 has zero orders and an undefined rate, represented as NULL. COUNT(*) would incorrectly count the unmatched placeholder row as an order.
If the metric concerns only resolved orders, change both the eligible population and denominator deliberately. Preserve numerators and denominators when combining groups; averaging percentages usually gives a different metric.
10. How do you calculate median salary?
Decide whether the desired statistic interpolates between the middle pair or selects an observed value. PostgreSQL's continuous percentile interpolates:
WITH salaries(value) AS (VALUES (10), (20), (30), (40), (NULL))
SELECT PERCENTILE_CONT(0.5) WITHIN GROUP (ORDER BY value) AS median
FROM salaries
WHERE value IS NOT NULL;
The result is 25. This ranked-row alternative averages the middle position or pair:
WITH salaries(value) AS (VALUES (10), (20), (30), (40), (NULL)),
ranked AS (
SELECT value, ROW_NUMBER() OVER (ORDER BY value) AS rn,
COUNT(*) OVER () AS cnt
FROM salaries
WHERE value IS NOT NULL
)
SELECT AVG(1.0 * value) AS median
FROM ranked
WHERE rn IN (FLOOR((cnt + 1) / 2.0), FLOOR((cnt + 2) / 2.0));
This also returns 25. NULL filtering belongs before row numbering and counting. For an odd population both position expressions select the same middle row; for an empty population the aggregate returns NULL. PostgreSQL's aggregate-function reference distinguishes continuous and discrete percentiles. The median and percentile article adds dialect and tie cases.
Practice a result contract before memorizing syntax
For each query, state the output grain, eligible records, ordering rules, and empty-result behavior. Then challenge it with a duplicate, a missing period, a NULL, or a zero denominator. Use window frames and ranking practice when the failure depends on peers or ordering.
For career context, see 365 Data Science's analysis of the data analyst job market. It does not measure how often particular interview questions are asked. Database syntax and behavior should be checked against the engine version you will use.
Comments (0)