1.8 Sample analytics | E commerce
Here are some sample problem statements that a data analyst may have to solve in the e-commerce or any sales data base based company.
1. Write a query to find the names and ages of all customers who have placed an order in the past month.
Customers table:
| Customer ID | Name | Age |
|---|---|---|
| 1 | Alice | 25 |
| 2 | Bob | 32 |
| 3 | Carol | 21 |
| 4 | Dave | 28 |
Orders table:
| Order ID | Customer ID | Order Date |
|---|---|---|
| 1 | 1 | 2021-12-01 |
| 2 | 2 | 2021-12-03 |
| 3 | 3 | 2021-11-30 |
| 4 | 1 | 2021-12-15 |
| 5 | 4 | 2021-12-20 |
Solution:
SELECT c.name, c.age
FROM customers c
JOIN orders o
ON c.customer_id = o.customer_id
WHERE o.order_date BETWEEN DATEADD(month, -1, GETDATE()) AND GETDATE();
This query returns:
| Name | Age |
|---|---|
| Alice | 25 |
| Bob | 32 |
| Dave | 28 |
2. Write a query to find the top 3 best-selling products by total quantity sold.
Products table:
| Product ID | Name | Price |
|---|---|---|
| 1 | T-shirt | 20 |
| 2 | Jeans | 50 |
| 3 | Jacket | 80 |
| 4 | Sneakers | 40 |
Order_items table:
| Order ID | Product ID | Quantity |
|---|---|---|
| 1 | 1 | 2 |
| 1 | 2 | 1 |
| 2 | 2 | 2 |
| 2 | 3 | 1 |
| 3 | 3 | 3 |
| 4 | 1 | 3 |
| 4 | 2 | 2 |
| 5 | 3 | 1 |
| 5 | 4 | 2 |
Solution:
SELECT p.name, SUM(oi.quantity) as Total Quantity
FROM products p
JOIN order_items oi
ON p.product_id = oi.product_id
GROUP BY p.name
ORDER BY Total Quantity DESC
LIMIT 3;
This query returns:
| Name | Total Quantity |
|---|---|
| Jacket | 4 |
| T-shirt | 5 |
| Jeans | 3 |
3. Write a query to find the average order value for each customer.
Customers table:
| Customer ID | Name | Age |
|---|---|---|
| 1 | Alice | 25 |
| 2 | Bob | 32 |
| 3 | Carol | 21 |
| 4 | Dave | 28 |
Orders table:
| Order ID | Customer ID | Total Price |
|---|---|---|
| 1 | 1 | 100 |
| 2 | 2 | 150 |
| 3 | 3 | 50 |
| 4 | 1 | 200 |
| 5 | 4 | 75 |
Solution:
SELECT c.name, AVG(o.total_price) as Average Order Value
FROM customers c
JOIN orders o
ON c.customer_id = o.customer_id
GROUP BY c.name;
This query returns:
| Name | Average Order Value |
|---|---|
| Alice | 150 |
| Bob | 150 |
| Carol | 50 |
| Dave | 75 |
4. Write a query to find the most popular product among customers who are under the age of 25.
Products table:
| Product ID | Name | Price |
|---|---|---|
| 1 | T-shirt | 20 |
| 2 | Jeans | 50 |
| 3 | Jacket | 80 |
| 4 | Sneakers | 40 |
Order items table:
| Order ID | Product ID | Quantity |
|---|---|---|
| 1 | 1 | 2 |
| 1 | 2 | 1 |
| 2 | 2 | 2 |
| 2 | 3 | 1 |
| 3 | 3 | 3 |
| 4 | 1 | 3 |
| 4 | 2 | 2 |
| 5 | 3 | 1 |
| 5 | 4 | 2 |
Customers table:
| Customer ID | Name | Age |
|---|---|---|
| 1 | Alice | 25 |
| 2 | Bob | 32 |
| 3 | Carol | 21 |
| 4 | Dave | 28 |
Solution:
SELECT p.name, COUNT(o.order_id) as Order Count
FROM products p
JOIN order_items oi
ON p.product_id = oi.product_id
JOIN orders o
ON oi.order_id = o.order_id
JOIN customers c
ON o.customer_id = c.customer_id
WHERE c.age < 25
GROUP BY p.name
ORDER BY Order Count DESC
LIMIT 1;
This query returns:
| Name | Order Count |
|---|---|
| Jacket | 2 |