Experienced interviewers rarely ask "what is a JOIN?". They describe a business situation and ask you to write the query. Here are common scenarios with clear solutions, using a fictional online store, CartKaro.
Tables: customers(customer_id, name, city, signup_date), orders(order_id, customer_id, order_date, amount, status), order_items(order_id, product_id, qty, price), products(product_id, name, category).
1. Repeat customers
"How many customers placed more than one order?"
SELECT COUNT(*) AS repeat_customers
FROM (
SELECT customer_id
FROM orders
WHERE status = 'delivered'
GROUP BY customer_id
HAVING COUNT(*) > 1
) t;2. Month-over-month revenue growth
WITH monthly AS (
SELECT DATE_TRUNC('month', order_date) AS month, SUM(amount) AS revenue
FROM orders
WHERE status = 'delivered'
GROUP BY 1
)
SELECT month, revenue,
LAG(revenue) OVER (ORDER BY month) AS prev_revenue,
ROUND(100.0 * (revenue - LAG(revenue) OVER (ORDER BY month))
/ NULLIF(LAG(revenue) OVER (ORDER BY month), 0), 1) AS growth_pct
FROM monthly
ORDER BY month;NULLIF avoids division by zero.
3. Top 3 products per category by revenue
WITH product_rev AS (
SELECT p.category, p.name, SUM(oi.qty * oi.price) AS revenue
FROM order_items oi
JOIN products p ON p.product_id = oi.product_id
GROUP BY p.category, p.name
)
SELECT category, name, revenue
FROM (
SELECT *, DENSE_RANK() OVER (PARTITION BY category ORDER BY revenue DESC) AS rnk
FROM product_rev
) r
WHERE rnk <= 3;Mention why you chose DENSE_RANK (ties) over ROW_NUMBER.
4. Customers who never ordered
SELECT c.customer_id, c.name
FROM customers c
LEFT JOIN orders o ON o.customer_id = c.customer_id
WHERE o.order_id IS NULL;5. Users active on three consecutive days
Given logins(user_id, login_date):
WITH d AS (
SELECT DISTINCT user_id, login_date FROM logins
),
g AS (
SELECT user_id, login_date,
login_date - CAST(ROW_NUMBER() OVER (PARTITION BY user_id ORDER BY login_date) AS INT) AS grp
FROM d
)
SELECT DISTINCT user_id
FROM g
GROUP BY user_id, grp
HAVING COUNT(*) >= 3;The "date minus row number" trick gives consecutive dates the same group value. Date arithmetic syntax varies by database; explain the idea if unsure.
6. First order per customer
SELECT customer_id, order_id, order_date, amount
FROM (
SELECT *, ROW_NUMBER() OVER (PARTITION BY customer_id ORDER BY order_date, order_id) AS rn
FROM orders
) t
WHERE rn = 1;7. Share of revenue by city
SELECT c.city, SUM(o.amount) AS revenue,
ROUND(100.0 * SUM(o.amount) / SUM(SUM(o.amount)) OVER (), 1) AS share_pct
FROM orders o
JOIN customers c ON c.customer_id = o.customer_id
WHERE o.status = 'delivered'
GROUP BY c.city
ORDER BY revenue DESC;SUM(SUM(...)) OVER () is the grand total across groups.
8. Remove duplicate rows, keep one
WITH ranked AS (
SELECT order_id,
ROW_NUMBER() OVER (PARTITION BY customer_id, order_date, amount ORDER BY order_id) AS rn
FROM orders
)
DELETE FROM orders WHERE order_id IN (SELECT order_id FROM ranked WHERE rn > 1);Always confirm with a SELECT before deleting.
How to answer in the interview
- Restate the question and confirm definitions ("delivered orders only?").
- Say your approach in one or two sentences.
- Write the query step by step, using CTEs for readability.
- Mention edge cases: ties, NULLs, customers with no orders.
Practise
Write these yourself in our SQL interview set, drill window analytics and CTEs, and read the window functions guide.
Practise what you just read
Reading helps; writing the code yourself is what sticks. Start here: