Sales by Day of Week
ASKED IN INTERVIEWAmazon
Problem Statement
Find the total revenue generated for each day of the week for orders placed in 2025.
Rules:
- Output columns: day_of_week, total_revenue.
- Sort: Monday to Sunday.
- Use full day names.
Tests your understanding of
Date Extraction, Aggregation, Sorting and TO_CHAR
Input Tables
orders
| order_id(INTEGER) | order_date(TIMESTAMP) | revenue(DECIMAL) |
|---|---|---|
| 1 | 2025-01-06 10:00:00 | 150 |
| 2 | 2025-01-07 14:00:00 | 200 |
| 3 | 2025-01-13 09:00:00 | 50 |
| 4 | 2025-01-08 11:00:00 | 300 |
products
| product_id(INTEGER) | price(DECIMAL) |
|---|---|
| 101 | 10 |
warehouse_logs
| log_id(INTEGER) | order_id(INTEGER) |
|---|---|
| 1 | 1 |
Expected Output
| day_of_week(VARCHAR) | total_revenue(DECIMAL) |
|---|---|
| Monday | 200 |
| Tuesday | 200 |
| Wednesday | 300 |
Tags
HardASKED IN INTERVIEWDate ExtractionAggregationSortingTO_CHAR
20-30 min
41%