Amazon: EC2 Billable Seconds With the One-Minute Minimum
A cloud billing engineer is checking per-second compute charges.
- Difficulty: Easy
- Topics: Dates, Aggregation, Conditional Logic
- Dialect: MySQL
- Problem: #580
Problem statement
A cloud billing engineer is checking per-second compute charges. Each run of an instance is billed by the second, but every run is billed for at least 60 seconds — a run of 25 seconds is billed as 60.
For every instance with at least one finished run, return instance_id and billable_seconds, the sum over its finished runs of the run's length in seconds, raised to 60 where shorter. Runs still going (stopped_at is NULL) are not billed yet. Return the rows in any order.
A practice scenario set at Amazon: the tables and data are invented, and this is not a question from, or endorsed by, Amazon.
Tables
Table: InstanceRun
| Column | Type |
|---|---|
| run_id | int |
| instance_id | varchar |
| started_at | datetime |
| stopped_at | datetime |
Primary key: run_id.
One row per start-to-stop run of an instance; stopped_at is NULL while it is still running.
Examples
Example 1
InstanceRun
| run_id | instance_id | started_at | stopped_at |
|---|---|---|---|
| 1 | i-0a17 | 2025-09-01 10:00:00 | 2025-09-01 10:00:25 |
| 2 | i-0a17 | 2025-09-01 11:00:00 | 2025-09-01 11:30:00 |
| 3 | i-0b42 | 2025-09-01 09:00:00 | 2025-09-01 09:01:00 |
| 4 | i-0b42 | 2025-09-01 12:00:00 | NULL |
| 5 | i-0c99 | 2025-09-01 13:00:00 | NULL |
| 6 | i-0d05 | 2025-09-01 08:00:00 | 2025-09-01 08:00:59 |
Output
| instance_id | billable_seconds |
|---|---|
| i-0a17 | 1860 |
| i-0b42 | 60 |
| i-0d05 | 60 |
How to solve Amazon: EC2 Billable Seconds With the One-Minute Minimum
The minimum applies per run, not per instance, so it has to be applied before summing: GREATEST(60, TIMESTAMPDIFF(SECOND, started_at, stopped_at)) is a run's billed seconds, and SUM of that per instance_id is the instance's total. Applying the minimum to the instance total instead would undercharge an instance with many tiny runs.
Runs still in progress have stopped_at NULL. They are filtered out in WHERE before grouping; an instance whose only run is still going therefore forms no group and does not appear, as the statement asks. (Without the filter, TIMESTAMPDIFF would be NULL, and GREATEST with a NULL argument is NULL too, which SUM ignores — but the instance would still show up with a NULL total.)
The alternative writes the floor as a CASE. A run of exactly 60 seconds is billed 60 either way. The query is one grouped pass over the runs.
Reference solution (MySQL)
SELECT instance_id,
SUM(GREATEST(60, TIMESTAMPDIFF(SECOND, started_at, stopped_at))) AS billable_seconds
FROM InstanceRun
WHERE stopped_at IS NOT NULL
GROUP BY instance_idAnother way
SELECT instance_id, SUM(CASE WHEN TIMESTAMPDIFF(SECOND, started_at, stopped_at) < 60 THEN 60 ELSE TIMESTAMPDIFF(SECOND, started_at, stopped_at) END) AS billable_seconds FROM InstanceRun WHERE stopped_at > started_at OR stopped_at = started_at GROUP BY instance_id← Amazon: Subscribe & Save Discount Tier per Delivery Month · Amazon: Buy Box Winner for Each ASIN →