15 Practical SQL Questions & Answers
1. Find the total number of customers from each country.
select
country,
count(*) as customer_count
from customers
group by country
2. List the top 5 products by total sales amount.
select p.name,sum(oi.quantity*oi.price) as total_sales
from order_items as oi
JOIN
products as p
on oi.product_id=p.product_id
GROUP By p.name
order by total_sales DESC LIMIT 5
3. Calculate the average order value (AOV) across all customers.
SELECT avg(total_amount) as AOV
from orders
4. Find the customer who spent the most in total.
select c.name,sum(o.total_amount) as total
from orders as o
JOIN
customers as c on o.customer_id=c.customer_id
group by c.name
order by total DESC LIMIT 1
5. List all orders placed in the last 30 days.
select * from orders
WHERE
order_date>=(CURRENT_DATE()-INTERVAL 30 DAY)
Page 1
6. Get the total quantity sold for each product category.
select p.category,sum(oi.quantity) as total_quantity
from order_items as oi
JOIN
products as p
on oi.product_id=p.product_id
GROUP by category
7. Identify customers who have not placed any order.
select * from customers
WHERE
customer_id not in(select customer_id from orders)
8. Show the total revenue per month.
select date_format(order_date,'%M') as Revenue_Month ,sum(total_amount) as tota
FROM orders
group BY Revenue_Month
9. List the top 3 countries with the highest number of orders.
SELECT c.country,count(*) as total_orders
from orders as o
JOIN
customers as c
ON o.customer_id=c.customer_id
GROUP by c.country
ORDER by total_orders DESC LIMIT 3
10. Calculate the reorder rate (orders with more than one item).
SELECT count(*) * 100/(select count(*) from orders) as Reorder_Rate
FROM
(select order_id from order_items
GROUP by order_id
HAVING count(*)>1)
as multiple_Order
11. Find the average number of items per order.
Page 2
with item_quantity as(
select order_id,sum(quantity) as total_quantity
from order_items
GROUP BY
order_id
)
select AVG(total_quantity) as avg_number_order
from item_quantity
12. List products that have never been ordered.
select product_id,name from products
WHERE
product_id NOT IN (select product_id from order_items)
13. Show the trend of orders over time (monthly count).
SELECT DATE_FORMAT(order_date, '%Y-%m') AS month, COUNT(*) AS total_orders
FROM orders
GROUP BY month
ORDER BY month
14. Determine the most popular payment method.
SELECT payment_method,count(*)
from payments
GROUP BY payment_method
ORDER by count(*) DESC LIMIT 1
15. Find the percentage of returning customers (placed more than
one order).
with recurring_customers as
(
Select customer_id
from orders
GROUP by customer_id
HAVING count(*)>1
)
select count(*)*100/(select count(customer_id) from customers)
from recurring_customers
Page 3
