$ cat interview.sql
SQL Interview Questions and Answers
12 questions in the style data and analytics interviews actually ask, from easy filters to multi-table joins. Each one has the answer, a plain-English explanation and the most common mistake. Try it yourself first — every question opens in a live SQL editor in your browser.
1. Filter customers by country
Easy · WHERE + ORDER BY
Return the id and name of every customer from the UK, sorted by name (A-Z).
show answer
SELECT id, name FROM customers WHERE country = 'UK' ORDER BY name;
Why it works: WHERE keeps only rows that match a condition before anything else happens. ORDER BY runs last and sorts what's left. Text values go in single quotes.
Common mistake: Using double quotes around 'UK' — in most databases that means a column name, not a string.
run it in the editor2. Count orders per status
Easy · GROUP BY + COUNT
Return each order status and the number of orders with that status, aliased as order_count. Sort by order_count descending.
show answer
SELECT status, COUNT(*) AS order_count FROM orders GROUP BY status ORDER BY order_count DESC;
Why it works: GROUP BY collapses rows that share a value into one row per group; COUNT(*) counts the rows in each group. You can sort by the alias you gave the aggregate.
Common mistake: Selecting a column that is neither grouped nor aggregated.
run it in the editor3. Most expensive product per category
Medium · MAX per group
For each product category return the category and the highest price in it, aliased as max_price. Sort by max_price descending.
show answer
SELECT category, MAX(price) AS max_price FROM products GROUP BY category ORDER BY max_price DESC;
Why it works: Aggregates like MAX work per group once you GROUP BY. Asking for the top value per category is one of the most common screening questions.
Common mistake: Trying to also select the product name — that needs a join back or a window function, not plain GROUP BY.
run it in the editor4. Join orders to customers
Easy · INNER JOIN
Return order id (as order_id), customer name (as customer_name) and order_date for every completed order, sorted by order_id.
show answer
SELECT o.id AS order_id, c.name AS customer_name, o.order_date FROM orders o JOIN customers c ON c.id = o.customer_id WHERE o.status = 'completed' ORDER BY o.id;
Why it works: JOIN ... ON links two tables through a shared key (orders.customer_id = customers.id). Short aliases (o, c) keep the query readable, and the filter on status runs after the join.
Common mistake: Forgetting the ON clause, which multiplies every order by every customer.
run it in the editor5. Revenue per order
Medium · SUM of an expression
Return order_id and total revenue (quantity * unit_price summed) as revenue for every order in order_items. Sort by revenue descending, then order_id ascending.
show answer
SELECT order_id, SUM(quantity * unit_price) AS revenue FROM order_items GROUP BY order_id ORDER BY revenue DESC, order_id;
Why it works: You can aggregate a calculation: SUM(quantity * unit_price) gives revenue per order. A second sort key (order_id) makes ties deterministic.
Common mistake: Computing SUM(quantity) * SUM(unit_price), which gives the wrong total.
run it in the editor6. Top spending customers
Medium · Multi-table JOIN + HAVING
Return customer_name and total spend (as total_spend) across completed orders only. Include only customers whose total spend is above 500. Sort by total_spend descending.
show answer
SELECT c.name AS customer_name, SUM(oi.quantity * oi.unit_price) AS total_spend FROM customers c JOIN orders o ON o.customer_id = c.id JOIN order_items oi ON oi.order_id = o.id WHERE o.status = 'completed' GROUP BY c.name HAVING SUM(oi.quantity * oi.unit_price) > 500 ORDER BY total_spend DESC;
Why it works: Chain joins from customers to orders to order_items, group by customer, then use HAVING to filter on the aggregate. WHERE filters rows; HAVING filters groups.
Common mistake: Putting SUM(...) > 500 in WHERE — aggregates aren't allowed there.
run it in the editor7. Products above average price
Easy · Scalar subquery · asked-style: Amazon
Return the name and price of every product priced above the average product price. Sort by price descending.
show answer
SELECT name, price FROM products WHERE price > (SELECT AVG(price) FROM products) ORDER BY price DESC;
Why it works: A subquery in parentheses runs first and returns one number — the average price — which the outer WHERE compares against.
Common mistake: Writing WHERE price > AVG(price) without a subquery; aggregates can't be used directly in WHERE.
run it in the editor8. Active users who posted
Easy · JOIN + DISTINCT · asked-style: Meta
Return the name (as user_name) of every active user who has written at least one post. Each user should appear once. Sort by name.
show answer
SELECT DISTINCT u.name AS user_name FROM users u JOIN posts p ON p.user_id = u.id WHERE u.active = 1 ORDER BY user_name;
Why it works: Joining users to posts returns one row per post, so a user with three posts appears three times. DISTINCT removes the duplicates.
Common mistake: Forgetting DISTINCT and returning repeated names.
run it in the editor9. Filter active listings by city
Easy · Multiple conditions · asked-style: Airbnb
Return id, city and price for every active listing in San Francisco, sorted by price ascending.
show answer
SELECT id, city, price FROM listings WHERE city = 'San Francisco' AND active = 1 ORDER BY price;
Why it works: Combine filters with AND. Boolean flags are often stored as 1/0, so active = 1 means 'is active'.
Common mistake: Using OR by accident and getting inactive listings in other cities.
run it in the editor10. Applications per job post
Easy · GROUP BY a key, show labels · asked-style: LinkedIn
Return company, title and the number of applications (as applications) for every job post that received at least one application. Sort by applications descending, then title.
show answer
SELECT j.company, j.title, COUNT(*) AS applications FROM job_posts j JOIN applications a ON a.job_id = j.id GROUP BY j.id, j.company, j.title ORDER BY applications DESC, j.title;
Why it works: Group by the job's id so two jobs with the same title stay separate, and include the label columns in GROUP BY so you can display them.
Common mistake: Grouping by title only, which merges different jobs that share a name.
run it in the editor11. Average delivery time per city
Easy · AVG + ROUND · asked-style: DoorDash
Return city and the average delivery time in minutes rounded to 1 (as avg_minutes) for each city, slowest first, breaking ties by city ascending.
show answer
SELECT s.city, ROUND(AVG(d.minutes), 1) AS avg_minutes FROM deliveries d JOIN stores s ON s.id = d.store_id GROUP BY s.city ORDER BY avg_minutes DESC, s.city;
Why it works: AVG gives the mean per group; ROUND(value, 1) tidies it to one decimal place for a clean report.
Common mistake: Rounding before averaging, which skews the result.
run it in the editor12. Open pipeline by region
Easy · NOT IN filter · asked-style: Salesforce
Open opportunities are those whose stage is not 'closed won' or 'closed lost'. Return region and the total open amount (as open_pipeline) per rep region, largest first.
show answer
SELECT r.region, SUM(o.amount) AS open_pipeline FROM opportunities o JOIN reps r ON r.id = o.rep_id WHERE o.stage NOT IN ('closed won', 'closed lost') GROUP BY r.region ORDER BY open_pipeline DESC, r.region;Why it works: NOT IN excludes a list of values — here the closed stages — so only open deals are summed per region.
Common mistake: Watch out: NOT IN behaves unexpectedly if the list can contain NULL.
run it in the editorReady for harder questions?
Take the 3-minute diagnostic to find your weak spots, or browse company-style questions.