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…