Problem

Given a table of data representing customer orders, write a SQL query to find the total number of orders for each customer.
Input:
customer_orders
| customer_id | order_id |
| ------------|----------|
| 1           | 1001     |
| 1           | 1002     |
| 2           | 1003     |
| 2           | 1004     |
| 3           | 1005     |
Output:
| customer_id | total_orders |
| ------------|--------------|
| 1           | 2            |
| 2           | 2            |
| 3           | 1            |

Solution

SELECT customer_id, COUNT(order_id) AS total_orders
FROM customer_orders
GROUP BY customer_id;

/*

The solution above is optimal because it uses the GROUP BY clause to group the customer_id column and then uses the COUNT function to count the number of orders for each customer.

*/