SQL Interview Questions for Data Analysts: Five Tested Answers

Five analyst SQL questions with definitions and checked answers

Analyst interviews test whether a query answers the intended question, not just whether it runs. These PostgreSQL 16 questions use complete fictional VALUES fixtures and read-only solutions. Each highlights a common ambiguity: distinct salary values, complete calendar months, tied leaders, unresolved identities, or customers with no activity.

1. Find every employee at the second distinct known salary

“Second highest” here means the second distinct non-NULL salary value, with every employee at that value returned. It does not mean the second row after sorting.

WITH employees(id,salary) AS (VALUES (1,100),(2,100),(3,80),(4,NULL::integer),(5,80)),
ranked AS (
 SELECT *,DENSE_RANK() OVER(ORDER BY salary DESC) AS salary_rank
 FROM employees WHERE salary IS NOT NULL
)
SELECT id,salary FROM ranked WHERE salary_rank=2 ORDER BY id;

id | salary
3 | 80
5 | 80

The two employees at 100 share rank 1; both at 80 share rank 2. With only one distinct known salary, no employee qualifies. With all salaries unknown, the result is also empty. A one-row scalar report could deliberately return NULL instead, but that is a different output contract.

2. Calculate sales for the previous complete calendar month

Use an explicit as-of date of October 17, 2026. The previous month is September 1 through October 1, not the trailing month from September 17. The timestamps below represent UTC by convention.

WITH params(as_of) AS (VALUES (DATE '2026-10-17')),
sales(id,sold_at,amount) AS (
 VALUES (1,TIMESTAMP '2026-08-31 23:59:59',99),
 (2,TIMESTAMP '2026-09-01 00:00:00',10),
 (3,TIMESTAMP '2026-09-30 23:59:59.999999',20),
 (4,TIMESTAMP '2026-10-01 00:00:00',100)
), bounds AS (
 SELECT DATE_TRUNC('month',as_of)::timestamp AS end_ts FROM params
)
SELECT COUNT(*) AS sale_rows,SUM(s.amount) AS total_sales
FROM sales s CROSS JOIN bounds b
WHERE s.sold_at>=b.end_ts-INTERVAL '1 month' AND s.sold_at<b.end_ts;

sale_rows | total_sales
2 | 30

The upper bound includes every representable instant before October 1 without guessing the last time on September 30. Empty input would produce count zero and sum NULL. Decide whether a complete sales feed permits displaying that sum as zero; do not treat a missing feed as no sales.

3. Return all products tied for the most distinct orders

The fact rows are order-product occurrences. Product A appears twice in order 1, so count distinct order IDs rather than raw lines. The output keeps every tied leader.

WITH lines(order_id,product) AS (
 VALUES (1,'A'),(1,'A'),(2,'A'),(1,'B'),(3,'B'),(4,'C')
), totals AS (
 SELECT product,COUNT(DISTINCT order_id) AS orders FROM lines GROUP BY product
), ranked AS (
 SELECT *,RANK() OVER(ORDER BY orders DESC) AS popularity_rank FROM totals
)
SELECT product,orders FROM ranked WHERE popularity_rank=1 ORDER BY product;

product | orders
A | 2
B | 2

A LIMIT 1 would hide the tie unless the business explicitly wants a single winner with a secondary rule. “Most units sold,” “most line items,” and “most distinct orders” are three different popularity measures. Product IDs, rather than display names, should identify real products if names can repeat or change.

4. Propose one current record per known normalized email

For this fictional exercise only, trim and lowercase email under an agreed case-insensitive matching policy. The newest update wins, with ID breaking ties. Unknown email rows remain independent; do not combine all NULL identities. The query selects winners and does not delete source data.

WITH users(id,email,updated_at) AS (
 VALUES (1,' [email protected] ',TIMESTAMP '2026-01-01'),
 (2,'[email protected]',TIMESTAMP '2026-01-02'),
 (3,NULL,TIMESTAMP '2026-01-03'),(4,NULL,TIMESTAMP '2026-01-04')
), normalized AS (
 SELECT *,NULLIF(LOWER(BTRIM(email)),'') AS email_key FROM users
), ranked AS (
 SELECT *,ROW_NUMBER() OVER(
 PARTITION BY email_key,CASE WHEN email_key IS NULL THEN id END
 ORDER BY updated_at DESC NULLS LAST,id DESC) AS rn FROM normalized
)
SELECT id,email_key FROM ranked WHERE rn=1 ORDER BY id;

id | email_key
2 | [email protected]
3 | NULL
4 | NULL

An email address is not a universal person identifier: people can share an address or change it. A real cleanup needs source authority and identity rules beyond this exercise. Retain raw values and review losing records. For the separate mutation workflow, see deterministic deduplication.

5. Count orders while retaining customers with none

WITH customers(id) AS (VALUES (1),(2),(3)),
orders(id,customer_id) AS (VALUES (10,1),(11,1),(12,2))
SELECT c.id,COUNT(o.id) AS orders
FROM customers c LEFT JOIN orders o ON o.customer_id=c.id
GROUP BY c.id ORDER BY c.id;

id | orders
1 | 2
2 | 1
3 | 0

COUNT(o.id) counts matched order identities. COUNT(*) would count the preserved unmatched row for customer 3. A date or status filter on orders belongs in the qualifying input or join condition when unmatched customers must remain; adding it to a final WHERE can undo preservation.

Explain assumptions before optimizing

For each answer, name the output grain, eligibility rule, tie handling, and unknown-value policy. Then predict one edge case and reconcile a count or sum. Performance discussion should follow a correct query: inspect representative plans and propose indexes for actual access patterns rather than claiming every subquery is slow or every join is faster.

The fixtures are a baseline, not a guarantee of interview success. Practice by changing one assumption at a time: request one tied winner, use a local business month, add an unknown salary, or make every customer inactive. Write the new expected answer before changing SQL. Primary references: ranking, date/time functions, and joins.