Customer Purchase Summary

Problem

Table: Purchases

+---------------+---------+
| Column Name   | Type    |
+---------------+---------+
| purchase_date | date    |
| customer_id   | int     |
| product       | varchar |
+---------------+---------+
There is no primary key for this table. It may contain duplicates.
Each row of this table contains the purchase date, customer ID, and product name for each product purchased by customers.

Problem Definition

Write a solution to find for each customer the total number of distinct products purchased and a list of distinct products bought.

The products bought by each customer should be sorted lexicographically.

Return the result table ordered by customer_id.

Example

Input: 
Purchases table:
+---------------+-------------+------------+
| purchase_date | customer_id | product    |
+---------------+-------------+------------+
| 2020-05-01    | 1           | Milk       |
| 2020-05-01    | 2           | Eggs       |
| 2020-06-01    | 2           | Milk       |
| 2020-06-01    | 3           | Bread      |
| 2020-06-02    | 1           | Water      |
| 2020-07-01    | 2           | Eggs       |
| 2020-07-01    | 3           | Milk       |
+---------------+-------------+------------+

Output: 
+-------------+----------+-------------------+
| customer_id | num_products | products       |
+-------------+----------+-------------------+
| 1           | 2        | Milk,Water        |
| 2           | 2        | Eggs,Milk         |
| 3           | 2        | Bread,Milk        |
+-------------+----------+-------------------+

Try It Yourself

-- TODO: Write your user queries here

Solution

To solve this problem, the approach involves using SQL queries to analyze the Purchases table and find the total number of distinct products purchased and a list of distinct products bought by each customer. The desired output requires counting the distinct products and listing their names for each unique customer_id.

The COUNT function with the DISTINCT keyword is used to calculate the number of distinct products purchased by each customer. This information is captured in the "num_products" column. Additionally, the GROUP_CONCAT function concatenates the distinct product names for each customer. The ORDER BY clause is used within the GROUP_CONCAT function to ensure that the product names are sorted lexicographically.

The results are then grouped by customer_id using the GROUP BY clause, ensuring that the calculations are performed for each unique customer. The final step involves ordering the result table by customer_id in ascending order, as the problem statement specifies.

SELECT customer_id,
       Count(DISTINCT product) AS num_products,
       Group_concat(DISTINCT product ORDER BY product ASC SEPARATOR ',') AS products
FROM   Purchases
GROUP  BY customer_id
ORDER  BY customer_id ASC; 

Let's break down the query into sub-steps:

Step 1: Count the Number of Distinct Products Purchased by Each Customer

We use the COUNT(DISTINCT product) to count the number of distinct products purchased by each customer.

SELECT
    customer_id,
    COUNT(DISTINCT product) AS num_products
FROM
    purchases
GROUP BY
    customer_id;

Output After Step 1:

+-------------+--------------+
| customer_id | num_products |
+-------------+--------------+
| 1           | 2            |
| 2           | 2            |
| 3           | 2            |
+-------------+--------------+

Step 2: Concatenate Distinct Sorted Product Names for Each Customer

We use GROUP_CONCAT to concatenate the product names for each customer, sorting them lexicographically.

SELECT
    customer_id,
    num_products,
    GROUP_CONCAT(DISTINCT product ORDER BY product ASC SEPARATOR ',') AS products
FROM 
    purchases
GROUP BY
    customer_id;

Output After Step 2:

+-------------+--------------+-----------------+
| customer_id | num_products | products        |
+-------------+--------------+-----------------+
| 1           | 2            | Milk,Water      |
| 2           | 2            | Eggs,Milk       |
| 3           | 2            | Bread,Milk      |
+-------------+--------------+-----------------+

Step 3: Order the Result

We order the result table by customer_id.

ORDER BY customer_id ASC;

Final Output:

+-------------+--------------+-----------------+
| customer_id | num_products | products        |
+-------------+--------------+-----------------+
| 1           | 2            | Milk,Water      |
| 2           | 2            | Eggs,Milk       |
| 3           | 2            | Bread,Milk      |
+-------------+--------------+-----------------+

I just read a lesson on **Customer Purchase Summary** (Databases) and want to truly understand it. Explain Customer Purchase Summary from first principles using ONE vivid real-world analogy and a visual mental model — draw it as ASCII art or a clear step-by-step diagram — with a concrete example using real numbers. Then ask me one question to check I got the mental picture, and wait for my reply. If you're unsure or a claim isn't standard, say so and reason from first principles instead of guessing.

Teach me **Customer Purchase Summary** interactively. Ask me ONE guiding question at a time, wait for my answer, and adapt to my confusion — build the idea with me step by step instead of explaining it all at once. If you're unsure or a claim isn't standard, say so and reason from first principles instead of guessing.

Quiz me on **Customer Purchase Summary** with 5 questions, easy to tricky, ONE at a time. Tell me if each answer is right; at the end, explain clearly what I got wrong and why. If you're unsure or a claim isn't standard, say so and reason from first principles instead of guessing.

Help me remember **Customer Purchase Summary** for the long term: give the one-sentence intuition, a memorable hook/mnemonic, a tiny worked example, and 3 active-recall flashcards (Q -> A). If you're unsure or a claim isn't standard, say so and reason from first principles instead of guessing.