Busiest Day of the Week
Aggregate orders by day of week and return its name, order count, and revenue.
- Aggregation
- Date analysis
- Numeric functions
- Type conversion
- Sorting
Exercise brief
Understand the request
Workforce planning analyst Operations wants order volume and revenue by named weekday.
Operations wants order volume and revenue by named weekday. Aggregate orders by day of week and return its name, order count, and revenue.
Return
- Return day_of_week, order_count, total_revenue in this exact left-to-right order.
Constraints
- Map every supported weekday number to the correct name.
- Keep Sunday and Saturday distinct.
- Order by count descending with weekday number as the tie-breaker.
Data you will use
Review the relevant tables before deciding how to join, filter, or aggregate them.
orders
order_idINTEGERorder_dateDATEorder_totalDECIMAL
Hints, when you need them
Open one clue at a time so you still do the reasoning.
Hint 1
Extract the weekday number: SQLite strftime('%w', d) → 0 (Sun) … 6 (Sat).
Hint 2
Map the number to a name with a CASE, then GROUP BY the weekday.
Hint 3
Tie-break equal counts by the weekday number for deterministic output.
Verified SQL answer
Attempt the problem first, then compare structure and reasoning—not just syntax.
Reveal solution and explanation
SELECT CASE CAST(strftime('%w', order_date) AS INTEGER) WHEN 0 THEN 'Sunday' WHEN 1 THEN 'Monday' WHEN 2 THEN 'Tuesday' WHEN 3 THEN 'Wednesday' WHEN 4 THEN 'Thursday' WHEN 5 THEN 'Friday' WHEN 6 THEN 'Saturday' END AS day_of_week, COUNT(*) AS order_count, ROUND(SUM(order_total), 2) AS total_revenue FROM orders GROUP BY strftime('%w', order_date) ORDER BY order_count DESC, CAST(strftime('%w', order_date) AS INTEGER);Why this works
Day-of-week analysis exposes weekly demand cycles. Beware: the integer each engine assigns to a weekday differs! SQLite strftime('%w') and Postgres EXTRACT(DOW) use 0=Sunday; MySQL DAYOFWEEK() uses 1=Sunday; SQL Server DATEPART(WEEKDAY) depends on DATEFIRST. Always normalize before comparing across engines.
Success check
All seven weekday groups appear once with correct totals and stable ordering.
Expected result
Use this output to verify values, aliases, ordering, and row count.
| day_of_week | order_count | total_revenue |
|---|---|---|
| Saturday | 5 | 1510 |
| Monday | 4 | 1090 |
| Wednesday | 4 | 1410 |
| Sunday | 3 | 820 |
| Tuesday | 2 | 620 |
| Thursday | 1 | 260 |
| Friday | 1 | 280 |
Learn the concepts behind this answer
Strengthen your understanding with these targeted learning topics:
Continue practicing
Build the next SQL skill
WHERE Clause & Filtering
Practice SQL WHERE clauses with realistic boundary, NULL, text, date, exclusion, and production-filtering problems.
SQL Aggregations
Build reliable SQL metrics from aggregate functions through grain, fan-out, weighted ratios, rollups, percentiles, and approximate counts.
CTEs & Window Functions
Practice modular CTE pipelines, deterministic window analytics, period comparisons, deduplication, frames, and gaps-and-islands.
Open the interactive workspace and practice across SQL topics.