Some useful SQL to look into upgrades

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:

  1. The upgrade to 3.15.0 was done on June 1st 2022
  2. 390 users started recording telemetry records with a version of 3.15.0 on that same day (June 1st)
  3. On June 6th, 51 additional users started recording telemetry records with 3.15.0 as the version
  4. 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
	;

4 Likes