I was recently doing some work to understand when server upgrades happened and compare that to when users upgraded their devices… and just wanted to share some SQL with everyone in case it might be useful in your work (or if you already have a better way!).
While there’s no easy way to know exactly when a user upgraded their device, one close proxy is to look at Telemetry data since it stores the user’s current app version and is recorded daily (in v3.12+).
I’ve included two queries: The first is just an easy way to look back and see when server upgrades happened. The second can help you understand how long users are using an old version of the app even though a new version is available to them. Upgrades include important new features, performance enhancements, as well as bug fixes so it’s important that health workers can benefit from these as soon as possible.
Server Upgrade
horti-upgrade records store information about when server upgrades occurred.
NOTE: The time here is when the upgrade started. These tombstone records also include when each stage of the upgrade occurred, but the upgrade start date is easier to extract and a close enough proxy.
SELECT
doc#>>'{tombstone,build_info,version}' AS app_version,
to_timestamp(((doc#>>'{tombstone,created}')::bigint / 1000)::double precision)::date AS upgrade_started
FROM
couchdb
WHERE
doc#>>'{tombstone,_id}' = 'horti-upgrade'
AND doc->>'type' = 'tombstone'
ORDER BY
upgrade_started
Server Upgrade vs. User Telemetry
For a given CHT Version, the query below compares the Server Upgrade date with the date of user’s first recorded telemetry record for that version. The results will be formatted like the screenshot below.
In the example screenshot, we see:
- The upgrade to 3.15.0 was done on June 1st 2022
- 390 users started recording telemetry records with a version of 3.15.0 on that same day (June 1st)
- On June 6th, 51 additional users started recording telemetry records with 3.15.0 as the version
- By Jun 11th, a total of 747 users had already recorded telemetry records with 3.15.0.
WITH upgrade_CTE AS
(
SELECT
doc#>>'{tombstone,build_info,version}' AS app_version,
to_timestamp(((doc#>>'{tombstone,created}')::bigint / 1000)::double precision)::date AS upgrade_started
FROM
couchdb
WHERE
doc#>>'{tombstone,_id}' = 'horti-upgrade'
AND doc->>'type' = 'tombstone'
), earliest_by_user_CTE AS
(
SELECT
doc#>>'{metadata,versions,app}' AS app_version,
doc#>>'{metadata,user}' AS cht_user,
min(date(concat(doc#>>'{metadata,year}','-',doc#>>'{metadata,month}','-',doc#>>'{metadata,day}'))) AS telemetry_date
FROM
couchdb_users_meta
WHERE
doc->>'type'='telemetry'
AND doc#>'{metadata}' ? 'day' --We only care about telemetry records that have a `day` value (ie 3.12.0+)
GROUP BY
app_version,
cht_user
)
SELECT
upgrade_CTE.app_version,
upgrade_CTE.upgrade_started AS instance_upgrade_date,
earliest_by_user_cte.telemetry_date AS first_recorded_telemetry,
(earliest_by_user_cte.telemetry_date - upgrade_CTE.upgrade_started) AS days_between,
count(distinct(earliest_by_user_CTE.cht_user)) AS num_users,
sum(count(*)) OVER (PARTITION BY upgrade_CTE.app_version ORDER BY upgrade_CTE.app_version, earliest_by_user_cte.telemetry_date ASC) AS running_sum_users
FROM
upgrade_CTE
LEFT JOIN earliest_by_user_CTE ON (upgrade_CTE.app_version = earliest_by_user_CTE.app_version)
GROUP BY
upgrade_CTE.app_version,
instance_upgrade_date,
first_recorded_telemetry,
days_between
ORDER BY
upgrade_CTE.app_version,
instance_upgrade_date
;