SQL Interview Practice: Five Graded Queries with Exact Answers

Five graded SQL questions on one small order dataset

Use this PostgreSQL 16 set as a timed practice exercise. Each answer includes its complete read-only fixture, expected output, and a follow-up that tests the definition. Try the question before reading the answer. The data is fictional, timestamps follow a UTC convention, and paid orders with an amount of zero are valid orders.

Start with the common contract

Customers 1, 2, and 3 are Ada, Ben, and Cara. Ada has two paid January orders worth 10 and 20, plus a cancelled January order worth 100. Ben has a zero-value paid January order and a 99-value order at February 1 midnight. Cara has no orders. The January report includes January 1 and excludes February 1. Every query repeats this setup so it can be copied independently.

Allow about 25 minutes: three minutes for filtering, five for aggregation, four for absence, six for the latest order, and seven for the running total and checks. The rubric is a practice tool, not a claim about an employer’s hiring score. State any changed assumptions before changing a query.

1. Which paid orders belong to January?

Return order ID, customer ID, and amount in chronological order. Include the entire last day of the month without relying on a guessed final timestamp.

WITH customers(id,name) AS (VALUES (1,'Ada'),(2,'Ben'),(3,'Cara')),
orders(id,customer_id,ordered_at,status,amount) AS (
 VALUES (1,1,TIMESTAMP '2026-01-01 00:00','paid',10),
 (2,1,TIMESTAMP '2026-01-31 09:00','paid',20),
 (3,2,TIMESTAMP '2026-01-15 12:00','paid',0),
 (4,2,TIMESTAMP '2026-02-01 00:00','paid',99),
 (5,1,TIMESTAMP '2026-01-20 10:00','cancelled',100)
), january AS (
 SELECT * FROM orders WHERE status='paid'
 AND ordered_at>=TIMESTAMP '2026-01-01' AND ordered_at<TIMESTAMP '2026-02-01'
)
SELECT id,customer_id,amount FROM january ORDER BY ordered_at,id;

id | customer_id | amount
1 | 1 | 10
3 | 2 | 0
2 | 1 | 20

The zero-value order qualifies because payment status and time determine eligibility. The cancelled 100 does not. The February 1 midnight order is excluded. Follow-up: if timestamps are instants and the business month is local, derive the two instant boundaries in that timezone before applying the same half-open comparison.

2. What are January orders and revenue for every customer?

WITH customers(id,name) AS (VALUES (1,'Ada'),(2,'Ben'),(3,'Cara')),
orders(id,customer_id,ordered_at,status,amount) AS (
 VALUES (1,1,TIMESTAMP '2026-01-01 00:00','paid',10),
 (2,1,TIMESTAMP '2026-01-31 09:00','paid',20),
 (3,2,TIMESTAMP '2026-01-15 12:00','paid',0),
 (4,2,TIMESTAMP '2026-02-01 00:00','paid',99),
 (5,1,TIMESTAMP '2026-01-20 10:00','cancelled',100)
), january AS (
 SELECT * FROM orders WHERE status='paid'
 AND ordered_at>=TIMESTAMP '2026-01-01' AND ordered_at<TIMESTAMP '2026-02-01'
), totals AS (
 SELECT customer_id,COUNT(*) AS orders,SUM(amount) AS revenue
 FROM january GROUP BY customer_id
)
SELECT c.id,c.name,COALESCE(t.orders,0) AS orders,COALESCE(t.revenue,0) AS revenue
FROM customers c LEFT JOIN totals t ON t.customer_id=c.id ORDER BY c.id;

id | name | orders | revenue
1 | Ada | 2 | 30
2 | Ben | 1 | 0
3 | Cara | 0 | 0

Ben and Cara both have zero revenue but different activity. Keeping order count prevents a no-revenue label from becoming a no-orders claim. The output population comes from customers, and the order summary has at most one row per customer. Follow-up: a missing or incomplete order feed would require a completeness flag before assigning zero.

3. Which customers had no paid January order?

WITH customers(id,name) AS (VALUES (1,'Ada'),(2,'Ben'),(3,'Cara')),
orders(id,customer_id,ordered_at,status,amount) AS (
 VALUES (1,1,TIMESTAMP '2026-01-01 00:00','paid',10),
 (2,1,TIMESTAMP '2026-01-31 09:00','paid',20),
 (3,2,TIMESTAMP '2026-01-15 12:00','paid',0),
 (4,2,TIMESTAMP '2026-02-01 00:00','paid',99),
 (5,1,TIMESTAMP '2026-01-20 10:00','cancelled',100)
), january AS (
 SELECT * FROM orders WHERE status='paid'
 AND ordered_at>=TIMESTAMP '2026-01-01' AND ordered_at<TIMESTAMP '2026-02-01'
)
SELECT c.id,c.name FROM customers c
WHERE NOT EXISTS(SELECT 1 FROM january j WHERE j.customer_id=c.id)
ORDER BY c.id;

