Group Sold Products By The Date

Group Sold Products By The Date

What is this problem about?

The Group Sold Products By The Date interview question is a SQL task where you are given an Activities table containing sell_date and product. You need to find for each date the number of distinct products sold and the names of those products. The product names should be sorted lexicographically and combined into a single comma-separated string. The final result should be ordered by sell_date.

Why is this asked in interviews?

Companies like Meta and Amazon ask this Database coding problem to test your ability to perform string aggregation in SQL. While standard aggregation functions like SUM or COUNT are basic, aggregating strings into a single delimited list requires knowledge of specific, often dialect-dependent, SQL functions. It evaluates your practical data reporting skills.

Algorithmic pattern used

This problem relies on the String Aggregation pattern.

1. Group By: Group the records by sell_date.
2. Count Distinct: Use COUNT(DISTINCT product) to get the number of unique products sold that day.
3. String Aggregation: Use the specific string aggregation function for your SQL dialect (e.g., GROUP_CONCAT in MySQL, STRING_AGG in PostgreSQL) to combine the distinct product names. You must also specify an ORDER BY clause within the aggregation function to ensure the list is lexicographically sorted.

Example explanation

Activities:

• 2020-05-30: Headphone
• 2020-05-30: Basketball
• 2020-05-30: T-Shirt
• 2020-05-30: Headphone
• 2020-06-01: Pencil

For 2020-05-30:

1. Distinct products: Basketball, Headphone, T-Shirt. Count = 3.
2. Sorted list: "Basketball,Headphone,T-Shirt". For 2020-06-01:
3. Distinct products: Pencil. Count = 1.
4. Sorted list: "Pencil".

Common mistakes candidates make

• Missing DISTINCT in Aggregation: Using GROUP_CONCAT(product) instead of GROUP_CONCAT(DISTINCT product), which includes duplicate products in the comma-separated list.
• Sorting incorrectly: Forgetting to add the ORDER BY product inside the GROUP_CONCAT function, leading to a random order of products in the string.
• Dialect syntax: Using STRING_AGG in a MySQL environment or vice versa, resulting in a syntax error.

Interview preparation tip

Memorize the syntax for GROUP_CONCAT (MySQL) and STRING_AGG (PostgreSQL/SQL Server). It is the only way to pivot multiple string rows into a single column, which is a very common requirement for generating tags or lists in dashboards.