Amazon: EC2 Billable Seconds With the One-Minute Minimum

Easy
580.

Amazon: EC2 Billable Seconds With the One-Minute Minimum

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

ColumnType
run_idint
instance_idvarchar
started_atdatetime
stopped_atdatetime

Primary key: run_id.

One row per start-to-stop run of an instance; stopped_at is NULL while it is still running.

Example

The rows may come back in any order; the columns must be the ones shown, in that order.

InstanceRun

run_idinstance_idstarted_atstopped_at
1i-0a172025-09-01 10:00:002025-09-01 10:00:25
2i-0a172025-09-01 11:00:002025-09-01 11:30:00
3i-0b422025-09-01 09:00:002025-09-01 09:01:00
4i-0b422025-09-01 12:00:00NULL
5i-0c992025-09-01 13:00:00NULL
6i-0d052025-09-01 08:00:002025-09-01 08:00:59

Output

instance_idbillable_seconds
i-0a171860
i-0b4260
i-0d0560
Read the hints, the editorial and the reference solution
-- Write your MySQL query below

Sign in to write, run and submit queries

A free account — no card, no trial clock. You come straight back here.

Run your query to see its result next to the expected one, example by example.

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

ColumnType
run_idint
instance_idvarchar
started_atdatetime
stopped_atdatetime

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_idinstance_idstarted_atstopped_at
1i-0a172025-09-01 10:00:002025-09-01 10:00:25
2i-0a172025-09-01 11:00:002025-09-01 11:30:00
3i-0b422025-09-01 09:00:002025-09-01 09:01:00
4i-0b422025-09-01 12:00:00NULL
5i-0c992025-09-01 13:00:00NULL
6i-0d052025-09-01 08:00:002025-09-01 08:00:59

Output

instance_idbillable_seconds
i-0a171860
i-0b4260
i-0d0560

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_id

Another 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 →