Write a query to retrieve the names of customers who have made multiple purchases and the count of their purchases.

Assuming you are using a SQL database and you have a table named 'purchases' with 'customer_name' and 'purchase_id' as columns, you can use the following query:

SELECT customer_name, COUNT(purchase_id) as purchase_count
FROM purchases
GROUP BY customer_name
HAVING COUNT(purchase_id) > 1;

Here's the step by step explanation:

1. SELECT customer_name, COUNT(purchase_id) as purchase_count: This line is selecting the columns we are interested in. We want the names of the customers and the count of their purchases. The COUNT(purchase_id) function counts the number of purchase IDs associated with each customer, and as purchase_count renames this column in the output.

2. FROM purchases: This line is specifying the table we are selecting data from, which is the 'purchases' table.

3. GROUP BY customer_name: This line groups the data by the customer name. This means that the output will have one row per customer.

4. HAVING COUNT(purchase_id) > 1: This line is a filter that only includes customers who have made more than one purchase. The HAVING clause is used instead of WHERE because it allows us to filter based on the result of an aggregate function (COUNT in this case).