SQL Interview Questions for Intern (Medium)
Tables
customers
customer_id, customer_name, city, customer_type
101 Rahul Chennai Premium
102 Priya Bangalore Regular
103 Karthik Chennai Premium
104 Sneha Hyderabad Basic
105 Arun Bangalore Regular
orders
order_id, customer_id, order_date, amount
1001 101 2026-01-05 2500
1002 101 2026-02-10 4000
1003 102 2026-02-15 1800
1004 103 2026-03-01 5200
1005 104 2026-03-05 900
1006 101 2026-03-20 3000
1007 105 2026-04-10 4500
order_items
item_id, order_id, product, category, quantity, price
1 1001 Laptop Electronics 1 2500
2 1002 Mobile Electronics 1 4000
3 1003 Shoes Fashion 2 900
4 1004 Laptop Electronics 2 2600
5 1005 T-shirt Fashion 3 300
6 1006 Headphones Electronics 2 1500
7 1007 Watch Accessories 1 4500
Q1
Display each customer's name, number of orders and total amount spent.
Answer:
SELECT c.customer_name, COUNT(o.order_id) total_orders, SUM([Link])
total_spent FROM customers c JOIN orders o ON c.customer_id=o.customer_id
GROUP BY c.customer_name ORDER BY total_spent DESC;
Q2
Using a CTE, show customers spending >5000.
Answer:
WITH s AS (SELECT customer_id,SUM(amount) total FROM orders GROUP BY
customer_id) SELECT c.customer_name,[Link] FROM s JOIN customers c ON
c.customer_id=s.customer_id WHERE [Link]>5000;
Q3
Rank customers by total spending.
Answer:
WITH s AS (SELECT c.customer_name,SUM(amount) total FROM customers c JOIN
orders o ON c.customer_id=o.customer_id GROUP BY c.customer_name) SELECT
*, RANK() OVER(ORDER BY total DESC) spending_rank FROM s;
Q4
Show previous order date for each customer.
Answer:
SELECT customer_id,order_date,LAG(order_date) OVER(PARTITION BY
customer_id ORDER BY order_date) previous_order FROM orders;
Q5
Latest order for each customer.
Answer:
WITH x AS (SELECT *,ROW_NUMBER() OVER(PARTITION BY customer_id ORDER BY
order_date DESC) rn FROM orders) SELECT
c.customer_name,x.order_date,[Link] FROM x JOIN customers c ON
c.customer_id=x.customer_id WHERE rn=1;
Q6
Category sales.
Answer:
SELECT [Link],SUM([Link]*[Link]) total_sales,SUM(quantity)
total_qty FROM order_items oi GROUP BY [Link] ORDER BY total_sales
DESC;
Q7
Classify customers by spending.
Answer:
WITH s AS (SELECT c.customer_name,SUM(amount) total FROM customers c JOIN
orders o ON c.customer_id=o.customer_id GROUP BY c.customer_name) SELECT
customer_name,total,CASE WHEN total>7000 THEN 'High Value' WHEN
total>=3000 THEN 'Medium Value' ELSE 'Low Value' END customer_category
FROM s;
Q8
Top-selling product in each category.
Answer:
WITH p AS (SELECT category,product,SUM(quantity) qty,DENSE_RANK()
OVER(PARTITION BY category ORDER BY SUM(quantity) DESC) rnk FROM
order_items GROUP BY category,product) SELECT category,product,qty FROM p
WHERE rnk=1;