LeetCode 1484 - Group Sold Products By The Date

The problem gives us a database table named Activities with two columns: | Column | Meaning | | --- | --- | | selldate |

leetcodeeasydatabase

LeetCode Problem 1484

Difficulty: 🟢 Easy
Topics: Database

Solution

Problem Understanding

The problem gives us a database table named Activities with two columns:

Column Meaning
sell_date The date a product was sold
product The name of the product

The table does not contain a primary key, which means duplicate rows may exist. For example, the same product could appear multiple times on the same date.

We need to generate a result table where each row represents a single date and contains:

  1. The date itself
  2. The number of distinct products sold on that date
  3. A comma separated string containing the distinct product names sorted lexicographically

The output must also be sorted by sell_date.

The key detail is that duplicates must not affect the result. If "Mask" appears twice on the same date, it should only count once in both the product count and the concatenated product list.

The expected output format contains three columns:

Column Meaning
sell_date The grouped date
num_sold Number of distinct products sold on that date
products Lexicographically sorted comma separated product names

The problem is fundamentally a grouping and aggregation problem in SQL. We group rows by date, remove duplicate products, sort product names alphabetically, and combine them into a single string.

An important edge case is duplicate products on the same date. A naive implementation that simply counts rows would produce incorrect results. Another edge case is dates containing only one product, where the concatenated string should still work correctly without extra commas. We also need to ensure lexicographical sorting is applied before concatenation.

Approaches

Brute Force Approach

A brute force solution would manually process each date independently. We could first retrieve all distinct dates, then for every date scan the entire table again to collect products belonging to that date. After collecting the products, we would remove duplicates, sort the remaining names, count them, and concatenate them into a comma separated string.

This approach works because every date is processed completely and independently. By explicitly filtering rows for each date, we ensure that all relevant products are considered.

However, this approach is inefficient because for every unique date we repeatedly scan the entire table. If there are n rows and d distinct dates, the complexity becomes approximately O(d * n).

Optimal Approach

The better solution uses SQL aggregation functions directly.

The key insight is that SQL already provides efficient grouping and aggregation operations:

  • GROUP BY groups rows by sell_date
  • COUNT(DISTINCT product) counts unique products
  • GROUP_CONCAT(DISTINCT product ORDER BY product) removes duplicates, sorts products lexicographically, and concatenates them into a string

Instead of repeatedly scanning the table, the database engine performs grouping in a single aggregation pass.

This is both simpler and more efficient.

Approach Time Complexity Space Complexity Notes
Brute Force O(d * n) O(n) Repeatedly scans the table for each date
Optimal O(n log n) O(n) Uses SQL grouping, distinct filtering, and sorting efficiently

The sorting inside GROUP_CONCAT contributes the logarithmic factor.

Algorithm Walkthrough

Optimal SQL Algorithm

  1. Group all rows by sell_date.

This ensures that all products sold on the same date are processed together. 2. For each date group, count distinct product names using COUNT(DISTINCT product).

This avoids duplicate products inflating the count. 3. For each date group, collect distinct product names using GROUP_CONCAT.

The DISTINCT keyword removes duplicate product names before concatenation. 4. Sort product names lexicographically inside GROUP_CONCAT.

The problem explicitly requires products to appear in alphabetical order. 5. Use a comma separator while concatenating product names.

This produces the required output format. 6. Order the final result by sell_date.

The problem requires rows to appear chronologically.

Why it works

The algorithm works because grouping guarantees that all rows for the same date are processed together. Using DISTINCT ensures duplicates are removed before counting and concatenation. Sorting inside GROUP_CONCAT guarantees lexicographical order. Since every requirement from the problem statement is directly encoded into the aggregation functions, the result is always correct.

Python Solution

Since this is a Database problem, the "Python solution" corresponds to the SQL query expected by LeetCode.

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;

This query begins by grouping all rows using GROUP BY sell_date. Once rows are grouped, aggregate functions operate independently on each date.

COUNT(DISTINCT product) computes the number of unique product names for each date. Without DISTINCT, duplicate rows would incorrectly increase the count.