id | name
3 | Cara

Ben’s zero-value order excludes him from this result. Testing SUM(amount) = 0 would answer the wrong question. NOT EXISTS expresses the absence of qualifying rows and does not multiply customers when there are several matches. Follow-up: if the question is “no order ever,” remove the reporting-window restriction deliberately.

4. What was each customer’s latest paid January order?

WITH customers(id,name) AS (VALUES (1,'Ada'),(2,'Ben'),(3,'Cara')),
orders(id,customer_id,ordered_at,status,amount) AS (
 VALUES (1,1,TIMESTAMP '2026-01-01 00:00','paid',10),
 (2,1,TIMESTAMP '2026-01-31 09:00','paid',20),
 (3,2,TIMESTAMP '2026-01-15 12:00','paid',0),
 (4,2,TIMESTAMP '2026-02-01 00:00','paid',99),
 (5,1,TIMESTAMP '2026-01-20 10:00','cancelled',100)
), january AS (
 SELECT * FROM orders WHERE status='paid'
 AND ordered_at>=TIMESTAMP '2026-01-01' AND ordered_at<TIMESTAMP '2026-02-01'
), ranked AS (
 SELECT *,ROW_NUMBER() OVER(PARTITION BY customer_id ORDER BY ordered_at DESC,id DESC) AS rn
 FROM january
)
SELECT c.id AS customer_id,r.id AS latest_order_id,r.amount
FROM customers c LEFT JOIN ranked r ON r.customer_id=c.id AND r.rn=1
ORDER BY c.id;

customer_id | latest_order_id | amount
1 | 2 | 20
2 | 3 | 0
3 | NULL | NULL

The row number selects a complete row, keeping its amount associated with its ID. Independently taking MAX(id), MAX(timestamp), and MAX(amount) could combine values from different orders. The unique ID breaks equal-time ties under this stated policy. Follow-up: if a tie should retain every tied latest order, use a time rank and acknowledge the changed output grain.

5. What is the running paid January revenue per customer?

WITH customers(id,name) AS (VALUES (1,'Ada'),(2,'Ben'),(3,'Cara')),
orders(id,customer_id,ordered_at,status,amount) AS (
 VALUES (1,1,TIMESTAMP '2026-01-01 00:00','paid',10),
 (2,1,TIMESTAMP '2026-01-31 09:00','paid',20),
 (3,2,TIMESTAMP '2026-01-15 12:00','paid',0),
 (4,2,TIMESTAMP '2026-02-01 00:00','paid',99),
 (5,1,TIMESTAMP '2026-01-20 10:00','cancelled',100)
), january AS (
 SELECT * FROM orders WHERE status='paid'
 AND ordered_at>=TIMESTAMP '2026-01-01' AND ordered_at<TIMESTAMP '2026-02-01'
)
SELECT customer_id,id,amount,
 SUM(amount) OVER(PARTITION BY customer_id ORDER BY ordered_at,id
 ROWS BETWEEN UNBOUNDED PRECEDING AND CURRENT ROW) AS running_revenue
FROM january ORDER BY customer_id,ordered_at,id;

customer_id | id | amount | running_revenue
1 | 1 | 10 | 10
1 | 2 | 20 | 30
2 | 3 | 0 | 0

This is a transaction-level January running sum, not a lifetime balance and not a daily calendar series. Cara has no transaction row to display. A daily report with zero-activity dates needs a calendar spine; a lifetime balance displayed in January needs earlier history or an opening balance.

Score correctness, boundaries, and explanation

Award up to four points per question: one for the requested output grain, one for correct predicates and joins, one for the exact fixture result, and one for explaining the follow-up boundary. A query that runs but excludes Cara in question 2 should lose the population point. A query that calls Ben inactive in question 3 should lose the metric point. Review the missed rule and change the fixture to reproduce the mistake.

Before timing another attempt, test an empty orders relation, two equal-time orders, an order exactly at each date boundary, and an all-zero revenue month. Expected results should follow the definition, not whatever the query happens to return. Use final ORDER BY clauses so your displayed answer is deterministic.

Continue with the specific skill you missed

For syntax refresh, use the basics sheet. For maturity and event sequence questions, move to ordered funnels. Primary references: EXISTS and window functions.