Marketing wants to know which days of the week drive the most revenue. Using the orders table, return day_of_week (0 = Sunday … 6 = Saturday), day_name, and total_sales, ordered by day_of_week.
orders
| column | type |
|---|---|
| id | INTEGER |
| customer_id | INTEGER |
| amount | NUMERIC |
| created_at | DATE |
| id | customer_id | amount | created_at |
|---|---|---|---|
| 1 | 1 | 100.00 | 2024-01-01 |
| 2 | 2 | 200.00 | 2024-01-01 |
| 3 | 1 | 150.00 | 2024-01-03 |
| 4 | 3 | 50.00 | 2024-01-05 |
| 5 | 2 | 300.00 | 2024-01-05 |
| 6 | 1 | 100.00 | 2024-01-08 |
2024-01-01 is a Monday (DOW 1), 2024-01-03 is a Wednesday (DOW 3), 2024-01-05 is a Friday (DOW 5), 2024-01-08 is the next Monday (DOW 1).
| day_of_week | day_name | total_sales |
|---|---|---|
| 1 | Monday | 400.00 |
| 3 | Wednesday | 150.00 |
| 5 | Friday | 350.00 |
Monday (DOW 1) has orders from 2024-01-01 (100+200=300) and 2024-01-08 (100), totalling 400. Wednesday has 150. Friday has 50+300=350.