Question

How many days has each customer visited the restaurant?

Solution

SELECT customer_id, COUNT(DISTINCT order_date)
FROM sales
GROUP BY customer_id;

Walkthrough

Let’s start by simplifying the question to an individual customer.

Which days did customer A visit the restaurant?

SELECT order_date
FROM sales
WHERE customer_id = 'A';

Okay. There’s repeats though. We can get rid of the duplicates if we apply DISTINCT to the order_date.

SELECT DISTINCT(order_date)
FROM sales
WHERE customer_id = 'A';

Now, we could get the COUNT of these distinct dates to get the number of different days that customer A visited the restaurant.

SELECT COUNT(DISTINCT(order_date))
FROM sales
WHERE customer_id = 'A';

We’d be done if we could generalize this over all customers and get one row per customer_id. So, GROUP BY is our friend.

SELECT customer_id, COUNT(DISTINCT order_date)
FROM sales
GROUP BY customer_id;