Problem

Table: Orders

1
2
3
4
5
6
7

+-------------+------+ 
| Column Name | Type | 
+-------------+------+ 
| minute      | int  | 
| order_count | int  |
+-------------+------+ minute is the primary key for this table.
Each row of this table contains the minute and number of orders received during that specific minute. The total number of rows will be a multiple of 6.

Write a query to calculate total orders**** within each interval. Each interval is defined as a combination of 6 minutes.

  • Minutes 1 to 6 fall within interval 1, while minutes 7 to 12 belong to interval 2, and so forth.

Return the result table ordered byinterval_no in ascending order.

The result format is in the following example.

Examples

Example 1:

 1
 2
 3
 4
 5
 6
 7
 8
 9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
28
29

    Input: 
    Orders table:
    +--------+-------------+
    | minute | order_count | 
    +--------+-------------+
    | 1      | 0           |
    | 2      | 2           | 
    | 3      | 4           | 
    | 4      | 6           | 
    | 5      | 1           | 
    | 6      | 4           | 
    | 7      | 1           | 
    | 8      | 2           | 
    | 9      | 4           | 
    | 10     | 1           | 
    | 11     | 4           | 
    | 12     | 6           | 
    +--------+-------------+
    Output: 
    +-------------+--------------+
    | interval_no | total_orders | 
    +-------------+--------------+
    | 1           | 17           | 
    | 2           | 18           |    
    +-------------+--------------+
    Explanation: 
    - Interval number 1 comprises minutes from 1 to 6. The total orders in these six minutes are (0 + 2 + 4 + 6 + 1 + 4) = 17.
    - Interval number 2 comprises minutes from 7 to 12. The total orders in these six minutes are (1 + 2 + 4 + 1 + 4 + 6) = 18.
    Returning table orderd by interval_no in ascending order.

Solution

Method 1 – Group By Interval Using Integer Division

Intuition

Each interval consists of 6 consecutive minutes. We can determine the interval number for each row by dividing the minute by 6 and rounding up (using integer division). Grouping by this interval number and summing the order counts gives the total orders per interval.

Approach

  1. For each row, compute the interval number as (minute - 1) DIV 6 + 1.
  2. Group by this interval number.
  3. Sum the order_count for each group.
  4. Return the interval number and total orders, ordered by interval number ascending.

Code

1
2
3
4

SELECT (minute - 1) DIV 6 + 1 AS interval_no, SUM(order_count) AS total_orders
FROM Orders
GROUP BY interval_no
ORDER BY interval_no ASC;

Complexity

  • ⏰ Time complexity: O(n) — n is the number of rows in Orders (single scan and group by).
  • 🧺 Space complexity: O(1) — Only accumulators are used.