Customer Order Statistics

intermediate

E-commerce dashboard needs to show each customer with their order count, total spent, and last order date.

SELECT

c.id,

c.name,

c.email,

(SELECT COUNT(*)

FROM orders

WHERE customer_id = c.id) AS order_count,

COALESCE(

(SELECT SUM(total)

FROM orders

WHERE customer_id = c.id),

0

) AS total_spent,

(SELECT MAX(order_date)

FROM orders

WHERE customer_id = c.id) AS last_order_date

FROM customers c

ORDER BY total_spent DESC

LIMIT 10;

id | name | email | order_count | total_spent | last_order_date

1 | John | john@email.com | 5 | 1499.95 | 2024-01-15

2 | Jane | jane@email.com | 3 | 899.97 | 2024-01-10

3 | Bob | bob@email.com | 0 | 0.00 | NULL

Three correlated subqueries calculate per-customer stats. Each executes once per customer. COALESCE handles NULL for customers with no orders. Needs index on orders(customer_id) for performance.

All

Employee Salary vs Department Average

intermediate

HR wants to compare each employee salary to their department average to identify outliers.

SELECT

e.name,

e.department,

e.salary,

(SELECT ROUND(AVG(salary), 0)

FROM employees e2

WHERE e2.department = e.department) AS dept_avg,

e.salary - (SELECT AVG(salary)

FROM employees e2