A latest-row query cannot tell you whether every device is current

·3 mins

A dashboard can label an account “current” while that account still has an outdated device. Selecting the latest event per account and application retains one observation; it discards observations from other devices. Describe that selection in the report before claiming that the source telemetry cannot distinguish devices.

The distinction matters during a security review: “latest observed state” and “any observed device needs attention” answer different questions. Correct arithmetic cannot repair a mislabeled metric.

Reproduce the lost device with synthetic data

Run this example with Python 3 and SQLite supporting window functions. The names, dates, and status values are synthetic; current is a supplied classification, not an implementation of version comparison.

import sqlite3

con = sqlite3.connect(":memory:")
con.executescript("""
CREATE TABLE observations (
    event_id INTEGER PRIMARY KEY,
    account TEXT, app TEXT, device TEXT,
    observed_at TEXT, current INTEGER
);
INSERT INTO observations VALUES
    (1, 'account-a', 'demo', 'laptop', '2026-01-01T09:00:00Z', 0),
    (2, 'account-a', 'demo', 'phone',  '2026-01-01T10:00:00Z', 1),
    (3, 'account-b', 'demo', 'tablet', '2026-01-01T10:00:00Z', 0),
    (4, 'account-b', 'demo', 'tablet', '2026-01-02T10:00:00Z', 1);
""")

latest_account = """
WITH ranked AS (
    SELECT *, ROW_NUMBER() OVER (
        PARTITION BY account, app
        ORDER BY observed_at DESC, event_id DESC
    ) AS rn
    FROM observations
)
SELECT account, current FROM ranked WHERE rn = 1 ORDER BY account
"""

latest_devices = """
WITH ranked AS (
    SELECT *, ROW_NUMBER() OVER (
        PARTITION BY account, app, device
        ORDER BY observed_at DESC, event_id DESC
    ) AS rn
    FROM observations
)
SELECT account, MAX(CASE WHEN current = 0 THEN 1 ELSE 0 END)
FROM ranked WHERE rn = 1
GROUP BY account, app ORDER BY account
"""

account_rows = con.execute(latest_account).fetchall()
exposure_rows = con.execute(latest_devices).fetchall()
assert account_rows == [('account-a', 1), ('account-b', 1)]
assert exposure_rows == [('account-a', 1), ('account-b', 0)]
print("latest account state:", account_rows)
print("any latest-observed device outdated:", exposure_rows)

Both accounts have a current latest observation. Only account-a has an outdated device among its latest per-device observations. The laptop disappeared from the first query because the phone reported later.

The account-b rows are a negative control. Aggregating every historical outdated event would flag the tablet even after its newer current observation. Rank within each device first, then aggregate the surviving states.

State the measurement contract

An accurate table label might be:

Accounts classified by their latest observed event per application, with ties resolved by event ID.

That supports a claim about selected observations. It does not establish that every device on those accounts is current. Nor does the query establish what the ingestion system could identify before aggregation.

For an exposure report, define the device identity and observation window explicitly. A stable device key is a prerequisite for the second query. An installation identifier that rotates after reinstalling may represent installations rather than physical devices; a shared or missing identifier can merge unrelated observations. Inspect the schema and collection semantics before choosing the partition keys.

The example orders normalized UTC timestamps and breaks ties with a unique event ID. That makes the selection deterministic. It does not prove that a larger ID represents a later real-world event when timestamps tie. SQLite’s window-function documentation describes how ROW_NUMBER() follows the window’s ordering.

Keep unknown devices out of the healthy bucket

Even latest-per-device aggregation describes observed devices. Retired devices, missing telemetry, and a stale last observation need an explicit policy. Define freshness before calling the result a current fleet inventory, and report unknown states separately instead of treating silence as evidence of health.

A reporting correction may leave every displayed count unchanged: the query can stay the same while its title and interpretation become accurate. Calculating a replacement device-level metric is separate work and requires source data that supports it.

Audit the partition keys behind one account-level health table, then check whether its title describes exactly the rows that survive.