Group Sold Products By The Date | Leetcode 1484

Welcome to Chapter 48 of the Crack SQL Interview in 50 Questions series. In this chapter, we explore a classic MySQL problem that tests your ability to aggregate text, handle distinct multi-row values, and format concatenated strings in a specific lexicographical order.

1. Problem Statement & Schema Overview

We are given a single database table named Activities that tracks market sales data. The table contains no primary key and consists of two columns:

Objective

Write a SQL query to find for each date:

1. The number of different (distinct) products sold.
2. The names of the sold products grouped together.
3. The sold product names for each date must be sorted lexicographically (dictionary order: A to Z).
4. The final result table must be ordered by sell_date.

2. Real-Life Analogy: Daily Marketplace Ledger

Real-Life Analogy: Imagine you manage a bustling farmer's market stall. Every day, people buy various items. At closing time, you take a blank sheet of paper to create a daily summary report.

Instead of listing every single receipt line-by-line, you:

1. Group all sales by the specific calendar date.
2. Count how many unique types of items were sold (ignoring duplicate purchases of the exact same item on the same day).
3. Stitch together a neat, comma-separated inventory list of those items in alphabetical order so customers or managers can read them instantly without clutter.

3. Input Data Example vs. Expected Output

Input Table: Activities

Expected Output Table

Note on Duplicates & Ordering: Notice how on 2020-06-02, "Mask" appears twice in the raw input table, but our output counts it only once (num_sold = 1) and displays it once. Furthermore, on 2020-05-30, the items are sorted alphabetically: Basketball (B) comes before Headphone (H), which comes before T-Shirt (T).

4. Naive Approach vs. Production-Ready MySQL Approach

The Naive Approach

A beginner might attempt to select columns directly and use basic aggregate functions:

-- Naive / Incomplete Attempt
SELECT sell_date, COUNT(product), product 
FROM Activities 
GROUP BY sell_date;

Why this fails:

• Standard SQL throws an error because non-aggregated columns like product cannot be selected alongside an aggregate function (COUNT) without being grouped or concatenated.
• It doesn't handle distinct duplicate filtering.
• It lacks string aggregation and lexicographical ordering required for multi-item rows.

The Production-Ready MySQL Approach

To solve this cleanly, we need three distinct SQL mechanisms:

1. GROUP BY sell_date: Collapses rows sharing the same calendar date into a single summary row.
2. COUNT(DISTINCT product): Counts unique product occurrences, ignoring duplicate entries on the same day.
3. GROUP_CONCAT(): A specialized MySQL function that concatenates strings from multiple rows into a single string.

5. Deep Dive into MySQL's GROUP_CONCAT() Function

The core challenge of this problem is transforming multiple rows of products into a single comma-separated string per date. MySQL provides the powerful GROUP_CONCAT() function for this exact use case.

Syntax Anatomy of GROUP_CONCAT

GROUP_CONCAT(
    [DISTINCT] column_name
    [ORDER BY column_name ASC/DESC]
    [SEPARATOR 'string_separator']
)

• DISTINCT: Ensures that if a product is sold multiple times on the same date, it appears only once in the concatenated string.
• ORDER BY: Sorts the items internally before concatenating them (crucial for satisfying the lexicographical requirement).
• SEPARATOR: Defines the character used between items. By default, MySQL uses a comma (,), making the explicit declaration optional, but highly recommended for clarity.

6. Step-by-Step Query Construction

Let's build our final SQL query piece by piece.

Step 1: Define the Source Table & Grouping

We pull data from our base table and collapse rows by date.

FROM Activities
GROUP BY sell_date

Step 2: Select sell_date and Unique Count (num_sold)

We extract the date and count distinct products.

SELECT 
    sell_date,
    COUNT(DISTINCT product) AS num_sold

Step 3: Concatenate Products with GROUP_CONCAT

We add our string aggregation function, making sure to apply DISTINCT, sort them alphabetically, and separate them with a comma.

    GROUP_CONCAT(
        DISTINCT product
        ORDER BY product
        SEPARATOR ','
    ) AS products

Step 4: Apply Overall Result Ordering

Finally, we ensure the output table is sorted chronologically by date, and secondarily by product name if needed.

ORDER BY sell_date, product;

7. Complete Production SQL Query

Here is the complete, executed, and verified MySQL query:

SELECT 
    sell_date,
    COUNT(DISTINCT product) AS num_sold,
    GROUP_CONCAT(
        DISTINCT product
        ORDER BY product
        SEPARATOR ','
    ) AS products
FROM 
    Activities
GROUP BY 
    sell_date
ORDER BY 
    sell_date, 
    product;

8. Execution Flow Walkthrough

Let's trace how the database engine evaluates our query step-by-step:

9. Summary and Key Takeaways

• Aggregation by Date: Use GROUP BY sell_date to aggregate individual transaction rows into daily summaries.
• Handling Duplicates: Combine COUNT(DISTINCT column) and GROUP_CONCAT(DISTINCT column) to safely eliminate duplicate entries on the same day.
• String Concatenation: Leverage MySQL's GROUP_CONCAT() function when you need to flatten multi-row string values into a single cell.
• Lexicographical Ordering: Use ORDER BY inside GROUP_CONCAT() to guarantee alphabetical sorting (A to Z) of aggregated strings.
• Default Separators: While GROUP_CONCAT() uses a comma (,) as its default separator, explicitly writing SEPARATOR ',' improves query readability and maintainability.
• Column Aliasing: Use the AS keyword to rename aggregated expressions (num_sold, products) to match exact interview or problem specifications.
• Order of Execution: Remember that GROUP_CONCAT processes data after grouping has occurred, making it ideal for post-aggregation formatting tasks.