All 40 lessons
Lesson 40 of 40 · SQL for Analysts
SQL Interview Questions for Data Analysts
By Skillancy Editorial Team
Every example runs on the ShopKart dataset, already loaded in the free SQL Compiler. Click Try it in the SQL Compiler under a query to open it there, then press Run.
These 15 questions cover the SQL patterns that come up again and again in data analyst interviews. Each one is solved on the ShopKart dataset with the real result, and each links back to the lesson that teaches the technique. Try each question yourself before opening the solution.
Interview tip: before writing anything, ask about edge cases. "Should cancelled orders count?" "How should ties be handled?" "Can this column be NULL?" Interviewers value the question as much as the query.
Basics and aggregation
1. How many orders were delivered, and what share of all orders is that?
Show solution
SELECT COUNT(*) FILTER (WHERE status = 'Delivered') AS delivered,
COUNT(*) AS total,
ROUND(100.0 * COUNT(*) FILTER (WHERE status = 'Delivered') / COUNT(*), 1) AS delivered_pct
FROM orders;Result:
| delivered | total | delivered_pct |
|---|---|---|
| 16 | 24 | 66.7 |
(1 row)
Watch for integer division: 100.0 forces decimal maths. (Aggregate functions)
2. Find duplicate values: which cities have more than one customer?
Show solution
SELECT city, COUNT(*) AS customers
FROM customers
GROUP BY city
HAVING COUNT(*) > 1;Result:
| city | customers |
|---|---|
| Bengaluru | 2 |
(1 row)
(HAVING)
3. Which customers have never placed an order?
Show solution
SELECT c.customer_id, c.customer_name
FROM customers c
WHERE NOT EXISTS (SELECT 1 FROM orders o WHERE o.customer_id = c.customer_id);Result:
| customer_id | customer_name |
|---|---|
| 12 | Divya Menon |
(1 row)
LEFT JOIN ... WHERE o.order_id IS NULL also works. Mention why you'd avoid NOT IN (NULLs). (EXISTS)
Joins and revenue
4. What is the net revenue by category from delivered orders?
Show solution
SELECT cat.category_name,
ROUND(SUM(oi.quantity * oi.unit_price * (1 - oi.discount_pct / 100)), 2) AS net_revenue
FROM order_items oi
JOIN orders o ON o.order_id = oi.order_id
JOIN products p ON p.product_id = oi.product_id
JOIN categories cat ON cat.category_id = p.category_id
WHERE o.status = 'Delivered'
GROUP BY cat.category_name
ORDER BY net_revenue DESC;Result:
| category_name | net_revenue |
|---|---|
| Electronics | 79383.65 |
| Fashion | 19621.35 |
| Home & Kitchen | 9730.05 |
| Beauty | 7481.50 |
| Sports | 3036.20 |
| Books | 2545.00 |
(6 rows)
5. What is the average order value (AOV) of delivered orders?
Show solution
The trap: averaging order lines instead of orders. Aggregate to one row per order first.
WITH order_totals AS (
SELECT o.order_id,
SUM(oi.quantity * oi.unit_price * (1 - oi.discount_pct / 100)) AS order_value
FROM orders o
JOIN order_items oi ON oi.order_id = o.order_id
WHERE o.status = 'Delivered'
GROUP BY o.order_id
)
SELECT ROUND(AVG(order_value), 2) AS aov
FROM order_totals;Result:
| aov |
|---|
| 7612.36 |
(1 row)
(CTEs)
6. List employees with their manager's name, including the CEO.
Show solution
SELECT e.employee_name, COALESCE(m.employee_name, 'No manager') AS manager
FROM employees e
LEFT JOIN employees m ON m.employee_id = e.manager_id
ORDER BY e.employee_id
LIMIT 5;Result:
| employee_name | manager |
|---|---|
| Rajesh Khanna | No manager |
| Neha Joshi | Rajesh Khanna |
| Amit Verma | Rajesh Khanna |
| Pooja Sinha | Rajesh Khanna |
| Suresh Rao | Neha Joshi |
(5 rows)
Window functions
7. Find the second-highest salary.
Show solution
WITH ranked AS (
SELECT salary, DENSE_RANK() OVER (ORDER BY salary DESC) AS rnk
FROM employees
)
SELECT DISTINCT salary AS second_highest
FROM ranked
WHERE rnk = 2;Result:
| second_highest |
|---|
| 260000.00 |
(1 row)
DENSE_RANK handles ties. The pre-window-function answer is SELECT MAX(salary) FROM employees WHERE salary < (SELECT MAX(salary) FROM employees). (Ranking functions)
8. Find the top-selling product (by units) in each category.
Show solution
WITH units AS (
SELECT p.category_id, p.product_name, SUM(oi.quantity) AS units
FROM order_items oi
JOIN products p ON p.product_id = oi.product_id
GROUP BY p.category_id, p.product_name
),
ranked AS (
SELECT *, RANK() OVER (PARTITION BY category_id ORDER BY units DESC) AS rnk
FROM units
)
SELECT category_id, product_name, units
FROM ranked
WHERE rnk = 1
ORDER BY category_id;Result:
| category_id | product_name | units |
|---|---|---|
| 1 | boAt Airdopes 141 | 6 |
| 2 | Men's Cotton Kurta | 4 |
| 3 | Steel Water Bottle 1L | 9 |
| 4 | Herbal Face Wash | 9 |
| 5 | The Psychology of Money | 5 |
| 6 | Yoga Mat 6mm | 4 |
(6 rows)
If two products in a category sold the same number of units, RANK would return both, while ROW_NUMBER would pick one arbitrarily. Say which behaviour you chose and why.
9. Find each customer's first order.
Show solution
WITH ordered AS (
SELECT customer_id, order_id, order_date,
ROW_NUMBER() OVER (PARTITION BY customer_id ORDER BY order_date, order_id) AS rn
FROM orders
)
SELECT customer_id, order_id, order_date
FROM ordered
WHERE rn = 1
ORDER BY customer_id
LIMIT 5;Result:
| customer_id | order_id | order_date |
|---|---|---|
| 1 | 1001 | 2025-01-05 |
| 2 | 1002 | 2025-01-18 |
| 3 | 1003 | 2025-02-02 |
| 4 | 1005 | 2025-03-11 |
| 5 | 1006 | 2025-03-29 |
(5 rows)
10. Calculate month-over-month growth in delivered revenue.
Show solution
WITH monthly AS (
SELECT DATE_TRUNC('month', o.order_date)::date AS month,
SUM(oi.quantity * oi.unit_price * (1 - oi.discount_pct / 100)) AS revenue
FROM orders o
JOIN order_items oi ON oi.order_id = o.order_id
WHERE o.status = 'Delivered'
GROUP BY 1
)
SELECT month,
ROUND(revenue) AS revenue,
ROUND(100 * (revenue - LAG(revenue) OVER (ORDER BY month))
/ NULLIF(LAG(revenue) OVER (ORDER BY month), 0), 1) AS mom_pct
FROM monthly
ORDER BY month
LIMIT 5;Result:
| month | revenue | mom_pct |
|---|---|---|
| 2025-01-01 | 32271 | NULL |
| 2025-02-01 | 28839 | -10.6 |
| 2025-03-01 | 3492 | -87.9 |
| 2025-04-01 | 2988 | -14.4 |
| 2025-05-01 | 3685 | 23.3 |
(5 rows)
NULLIF guards against dividing by zero. (LAG and LEAD)
11. Show a running total of delivered revenue by month.
Show solution
WITH monthly AS (
SELECT DATE_TRUNC('month', o.order_date)::date AS month,
SUM(oi.quantity * oi.unit_price * (1 - oi.discount_pct / 100)) AS revenue
FROM orders o
JOIN order_items oi ON oi.order_id = o.order_id
WHERE o.status = 'Delivered'
GROUP BY 1
)
SELECT month,
ROUND(revenue) AS revenue,
ROUND(SUM(revenue) OVER (ORDER BY month)) AS running_total
FROM monthly
ORDER BY month
LIMIT 5;Result:
| month | revenue | running_total |
|---|---|---|
| 2025-01-01 | 32271 | 32271 |
| 2025-02-01 | 28839 | 61110 |
| 2025-03-01 | 3492 | 64601 |
| 2025-04-01 | 2988 | 67589 |
| 2025-05-01 | 3685 | 71274 |
(5 rows)
Business metrics
12. What is the repeat-customer rate?
Share of customers who ordered who placed more than one order.
Show solution
WITH per_customer AS (
SELECT customer_id, COUNT(*) AS orders
FROM orders
GROUP BY customer_id
)
SELECT COUNT(*) AS ordering_customers,
COUNT(*) FILTER (WHERE orders > 1) AS repeat_customers,
ROUND(100.0 * COUNT(*) FILTER (WHERE orders > 1) / COUNT(*), 1) AS repeat_rate_pct
FROM per_customer;Result:
| ordering_customers | repeat_customers | repeat_rate_pct |
|---|---|---|
| 11 | 9 | 81.8 |
(1 row)
Clarify the definition first: all orders, or only delivered ones?
13. Which customers ordered in two consecutive months?
Show solution
WITH months AS (
SELECT DISTINCT customer_id, DATE_TRUNC('month', order_date)::date AS month
FROM orders
),
with_prev AS (
SELECT customer_id, month,
LAG(month) OVER (PARTITION BY customer_id ORDER BY month) AS prev_month
FROM months
)
SELECT DISTINCT customer_id
FROM with_prev
WHERE prev_month = (month - INTERVAL '1 month')::date
ORDER BY customer_id;Result:
| customer_id |
|---|
| 1 |
(1 row)
Reduce to one row per customer-month first, so two orders in the same month don't count as "consecutive".
14. Which products have never been sold?
Show solution
SELECT p.product_id, p.product_name
FROM products p
LEFT JOIN order_items oi ON oi.product_id = p.product_id
WHERE oi.product_id IS NULL;Result:
| product_id | product_name |
|---|---|
| 115 | Cricket Bat (Kashmir Willow) |
(1 row)
15. Delete duplicate rows, keeping one copy.
Set up a table with duplicates first:
CREATE TABLE signups (email VARCHAR(100), signup_date DATE);
INSERT INTO signups VALUES
('a@x.com', '2025-01-01'), ('a@x.com', '2025-01-01'),
('b@x.com', '2025-01-02'), ('c@x.com', '2025-01-03'), ('c@x.com', '2025-01-03');Result:
Query OK, 5 row(s) affected.
Show solution
With no unique ID, PostgreSQL's hidden ctid (physical row address) can tell copies apart:
DELETE FROM signups
WHERE ctid IN (
SELECT ctid
FROM (
SELECT ctid, ROW_NUMBER() OVER (PARTITION BY email, signup_date ORDER BY ctid) AS rn
FROM signups
) t
WHERE rn > 1
);
SELECT * FROM signups ORDER BY email;Result:
| signup_date | |
|---|---|
| a@x.com | 2025-01-01 |
| b@x.com | 2025-01-02 |
| c@x.com | 2025-01-03 |
(3 rows)
If the table has an ID column, use it instead of ctid. Another approach that works everywhere: CREATE TABLE clean AS SELECT DISTINCT * FROM signups, then swap the tables.
How to keep practising
- Re-solve each question with a different technique (a join instead of
EXISTS, a subquery instead of a window function). - Change the question: top 3 instead of top 1, weekly instead of monthly, delivered orders only.
- Work through the Skillancy SQL practice questions.
Frequently asked questions
What SQL topics are asked in data analyst interviews?
Mostly joins, GROUP BY with HAVING, CASE WHEN, subqueries and CTEs, and window functions (ROW_NUMBER, RANK, LAG). Expect at least one question on NULL handling and one on avoiding double counting.
How should I approach a SQL interview question?
Clarify the grain and edge cases first (ties, NULLs, cancelled orders), say your plan out loud, write the query in CTE steps, then check the result on a small example.
How many SQL questions should I practise?
Quality beats quantity. Being able to solve the 15 patterns on this page from scratch, and explain them, covers most analyst interviews. Then practise variations.
Keep learning SQL
One email a week with SQL and analytics guides, new practice questions and cohort dates. No spam, unsubscribe anytime.
Prefer a one-page summary? Download the free SQL cheat sheet (PDF)