Daily Produce at the Co-op Stall
A farm co-op logs every sale at its market stall. For the daily report, produce one row per date on which at least one sale happened, with these columns:
sale_dateproduct_count: how many different products were sold that dayproducts: those distinct product names in alphabetical order, joined by a comma with no spaces (for exampleapples,honey,kale)
A product sold several times on the same day appears only once. Order the rows by sale_date, earliest first. If there are no sales, return no rows.
Tables
stall_sales — One row per sale at the stall.
| Column | Type | Description |
|---|---|---|
sale_id (key) | INTEGER | Unique sale id (in the order the sales were entered) |
sale_date | TEXT | Date of the sale, ISO text such as '2026-09-05' |
product | TEXT | Product name, always lower case |
kilos | REAL | Weight sold in kilograms |
Example
stall_sales
| sale_id | sale_date | product | kilos |
|---|---|---|---|
| 1 | 2026-09-02 | kale | 1.5 |
| 2 | 2026-09-01 | plums | 2 |
| 3 | 2026-09-02 | apples | 3.2 |
| 4 | 2026-09-01 | honey | 0.5 |
| 5 | 2026-09-02 | kale | 0.8 |
| 6 | 2026-09-03 | eggs | 1.2 |
| 7 | 2026-09-01 | apples | 4 |
| 8 | 2026-09-02 | eggs | 0.6 |
Expected output
| sale_date | product_count | products |
|---|---|---|
| 2026-09-01 | 3 | apples,honey,plums |
| 2026-09-02 | 3 | apples,eggs,kale |
| 2026-09-03 | 1 | eggs |
Return the rows in exactly the order described. Your query also runs against hidden datasets with tricky edge cases.
SQL Path · Strings & CASE · problem 52 of 70 · SQLite
The idea
Group the sales by sale_date; each group then holds every sale of that day. Two aggregates summarise the group: COUNT(DISTINCT product) counts different products, and group_concat(DISTINCT product ORDER BY product) builds the list.
The ORDER BY inside group_concat matters: without it, SQLite concatenates in whatever order it happens to visit the rows (usually insertion order), so the output could change from run to run or between databases. With DISTINCT, group_concat takes only one argument, and its default separator is a comma with no space, exactly what is asked.
The alternative removes duplicates first with SELECT DISTINCT sale_date, product. After that every row is a different product, so a plain COUNT(*) and a non-distinct group_concat with an explicit separator give the same result.
Solution
SELECT sale_date,
COUNT(DISTINCT product) AS product_count,
group_concat(DISTINCT product ORDER BY product) AS products
FROM stall_sales
GROUP BY sale_date
ORDER BY sale_date;
Another way
SELECT sale_date,
COUNT(*) AS product_count,
group_concat(product, ',' ORDER BY product) AS products
FROM (
SELECT DISTINCT sale_date, product
FROM stall_sales
)
GROUP BY sale_date
ORDER BY sale_date;
Why common answers fail
COUNT(product) counts sales, not different products, so repeat sales inflate the count.
SELECT sale_date, COUNT(product) AS product_count, group_concat(DISTINCT product ORDER BY product) AS products FROM stall_sales GROUP BY sale_date ORDER BY sale_date;
Without ORDER BY inside group_concat the names come out in whatever order rows are read, not alphabetically.
SELECT sale_date, COUNT(DISTINCT product) AS product_count, group_concat(DISTINCT product) AS products FROM stall_sales GROUP BY sale_date ORDER BY sale_date;
Forgets DISTINCT in the list, so a product sold twice is listed twice.
SELECT sale_date, COUNT(DISTINCT product) AS product_count, group_concat(product, ',' ORDER BY product) AS products FROM stall_sales GROUP BY sale_date ORDER BY sale_date;
What the interviewer is checking
Checks that you know string aggregation, that you apply DISTINCT consistently, and that you never rely on an unspecified order.
Mistakes to avoid
- Counting sales instead of distinct products.
- Relying on the implicit order of group_concat.
- Passing a custom separator together with DISTINCT: SQLite only allows one argument with DISTINCT.
Follow-ups to expect
- Use ', ' as the separator while keeping distinct names (de-duplicate in a subquery first).
- Also return the total kilos per day (add SUM(kilos)).
- List products by descending kilos sold instead of alphabetically (aggregate per product first, then group_concat ORDER BY total DESC).
In real work
Daily digests, order summaries and audit logs often flatten a set of items into one readable string per day or per customer.