Problem

Given two tables, "users" and "transactions", write a SQL query to find the total number of transactions for each user.
users:
id | name
1 | John
2 | Jane
3 | Joe
4 | Jill
transactions:
id | user_id | amount
1 | 1 | 100
2 | 1 | 50
3 | 2 | 75
4 | 3 | 25
5 | 3 | 50
6 | 4 | 150
Example output:
id | name | total_transactions
1 | John | 3
2 | Jane | 1
3 | Joe | 2
4 | Jill | 1

Solution

SELECT users.id, users.name, COUNT(transactions.id) AS total_transactions
FROM users
LEFT JOIN transactions ON users.id = transactions.user_id
GROUP BY users.id

/*

The solution above is optimal because it uses a LEFT JOIN to ensure that all users are included in the result, even if they have no transactions.

*/