Group Sold Products By The Date

Problem

Table Activities has two columns: sell_date (a date) and product (a varchar). There is no primary key, so the table may contain duplicate rows — the same product can appear more than once on the same date.

+-------------+---------+
| Column Name | Type    |
+-------------+---------+
| sell_date   | date    |
| product     | varchar |
+-------------+---------+

For each date, find how many different products were sold and list their names. The product names for each date must be sorted lexicographically and returned as a single comma-separated string. Return the result ordered by sell_date.

The shape of the answer

Two things have to happen per date, and both lean on the same idea — collapsing duplicates:

• num_sold — the count of distinct products, so a product sold twice on one day still counts once.
• products — the distinct product names, sorted A→Z, glued together with commas.

Both are aggregates computed inside a GROUP BY sell_date. The only genuinely new tool here is the string-aggregation function that turns a column of values into one delimited string.

Solution (MySQL)

MySQL provides GROUP_CONCAT, which accepts DISTINCT, an internal ORDER BY, and a SEPARATOR — everything the problem asks for in one call:

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

Reading it piece by piece:

• COUNT(DISTINCT product) — number of different products that day.
• GROUP_CONCAT(DISTINCT product ORDER BY product ASC SEPARATOR ',') — the de-duplicated names, sorted, joined by commas. The ORDER BY inside GROUP_CONCAT orders the values within each group; it is independent of the outer ORDER BY sell_date.
• GROUP BY sell_date defines the groups; the final ORDER BY sell_date sorts the output rows.

The same answer on other engines

String aggregation is one of the least standardized corners of SQL — the function name and the syntax for ordering and de-duplicating differ by engine. The logic is identical; only the spelling changes.

PostgreSQL

Postgres calls it STRING_AGG(expression, delimiter). It accepts an aggregate ORDER BY and the DISTINCT keyword, so the direct one-line translation is valid:

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

This works because the ORDER BY expression (product) is the same as the DISTINCT-ed argument (product). Postgres only rejects the combination when they differ — e.g. STRING_AGG(DISTINCT product, ',' ORDER BY sell_date) raises “in an aggregate with DISTINCT, ORDER BY expressions must appear in argument list.” Since we sort by the very column we are de-duplicating, there is no conflict and no subquery is needed.

SQLite

SQLite calls it group_concat(expression, delimiter) and accepts DISTINCT, but it has no aggregate ORDER BY syntax at all — you cannot write an ORDER BY inside the function regardless of whether DISTINCT is present. To get sorted output, order the rows in a subquery first, then aggregate:

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

The inner SELECT DISTINCT … ORDER BY both de-duplicates and sorts; the outer group_concat then concatenates in that order. (Note: SQLite’s ordering-via-subquery relies on the optimizer preserving row order into the aggregate, which holds in practice but is not formally guaranteed by the SQL standard.)

Common pitfalls

• Forgetting DISTINCT. Because the table allows duplicate rows, a missing DISTINCT over-counts num_sold and repeats names in the products string.
• Confusing the two ORDER BY clauses. The one inside the aggregate sorts names within a date; the outer one sorts the output rows by date. You need both.
• Assuming string-aggregation syntax is portable. It is the function whose spelling varies most across engines — confirm the exact name (GROUP_CONCAT vs STRING_AGG vs group_concat) and how each handles ordering before porting a query.
• Over-engineering Postgres. You do not need a subquery to de-duplicate in Postgres here; STRING_AGG(DISTINCT product, ',' ORDER BY product) is valid because the sort key matches the distinct argument.

Source & attribution

Problem adapted from LeetCode 1484, “Group Sold Products By The Date.” The MySQL reference solution follows the standard GROUP_CONCAT approach; the PostgreSQL and SQLite equivalents and the engine-portability notes were verified against the official documentation for each engine (MySQL GROUP_CONCAT, PostgreSQL string_agg aggregate-expression rules, and SQLite group_concat).