1484. Group Sold Products By The Date

SQL 50 · Advanced String Functions, Regex, Clause (day 7). Decode it with the hints, solve it on LeetCode, then compare with the reference solution in MySQLPostgreSQL.

EasySolve it on LeetCode ↗

Your work

Status Notes Your solution (MySQLPostgreSQL)

Hints

Open one at a time. Try to finish with as few as you can.

1. What does one output row represent?

One sell_date.

2. Approach

GROUP BY sell_date, COUNT(DISTINCT product), GROUP_CONCAT(DISTINCT product ORDER BY product SEPARATOR ',').GROUP BY sell_date, COUNT(DISTINCT product), STRING_AGG(DISTINCT product, ',' ORDER BY product).

3. The trap

DISTINCT inside both COUNT and GROUP_CONCAT.DISTINCT inside both COUNT and STRING_AGG.

Reference solution

Tested on MySQL 8.0 and PostgreSQL 16. Switch dialect at the top of the page.

Show the MySQLPostgreSQL solution

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;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;

Patterns used

MySQL vs PostgreSQL