Interview Questions
Sample Schema - Ecommerce Analytics
+-------------------+
| customers |
+-------------------+
| customer_id (PK) |
| name |
| email |
| city |
| signup_date |
+-------------------+
|
| 1..* (a customer can place many orders)
|
v
+-------------------+
| orders |
+-------------------+
| order_id (PK) |
| customer_id (FK) ---> customers.customer_id
| order_date |
| total_amount |
+-------------------+
|
| 1..* (an order has many order_items)
|
v
+-------------------+
| order_items |
+-------------------+
| order_item_id (PK)|
| order_id (FK) ---> orders.order_id
| product_id (FK) ---> products.product_id
| quantity |
| price_per_unit |
+-------------------+
+-------------------+
| products |
+-------------------+
| product_id (PK) |
| name |
| category |
| price |
+-------------------+
^
| (each item refers to one product)
|
+-------------------+
| payments |
+-------------------+
| payment_id (PK) |
| order_id (FK) ---> orders.order_id
| payment_date |
| amount |
| payment_method |
+-------------------+
Basic Questions (SQL Fundamentals)
Q1. Fetch all customers who signed up in 2024.
SELECT * FROM customers
WHERE EXTRACT(YEAR FROM signup_date) = 2024;
Q2. Get the total number of orders placed by each customer.
SELECT customer_id, COUNT(*) AS order_count
FROM orders
GROUP BY customer_id;
Q3. Find customers who haven’t placed any orders.
SELECT c.customer_id, c.name
FROM customers c
LEFT JOIN orders o ON c.customer_id = o.customer_id
WHERE o.order_id IS NULL;
Intermediate Questions (Joins, Aggregation, Filtering)
Q4. Retrieve top 3 products by total revenue.
SELECT p.name, SUM(oi.quantity * oi.price_per_unit) AS revenue
FROM order_items oi
JOIN products p ON oi.product_id = p.product_id
GROUP BY p.name
ORDER BY revenue DESC
LIMIT 3;
Q5. Get total revenue by product category for the current year.
SELECT p.category, SUM(oi.quantity * oi.price_per_unit) AS total_revenue
FROM order_items oi
JOIN products p ON oi.product_id = p.product_id
JOIN orders o ON oi.order_id = o.order_id
WHERE EXTRACT(YEAR FROM o.order_date) = EXTRACT(YEAR FROM CURRENT_DATE)
GROUP BY p.category;
Q6. Find customers who have spent more than ₹10,000 in total.
SELECT c.customer_id, c.name, SUM(o.total_amount) AS total_spent
FROM customers c
JOIN orders o ON c.customer_id = o.customer_id
GROUP BY c.customer_id, c.name
HAVING SUM(o.total_amount) > 10000;
Advanced Questions (Subqueries, CTEs, Window Functions)
Q7. Find the latest order for each customer.
SELECT customer_id, order_id, order_date
FROM (
SELECT customer_id, order_id, order_date,
ROW_NUMBER() OVER (PARTITION BY customer_id ORDER BY order_date DESC) AS rn
FROM orders
) t
WHERE rn = 1;
Q8. Get the top 5 customers by lifetime value (LTV).
SELECT c.name, SUM(o.total_amount) AS ltv
FROM customers c
JOIN orders o ON c.customer_id = o.customer_id
GROUP BY c.name
ORDER BY ltv DESC
LIMIT 5;
Q9. Find the percentage contribution of each category to total revenue.
WITH category_revenue AS (
SELECT p.category, SUM(oi.quantity * oi.price_per_unit) AS revenue
FROM order_items oi
JOIN products p ON oi.product_id = p.product_id
GROUP BY p.category
)
SELECT category,
revenue,
ROUND(100 * revenue / SUM(revenue) OVER (), 2) AS pct_of_total
FROM category_revenue;
Expert Questions (Optimization, Edge Cases, Tricky Logic)
Q10. Identify orders where payment is missing or partial.
SELECT o.order_id, o.total_amount, COALESCE(SUM(p.amount), 0) AS paid_amount
FROM orders o
LEFT JOIN payments p ON o.order_id = p.order_id
GROUP BY o.order_id, o.total_amount
HAVING COALESCE(SUM(p.amount), 0) < o.total_amount;
Q11. For each customer, show month-over-month growth in spending.
WITH monthly_spend AS (
SELECT customer_id,
DATE_TRUNC('month', order_date) AS month,
SUM(total_amount) AS spend
FROM orders
GROUP BY customer_id, DATE_TRUNC('month', order_date)
)
SELECT customer_id,
month,
spend,
spend - LAG(spend) OVER (PARTITION BY customer_id ORDER BY month) AS growth
FROM monthly_spend;
Q12. Find the most popular product category per city.
WITH city_category_sales AS (
SELECT c.city, p.category, SUM(oi.quantity) AS qty
FROM order_items oi
JOIN orders o ON oi.order_id = o.order_id
JOIN customers c ON o.customer_id = c.customer_id
JOIN products p ON oi.product_id = p.product_id
GROUP BY c.city, p.category
),
ranked AS (
SELECT *, ROW_NUMBER() OVER (PARTITION BY city ORDER BY qty DESC) AS rn
FROM city_category_sales
)
SELECT city, category, qty
FROM ranked
WHERE rn = 1;
Bonus Topics for Senior-Level Interviews
| Topic | Sample Question |
|---|---|
| Indexes | Explain when to use composite indexes and how they affect WHERE clause performance. |
| Query Optimization | How would you debug a slow query that joins 5 tables? |
| Transactions | What happens if one statement in a transaction fails? |
| Isolation Levels | Explain “phantom read” and how to prevent it. |
| Partitioning | How would you partition the orders table by year for performance? |
| CTE vs Subquery | When to prefer one over the other? |
| Window Functions | How are ROW_NUMBER() and RANK() different? |
ChatGPT - SQL interview questions
Sample Schema
Real estate agents purchase our advertising product, Premier Agent, on a monthly basis. Let’s assume we have a table which stores AgentID, Month, Spend.
Write a query to track the following 4 metrics every month (relative to the prior month):
- New Purchase, which is the spend from a new agent who joined Zillow’s platform that month
- Mod-Up, which is the increase in spend from an agent in a given month relative to the previous month
- Cancel, which is the spend we’ve lost from an agent who cancelled their spend at the end of the previous month
- Mod-Down, which is the decrease in spend from an agent in a given month relative to the previous month
The final product should be a summary table showing the total for each metric.
Monthly_Spend
| Zuid | Month | Spend |
|---|---|---|
| 123 | 2020-01-01 | $100 |
| 123 | 2020-02-01 | $150 |
| 456 | 2020-01-01 | $75 |
- [execution time limit] 10 seconds (mysql)
- [memory limit] 1 GB
SQL Questions
- Find the 3rd highest salary in a company, returning all employees who have that salary.
- Find the top 2 scorers per subject from a marks table.
- Find the highest paid employee in each department.
- Find the second highest salary in each department.
- Find students who scored more than the class average.
- Find the cumulative total salary per department ordered by salary descending.
- Find the employee(s) whose salary is just below the maximum salary in the company (difference-based).
- List employees ranked by salary within each department using
ROW_NUMBER(). - Find products whose price is above the average price in their category.
- Find the difference between each employee’s salary and the department average.
- Find the running total of sales ordered by sale date.
- Find the first transaction per customer by date.
- Find the top 3 selling products based on total revenue.
- Find students who scored exactly the median score.
- Find employees who earn more than their manager.
- Find consecutive login days for each user (window + gaps).
- Find the latest salary record per employee from a historical salary table.
- Find percentage contribution of each product to total sales.
- Find customers who purchased more than once within 7 days.
- Find each employee’s salary rank across the company and within their department.