SQL Practice Questions for Data Jobs

Assume three tables: customers (customer_id, signup_date, country), orders (order_id, customer_id, order_date, status, amount) and events (event_id, customer_id, event_type, event_time, ingested_at).

1. Return the number of customers who signed up each month and how many placed at least one completed order within 30 days of signup. Approach: left join customers to completed orders within the window, group by signup month, count distinct customers with a matching order.
2. List countries where the share of cancelled orders last quarter is higher than the overall share. Approach: compute the overall share in a subquery or CTE, then compare per-country shares.

1. For each customer, return each order with the previous order's date and the days between them. Approach: LAG over orders partitioned by customer and ordered by date.
2. Return each customer's running total of completed order amounts. Approach: SUM with a window ordered by date, partitioned by customer.
3. Find the top three customers by completed revenue in each country. Approach: aggregate, then ROW_NUMBER or DENSE_RANK partitioned by country, and explain how you treat ties.

1. Events may be ingested twice. Keep one row per event_id, choosing the earliest ingested_at. Approach: ROW_NUMBER partitioned by event_id ordered by ingested_at, keep row one.
2. Find days in the last 90 with no events at all. Approach: generate a date series and left join event counts, filtering for zero.
3. Orders refer to customers that do not exist. How would you detect them, and what would you do next? Approach: anti-join, then discuss whether to quarantine, alert or fix upstream.

Write the query, then test it on a small sample you build yourself with edge cases: ties, nulls, duplicates and time zones. In interviews, say your assumptions out loud and explain how you would check the result.