Calculate Orders Within Each Interval - Practice Coding Problems

Calculate Orders Within Each Interval - Problem

Database Medium

You're analyzing order data for a busy restaurant that tracks the number of orders received every minute throughout the day. Your task is to group these orders into 6-minute intervals and calculate the total orders for each interval.

Database Schema:

Table: Orders
+-------------+------+
| Column Name | Type |
+-------------+------+
| minute      | int  |
| order_count | int  |
+-------------+------+

The minute column is the primary key, representing the specific minute when orders were received. The order_count shows how many orders came in during that minute.

Interval Rules:

Minutes 1-6 belong to Interval 1
Minutes 7-12 belong to Interval 2
Minutes 13-18 belong to Interval 3
And so on...

Goal: Write a SQL query that calculates the total orders within each 6-minute interval and returns the results ordered by interval number ascending.

Input & Output

Basic Interval Grouping

$ Input: Orders table: | minute | order_count | |--------|-------------| | 1 | 5 | | 2 | 3 | | 6 | 2 | | 7 | 10 | | 8 | 2 | | 12 | 4 |

› Output: | interval_no | total_orders | |-------------|---------------| | 1 | 10 | | 2 | 16 |

💡 Note: Minutes 1, 2, and 6 fall in interval 1 (1-6): 5+3+2=10 orders. Minutes 7, 8, and 12 fall in interval 2 (7-12): 10+2+4=16 orders.

Multiple Intervals

$ Input: Orders table: | minute | order_count | |--------|-------------| | 1 | 2 | | 7 | 4 | | 13 | 6 | | 19 | 8 | | 25 | 10 |

› Output: | interval_no | total_orders | |-------------|---------------| | 1 | 2 | | 2 | 4 | | 3 | 6 | | 4 | 8 | | 5 | 10 |

💡 Note: Each minute represents the first minute of its respective 6-minute interval: 1→interval 1, 7→interval 2, 13→interval 3, 19→interval 4, 25→interval 5.

Edge Case - Last Minute of Intervals

$ Input: Orders table: | minute | order_count | |--------|-------------| | 6 | 15 | | 12 | 20 | | 18 | 25 | | 24 | 30 |

› Output: | interval_no | total_orders | |-------------|---------------| | 1 | 15 | | 2 | 20 | | 3 | 25 | | 4 | 30 |

💡 Note: Minutes 6, 12, 18, 24 are the last minutes of intervals 1, 2, 3, 4 respectively. The formula (minute + 5) / 6 correctly assigns: 6→1, 12→2, 18→3, 24→4.

Visualization

Tap to expand

Understanding the Visualization

Collect Orders

Restaurant receives orders every minute with varying quantities

Apply Formula

Use (minute + 5) / 6 to determine which 6-minute interval each order belongs to

Group & Sum

Combine all orders within the same interval to get totals

Analyze Patterns

Review interval totals to identify peak periods and optimize operations

Key Takeaway

🎯 Key Insight: The formula (minute + 5) / 6 elegantly calculates interval numbers using ceiling division, making the solution scalable and efficient for any number of intervals.

Time & Space Complexity

Time Complexity

⏱️

O(n log k)

Single pass through n records with O(log k) grouping operations where k is number of intervals

⚡ Linearithmic

Space Complexity

O(k)

Storage for k interval groups in the result set

✓ Linear Space

Constraints

1 ≤ minute ≤ 10⁴
1 ≤ order_count ≤ 10³
The total number of rows is a multiple of 6
minute is the primary key (no duplicate minutes)

Asked in

a Amazon 45 G Google 38 f Meta 32 ⊞ Microsoft 28

The optimal solution uses a mathematical formula to calculate interval numbers efficiently. The key insight is that minute N belongs to interval CEILING(N/6), which can be computed as (minute + 5) / 6 using integer division. This approach scales perfectly and requires only a single pass through the data with O(n log k) time complexity.

Common Approaches

Approach	Time	Space	Notes
✓ Multiple CASE Statements	O(n)	O(k)	Use nested CASE statements to manually assign each minute to its interval
Mathematical Formula (Optimal)	O(n log k)	O(k)	Use ceiling division formula to calculate intervals efficiently

Multiple CASE Statements — Algorithm Steps

Create CASE statements for each possible interval
Manually specify minute ranges for each interval
Group by the calculated interval and sum order counts
Order results by interval number

Visualization

Tap to expand

Step-by-Step Walkthrough

Check Range 1-6

If minute is between 1-6, assign to interval 1

Check Range 7-12

If minute is between 7-12, assign to interval 2

Continue Pattern

Keep checking predefined ranges manually

Group and Sum

Group by intervals and sum the order counts

Code -

solution.c — C

// Brute force approach with manual interval assignment
#include <stdio.h>
#include <stdlib.h>

typedef struct {
    int interval;
    int total;
} IntervalResult;

int compare(const void *a, const void *b) {
    IntervalResult *ia = (IntervalResult *)a;
    IntervalResult *ib = (IntervalResult *)b;
    return ia->interval - ib->interval;
}

int calculateOrdersIntervals(int orders[][2], int orderCount, IntervalResult *result) {
    int intervalTotals[1000] = {0}; // Assuming max 1000 intervals
    int maxInterval = 0;
    
    for (int i = 0; i < orderCount; i++) {
        int minute = orders[i][0];
        int count = orders[i][1];
        int intervalNo;
        
        // Manual interval assignment
        if (minute >= 1 && minute <= 6) {
            intervalNo = 1;
        } else if (minute >= 7 && minute <= 12) {
            intervalNo = 2;
        } else if (minute >= 13 && minute <= 18) {
            intervalNo = 3;
        } else if (minute >= 19 && minute <= 24) {
            intervalNo = 4;
        } else {
            intervalNo = (minute + 5) / 6;
        }
        
        intervalTotals[intervalNo] += count;
        if (intervalNo > maxInterval) maxInterval = intervalNo;
    }
    
    int resultCount = 0;
    for (int i = 1; i <= maxInterval; i++) {
        if (intervalTotals[i] > 0) {
            result[resultCount].interval = i;
            result[resultCount].total = intervalTotals[i];
            resultCount++;
        }
    }
    
    qsort(result, resultCount, sizeof(IntervalResult), compare);
    return resultCount;
}

Time & Space Complexity

Time Complexity

⏱️

O(n)

Single pass through all records, but with complex CASE evaluation

✓ Linear Growth

Space Complexity

O(k)

Where k is the number of intervals for grouping

✓ Linear Space

Constraints

1 ≤ minute ≤ 10⁴
1 ≤ order_count ≤ 10³
The total number of rows is a multiple of 6
minute is the primary key (no duplicate minutes)

34.2K Views

Medium Frequency

~15 min Avg. Time

890 Likes

Ln 1, Col 1

Smart Actions

💡 Explanation

AI Ready

💡 Suggestion Tab to accept Esc to dismiss

// Output will appear here after running code

Code Editor Closed

Click the red button to reopen

Input & Output

Visualization

Time & Space Complexity

Related Problems

Constraints

Common Approaches

Multiple CASE Statements — Algorithm Steps

Visualization

Code -

Time & Space Complexity

Constraints

Select Compiler