Calculate Customer Lifetime Value with INNER JOIN in SQL

Question

Given `customers` (customer_id, name) and `orders` (order_id, customer_id, total_amount), write a SQL query to return each customer's `name`, total number of orders `order_count`, and total spend `total_spent`. Exclude customers who haven't placed orders.

Accepted Answer

Optimal Solution:
SELECT 
  c.name,
  COUNT(o.order_id) AS order_count,
  SUM(o.total_amount) AS total_spent
FROM customers c
INNER JOIN orders o ON c.customer_id = o.customer_id
GROUP BY c.customer_id, c.name
ORDER BY total_spent DESC;

Technical Explanation:
An `INNER JOIN` connects customers with orders on `customer_id`. Grouping by `customer_id` and `name` aggregates the order count and sum of amounts.

customer_id	name	order_id	total_amount
1	Aarav	101	1500
1	Aarav	102	2500
2	Bhavna	103	3000
3	Chirag	NULL	NULL

name	order_count	total_spent
Aarav	2	4000
Bhavna	1	3000