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_date
  • product_count: how many different products were sold that day
  • products: those distinct product names in alphabetical order, joined by a comma with no spaces (for example apples,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.

ColumnTypeDescription
sale_id (key)INTEGERUnique sale id (in the order the sales were entered)
sale_dateTEXTDate of the sale, ISO text such as '2026-09-05'
productTEXTProduct name, always lower case
kilosREALWeight sold in kilograms

Example

stall_sales

sale_idsale_dateproductkilos
12026-09-02kale1.5
22026-09-01plums2
32026-09-02apples3.2
42026-09-01honey0.5
52026-09-02kale0.8
62026-09-03eggs1.2
72026-09-01apples4
82026-09-02eggs0.6

Expected output

sale_dateproduct_countproducts
2026-09-013apples,honey,plums
2026-09-023apples,eggs,kale
2026-09-031eggs

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

One output row per date means GROUP BY sale_date.
Both the count and the list need DISTINCT, because a product can sell many times a day.
SQLite lets you write ORDER BY inside an aggregate: group_concat(x ORDER BY x).

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.

Practice next

Next Post Previous Post
No Comment
Add Comment
comment url