Calculate Orders Within Each Interval - Problem
You are given a table Orders that contains order counts for specific minutes.
Each row contains a minute and the number of orders received during that minute. The total number of rows will always be a multiple of 6.
Write a query to calculate total orders within each interval. Each interval is defined as a combination of 6 consecutive minutes:
- Minutes 1-6 fall within interval 1
- Minutes 7-12 fall within interval 2
- And so forth...
Return the result table ordered by interval_no in ascending order.
Table Schema
Orders
| Column Name | Type | Description |
|---|---|---|
minute
PK
|
int | The minute number (primary key) |
order_count
|
int | Number of orders received during that minute |
Primary Key: minute
Note: Each row represents order count for a specific minute. Total rows is always a multiple of 6.
Input & Output
Example 1 — Basic Interval Calculation
Input Table:
| minute | order_count |
|---|---|
| 1 | 2 |
| 2 | 11 |
| 3 | 15 |
| 4 | 0 |
| 5 | 0 |
| 6 | 0 |
| 7 | 1 |
| 8 | 0 |
| 9 | 0 |
| 10 | 0 |
| 11 | 0 |
| 12 | 0 |
Output:
| interval_no | total_orders |
|---|---|
| 1 | 28 |
| 2 | 1 |
💡 Note:
Minutes 1-6 form interval 1 with total orders: 2+11+15+0+0+0 = 28. Minutes 7-12 form interval 2 with total orders: 1+0+0+0+0+0 = 1.
Example 2 — Multiple Intervals
Input Table:
| minute | order_count |
|---|---|
| 1 | 5 |
| 2 | 3 |
| 3 | 2 |
| 4 | 1 |
| 5 | 4 |
| 6 | 0 |
| 7 | 10 |
| 8 | 8 |
| 9 | 6 |
| 10 | 4 |
| 11 | 2 |
| 12 | 0 |
Output:
| interval_no | total_orders |
|---|---|
| 1 | 15 |
| 2 | 30 |
💡 Note:
Interval 1 (minutes 1-6): 5+3+2+1+4+0 = 15 orders. Interval 2 (minutes 7-12): 10+8+6+4+2+0 = 30 orders.
Constraints
-
1 ≤ minute ≤ 1000 -
0 ≤ order_count ≤ 1000 - The total number of rows is always a multiple of 6
Visualization
Tap to expand
Understanding the Visualization
1
Input
Orders table with minute and order_count
2
Group
Calculate intervals using CEIL(minute/6.0)
3
Sum
Sum orders within each interval
Key Takeaway
🎯 Key Insight: Use mathematical grouping with CEIL function to efficiently partition sequential data into fixed-size intervals
💡
Explanation
AI Ready
💡 Suggestion
Tab
to accept
Esc
to dismiss
// Output will appear here after running code