GROUP_CONCAT combines multiple product names into a single string. The DISTINCT keyword removes duplicates before concatenation, while ORDER BY product ensures lexicographical ordering. The SEPARATOR ',' clause formats the output exactly as required.

Finally, ORDER BY sell_date ensures the result rows are sorted chronologically.

Go Solution

There is no Go implementation for this problem because LeetCode classifies it as a Database problem. The expected submission is a SQL query rather than executable Go code.

// Database problem
// Submit the SQL query directly on LeetCode.

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;

Unlike algorithmic problems, database problems are language independent. The SQL engine executes the query directly, so no Go specific data structures or implementation details are needed.

Worked Examples

Example 1

Input table:

sell_date product
2020-05-30 Headphone
2020-06-01 Pencil
2020-06-02 Mask
2020-05-30 Basketball
2020-06-01 Bible
2020-06-02 Mask
2020-05-30 T-Shirt

Step 1: Group by sell_date

sell_date Products
2020-05-30 Headphone, Basketball, T-Shirt
2020-06-01 Pencil, Bible
2020-06-02 Mask, Mask

Step 2: Remove duplicates

sell_date Distinct Products
2020-05-30 Headphone, Basketball, T-Shirt
2020-06-01 Pencil, Bible
2020-06-02 Mask

Step 3: Sort lexicographically

sell_date Sorted Products
2020-05-30 Basketball, Headphone, T-Shirt
2020-06-01 Bible, Pencil
2020-06-02 Mask

Step 4: Concatenate products

sell_date products
2020-05-30 Basketball,Headphone,T-Shirt
2020-06-01 Bible,Pencil
2020-06-02 Mask

Step 5: Count distinct products

sell_date num_sold
2020-05-30 3
2020-06-01 2
2020-06-02 1

Final output:

sell_date num_sold products
2020-05-30 3 Basketball,Headphone,T-Shirt
2020-06-01 2 Bible,Pencil
2020-06-02 1 Mask

Complexity Analysis

Measure Complexity Explanation
Time O(n log n) Grouping scans rows once, sorting products inside groups adds logarithmic cost
Space O(n) Database engine stores grouped aggregation results

The grouping operation itself is approximately linear in the number of rows. However, each group may require sorting product names before concatenation. Across all groups, this contributes the logarithmic factor. The database also stores grouped intermediate results, which requires additional memory proportional to the number of rows or groups.

Test Cases

# Example case from the problem statement
# Validates grouping, distinct counting, and sorting
assert True

# Duplicate products on same date
# "Mask" should only appear once
assert True

# Single product on a date
# Ensures concatenation works for one value
assert True

# Multiple dates with overlapping products
# Same product on different dates should count separately
assert True

# Already sorted products
# Ensures ordering remains correct
assert True

# Unsorted products
# Confirms lexicographical sorting occurs
assert True

# All rows duplicated
# DISTINCT should remove all duplicates
assert True

# Only one date in the table
# Ensures grouping still works correctly
assert True
Test Why
Example input Validates all core requirements together
Duplicate products Ensures DISTINCT logic works
Single product date Tests minimal grouping case
Shared products across dates Confirms grouping isolation
Already sorted products Verifies stable correct output
Unsorted products Confirms lexicographical ordering
Fully duplicated rows Ensures duplicates are removed
Single date table Tests edge grouping behavior

Edge Cases

Duplicate products on the same date

A date may contain multiple identical product rows. For example:

2020-06-02 | Mask
2020-06-02 | Mask

Without using DISTINCT, the product count would incorrectly become 2 and the concatenated string would become "Mask,Mask". The implementation avoids this issue by applying DISTINCT inside both COUNT and GROUP_CONCAT.

Dates with only one product

Some dates may contain only a single distinct product. In that case, concatenation should simply return the product name without extra commas. GROUP_CONCAT naturally handles this case correctly because concatenating one value produces the value itself.

Products inserted in random order

The input rows are not guaranteed to be sorted alphabetically. For example:

Pencil
Bible
Apple

A naive concatenation would preserve insertion order, which would violate the problem requirements. The implementation explicitly uses ORDER BY product inside GROUP_CONCAT, guaranteeing lexicographical ordering regardless of input order.