The finance team wants to compare revenue earned on weekdays and weekends.
The orders table stores order amounts in amount_usd and their timestamps in order_ts. For this report, only orders with status = 'paid' generate revenue. Use the UTC calendar day of each order to classify it as either:
01weekday: Monday through Friday.
02weekend: Saturday or Sunday.
Prepare a summary showing total paid revenue for each category.
Ordering: Sort by bucket alphabetically (weekday before weekend).
Returnbucketrevenue
About this problem
You practise extracting part of a date and using it to bucket rows before aggregating. Each paid order in orders gets classified by the UTC calendar day of order_ts as weekday or weekend, and you return total amount_usd per bucket. That combines a date function, a CASE expression and a GROUP BY on the derived label. Retail and finance teams ask this to see whether weekends carry their share of revenue, so a single misclassified day can tilt the comparison.
The trap is the numbering of days. Different functions and engines number the week differently: dayofweek and EXTRACT(DOW …) usually give Sunday as 0 and Saturday as 6, while ISO numbering (isodow) runs Monday 1 to Sunday 7. If you write BETWEEN 1 AND 5 with the wrong scheme, Sunday or Saturday lands in the weekday bucket. The sample has paid orders on both a Sunday and a Saturday, so a numbering slip moves real revenue between buckets while the grand total stays the same, which makes it hard to spot. The second trap is the status filter: refunded and delivered orders in the sample have amounts too, and only paid orders count as revenue here. Finally, relying on grouping order to put weekday before weekend is a gamble, because GROUP BY doesn’t promise any output order.
Read the lesson: Dates & Timestamps (P11) →