Level 7 · Which weekday sells most?

Dates hide many useful parts: the year, the month, the day of the week, the hour. Pulling one out and grouping by it is a fast way to find patterns.

Recap: date parts in DuckDB

function	returns	example for Saturday 2025-11-29 20:15
ISODOW(ts)	1 = Monday … 7 = Sunday	6
DAYOFWEEK(ts)	0 = Sunday … 6 = Saturday	6
DAYNAME(ts)	the name	Saturday
HOUR(ts)	0 to 23	20

Names sort alphabetically, so keep a number next to them to sort by. ISO numbering (Monday first) is what most business calendars use.

SELECT HOUR(order_date) AS hour, COUNT(*) AS orders
FROM orders
WHERE status = 'completed'
  AND order_date >= '2025-01-01'
  AND order_date < '2026-01-01'
GROUP BY HOUR(order_date)
ORDER BY orders DESC
LIMIT 3;

Grouping by hour like this shows the evening peak the ticket asks about: the three busiest hours of 2025 are 20:00 (339 completed orders), 19:00 (328) and 21:00 (292). An hour value of 20 covers 20:00:00 up to 20:59:59, so HOUR(order_date) >= 18 means “from 6 PM until midnight”. If the ticket had defined the evening as 6 PM to 10 PM, the condition would need an upper bound too: HOUR(order_date) BETWEEN 18 AND 21.

In the full answer, Saturday (704) and Sunday (682) bring the most completed orders in 2025, and on every weekday roughly a third or more of orders arrive at 18:00 or later.

When you group by an expression, repeat the expression in GROUP BY (or use the column position). Both ISODOW and DAYNAME go into the grouping; they always agree, so the row count stays seven.

Common mistakes

• Alphabetical days. ORDER BY day_name gives Friday, Monday, Saturday, Sunday …
• Mixing numbering systems. DAYOFWEEK starts at Sunday = 0, ISODOW at Monday = 1.
• Forgetting the year filter. Weekday patterns change as the business grows; compare like with like.

In SQL Server

Use DATENAME(weekday, order_date) for the name and DATEPART(hour, order_date) for the hour. DATEPART(weekday, ...) depends on the DATEFIRST setting (Sunday = 1 by default in US English), so (DATEPART(weekday, order_date) + @@DATEFIRST - 2) % 7 + 1 is the usual way to get Monday = 1.