Google: Cloud VM Cost With Machine Types Filled Forward

Hard
693.

Google: Cloud VM Cost With Machine Types Filled Forward

A Google Cloud billing engineer rebuilds VM costs from a compact usage log. To save space the log records an instance's machine_type only on days it changed; on other days the column is NULL and the instance kept the last type recorded before it. Days before an instance's first recorded type have an unknown type.

Fill each day's type forward, price it with MachinePrice, and return for every instance in the log: instance_id, billed_hours (hours on days with a known type), cost_cents (the sum of hours × cents_per_hour of that day's type; 0 if none) and unknown_hours (hours on days whose type is unknown). Order by instance_id.

A practice scenario set at Google: the tables and data are invented, and this is not a question from, or endorsed by, Google.

Tables

Table: VmUsage

ColumnType
instance_idint
usage_datedate
hoursint
machine_typevarchar

Primary key (together): instance_id, usage_date.

Hours one VM instance ran on one day. machine_type is set only on the day the type changed (or was first logged), NULL otherwise; days the VM did not run have no row.

Table: MachinePrice

ColumnType
machine_typevarchar
cents_per_hourint

Primary key: machine_type.

The hourly list price of a machine type; every type in the log is priced here.

Example

The rows must come back in the order shown.

VmUsage

instance_idusage_datehoursmachine_type
12025-06-0110e2-small
12025-06-0224NULL
12025-06-0324e2-standard-4
12025-06-045NULL
12025-06-068NULL
22025-06-016NULL
22025-06-0212NULL
22025-06-0324n2-highmem-8
22025-06-043NULL
32025-06-024NULL

MachinePrice

machine_typecents_per_hour
e2-small2
e2-standard-413
n2-highmem-852
c3-standard-834

Output

instance_idbilled_hourscost_centsunknown_hours
1715490
227140418
3004
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.

Google: Cloud VM Cost With Machine Types Filled Forward

A Google Cloud billing engineer rebuilds VM costs from a compact usage log.

  • Difficulty: Hard
  • Topics: Window Functions, Joins, Aggregation
  • Dialect: MySQL
  • Problem: #693

Problem statement

A Google Cloud billing engineer rebuilds VM costs from a compact usage log. To save space the log records an instance's machine_type only on days it changed; on other days the column is NULL and the instance kept the last type recorded before it. Days before an instance's first recorded type have an unknown type.

Fill each day's type forward, price it with MachinePrice, and return for every instance in the log: instance_id, billed_hours (hours on days with a known type), cost_cents (the sum of hours × cents_per_hour of that day's type; 0 if none) and unknown_hours (hours on days whose type is unknown). Order by instance_id.

A practice scenario set at Google: the tables and data are invented, and this is not a question from, or endorsed by, Google.

Tables

Table: VmUsage

ColumnType
instance_idint
usage_datedate
hoursint
machine_typevarchar

Primary key: instance_id, usage_date.

Hours one VM instance ran on one day. machine_type is set only on the day the type changed (or was first logged), NULL otherwise; days the VM did not run have no row.

Table: MachinePrice

ColumnType
machine_typevarchar
cents_per_hourint

Primary key: machine_type.

The hourly list price of a machine type; every type in the log is priced here.

Examples

Example 1

VmUsage

instance_idusage_datehoursmachine_type
12025-06-0110e2-small
12025-06-0224NULL
12025-06-0324e2-standard-4
12025-06-045NULL
12025-06-068NULL
22025-06-016NULL
22025-06-0212NULL
22025-06-0324n2-highmem-8
22025-06-043NULL
32025-06-024NULL

MachinePrice

machine_typecents_per_hour
e2-small2
e2-standard-413
n2-highmem-852
c3-standard-834

Output

instance_idbilled_hourscost_centsunknown_hours
1715490
227140418
3004

How to solve Google: Cloud VM Cost With Machine Types Filled Forward

This is forward fill: carry the last known value onto later rows until a new one appears. SQL has no "ignore nulls" for LAG in MySQL, so the standard trick builds groups. A running COUNT(machine_type) per instance in date order counts only non-NULL values, so it rises by one on each day a type was logged and stays flat on the NULL days after it. Every day therefore shares a group number with the logged day it inherits from.

Inside such a group exactly one row carries a type, so MAX(machine_type) OVER (PARTITION BY instance_id, type_grp) copies it onto every row of the group. Rows before the first logged day are in group 0, where every value is NULL, and stay NULL — that is the unknown type the statement separates out.

With a type on every row, the rest is a priced aggregate: a LEFT JOIN to MachinePrice (an unknown type has no price), hours × cents summed per instance, and conditional sums to split known from unknown hours. COALESCE makes an instance with only unknown days cost 0 rather than NULL. Calendar gaps do not matter: the fill follows the log rows, not the dates.

The alternative fetches each day's type with a correlated subquery — the latest logged row at or before the day (ORDER BY usage_date DESC LIMIT 1) — which is the definition read literally but probes the table once per row; the window version sorts each instance once.

Reference solution (MySQL)

WITH marked AS (
  SELECT instance_id, usage_date, hours, machine_type,
         COUNT(machine_type) OVER (PARTITION BY instance_id ORDER BY usage_date
                                   ROWS BETWEEN UNBOUNDED PRECEDING AND CURRENT ROW) AS type_grp
  FROM VmUsage
), filled AS (
  SELECT instance_id, hours,
         MAX(machine_type) OVER (PARTITION BY instance_id, type_grp) AS filled_type
  FROM marked
)
SELECT f.instance_id,
       SUM(CASE WHEN f.filled_type IS NOT NULL THEN f.hours ELSE 0 END) AS billed_hours,
       COALESCE(SUM(f.hours * p.cents_per_hour), 0) AS cost_cents,
       SUM(CASE WHEN f.filled_type IS NULL THEN f.hours ELSE 0 END) AS unknown_hours
FROM filled f
LEFT JOIN MachinePrice p ON p.machine_type = f.filled_type
GROUP BY f.instance_id
ORDER BY f.instance_id

Another way

SELECT x.instance_id, SUM(IF(x.filled_type IS NULL, 0, x.hours)) AS billed_hours, SUM(IF(x.filled_type IS NULL, 0, x.hours * (SELECT p.cents_per_hour FROM MachinePrice p WHERE p.machine_type = x.filled_type))) AS cost_cents, SUM(IF(x.filled_type IS NULL, x.hours, 0)) AS unknown_hours FROM (SELECT u.instance_id, u.hours, (SELECT k.machine_type FROM VmUsage k WHERE k.instance_id = u.instance_id AND k.usage_date <= u.usage_date AND k.machine_type IS NOT NULL ORDER BY k.usage_date DESC LIMIT 1) AS filled_type FROM VmUsage u) x GROUP BY x.instance_id ORDER BY x.instance_id

← Google: Play Service Fee and the Purchase That Crossed the Tier · Google: Prorated Workspace Seat-Days for June 2025 →