Level 2 · A daily chart stuck at one order

The starter returns 244 rows for a seven-day window, and nearly every orders value is 1. The query is not wrong about the data. It is answering a different question: how many orders were placed at each exact second.

Grain is set by GROUP BY, not by SELECT

order_date is a TIMESTAMP. Two orders almost never share the same second (12,000 orders have 11,996 distinct timestamps), so GROUP BY order_date makes one group per order. The SELECT then casts each group’s timestamp to a date for display, which is why the output looks daily: 2025-11-28 appears 56 times, each with orders = 1.

DuckDB allows this because CAST(order_date AS DATE) is a function of a grouped column. Postgres and SQL Server accept it too. Nothing warns you.

How to spot it

• Count the rows you expect. A week should give 7 rows. If you get 244, the grain is finer than you think.
• Look for repeated labels. The same order_day on many rows means the real grouping key is something you are not showing.
• Sum it back. SUM(orders) over the starter’s output is still 246, the right total. The total being right while the shape is wrong points at grouping, not filtering.

The fix

Group by the same expression you display:

GROUP BY CAST(order_date AS DATE)

DuckDB also accepts GROUP BY order_day (the alias) or GROUP BY 1. The result has 7 rows, peaking on Sunday 2025-11-30 with 58 orders, right after 56 on Black Friday and 51 on Saturday.

Common mistakes

• DATE_TRUNC('day', order_date) in the SELECT but the raw column in GROUP BY: same bug.
• Adding DISTINCT to hide the repeats: you get 7 rows, each still saying 1.
• Grouping by an extra column “just in case” (channel, status): the rows split again, at a grain nobody asked for.

In SQL Server

T-SQL does not accept an alias or a position in GROUP BY, so repeat the expression. CAST(x AS date) works the same way:

SELECT CAST(order_date AS date) AS order_day, COUNT(*) AS orders
FROM orders
GROUP BY CAST(order_date AS date);

On the job

“Flat line at 1” and “the same label many times” are the two shapes of a grain bug. Interviewers like asking what one row of a result represents; answer it before you answer anything else.