Here is a practical problem you might face as a data analyst.
You have two tables: customers and orders.
The business team wants a report showing each customer's order count, total spending, latest order date and customer category.
Customer categories:
- High Value: ₹1,00,000 or above
- Medium Value: ₹50,000 to below ₹1,00,000
- Low Value: Below ₹50,000
customers
customer_id | customer_name
101 | Rahul
102 | Priya
103 | Amit
104 | Neha
orders
order_id | customer_id | order_date | amount
1 | 101 | 2026-01-10 | 40000
2 | 101 | 2026-02-15 | 35000
3 | 101 | 2026-03-20 | 30000
4 | 102 | 2026-01-18 | 25000
5 | 102 | 2026-03-12 | 30000
6 | 103 | 2026-02-05 | 20000
Your Challenge
Write a SQL query to:
- Display all customers, including those without orders.
- Calculate total orders and revenue.
- Find the latest order date.
- Assign customer categories using CASE WHEN.
- Keep the original tables unchanged.
Think before you scroll!
The SQL Solution (Oracle)
SELECT
c.customer_id,
c.customer_name,
COUNT(o.order_id) AS total_orders,
NVL(SUM(o.amount), 0) AS total_revenue,
MAX(o.order_date) AS latest_order_date,
CASE
WHEN NVL(SUM(o.amount), 0) >= 100000
THEN 'High Value'
WHEN NVL(SUM(o.amount), 0) >= 50000
THEN 'Medium Value'
ELSE 'Low Value'
END AS customer_category
FROM customers c
LEFT JOIN orders o
ON c.customer_id = o.customer_id
GROUP BY c.customer_id, c.customer_name
ORDER BY c.customer_id;
Expected Output
customer_id | customer_name | orders | revenue | latest_date | category
101 | Rahul | 3 | 105000 | 2026-03-20 | High Value
102 | Priya | 2 | 55000 | 2026-03-12 | Medium Value
103 | Amit | 1 | 20000 | 2026-02-05 | Low Value
104 | Neha | 0 | 0 | NULL | Low Value
Why Does This Work?
- LEFT JOIN keeps customers without orders.
- COUNT(o.order_id) counts actual orders.
- SUM() calculates total customer spending.
- NVL() replaces NULL revenue with 0.
- MAX() finds the latest order date.
- CASE WHEN assigns customer categories.
- GROUP BY creates one report row per customer.
One Thing to Remember:
With LEFT JOIN, COUNT(*) can return 1 for a customer without orders. Use COUNT(o.order_id) to get the correct count of 0.
Your Turn
How would you add average order value to this report?
Share your SQL query in the comments!
