Skip to content

2893. Calculate Orders Within Each Interval πŸ”’

DifficultyMedium

Description

Table: Orders

+-------------+------+ 
| 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 by interval_no in ascending order.

The result format is in the following example.

 

Example 1:

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.

Solutions

Solution 1

Thinking

Minutes \(1\) through \(6\) are interval \(1\) and minutes \(7\) through \(12\) are interval \(2\), so the interval number comes from the minute value itself. A six-row window assumes every minute from \(1\) onward is present and that each block ends on a multiple of \(6\); a gap, or a block that does not, drops an interval or mixes two of them.

\(\lfloor (minute+5)/6 \rfloor\) sends each minute into its interval. Group on that value, sum order_count, and sort by the interval number.

1
2
3
4
5
6
7
# Write your MySQL query statement below
SELECT
    (minute + 5) DIV 6 AS interval_no,
    SUM(order_count) AS total_orders
FROM Orders
GROUP BY 1
ORDER BY 1;

Solution 2

Thinking

The window still walks a prefix. When minutes are consecutive, \(\lfloor(minute+5)/6\rfloor\) groups rows directly and SUM inside each group is the interval total.

1
2
3
4
5
6
SELECT
    FLOOR((minute + 5) / 6) AS interval_no,
    SUM(order_count) AS total_orders
FROM Orders
GROUP BY 1
ORDER BY 1;

Comments