Telemetry/LongitudinalExamples

Note: There is good background in the example notebook for the longitudinal data set.

Table structure

Get an overview of the longitudinal data table:

describe longitudinal

That table has a row for each client, with columns for the different parts of the ping. Some properties are directly available to query on:

SELECT count(*) AS count
FROM longitudinal
WHERE os = 'Linux'

Sampling

While composing queries, it can be helpful to work on small samples to reduce query runtimes:

SELECT * FROM longitudinal LIMIT 1000 ...

Or to look at a 1% sample of the clients:

SELECT * FROM longitudinal WHERE sample_id[1] = 5 ...

The sample_id partitions the clients into stable ~1% samples.

Arrays

Other properties are arrays, which contain one entry for each submission from that client (newest first):

SELECT reason[1] AS newest_reason
FROM longitudinal
WHERE os = 'Linux'

To expand arrays and maps and work on the data in them row-wise we can use UNNEST(array):

WITH lengths AS
  (SELECT os, greatest(-1, least(31, sl / (24*60*60))) AS days
   FROM longitudinal
   CROSS JOIN UNNEST(session_length, reason) AS t(sl, r)
   WHERE r = 'shutdown' OR r = 'aborted-session')
SELECT os, days, count(*) AS count
FROM lengths
GROUP BY days, os ORDER BY days ASC

Links:

• Documentation on array functions
• UNNEST documentation

Maps

Some fields like active_addons or user_prefs are handled as maps, on which you can use the [] operator and special functions:

WITH adp AS
  (SELECT active_addons[1]['{d10d0bf8-f5b5-c8b4-a8b2-2b9879e08c5d}']
            IS NOT null AS has_adblockplus
   FROM longitudinal)
SELECT has_adblockplus, count(*) AS count
FROM adp GROUP BY 1 ORDER BY 2 DESC

Links:

• Documentation on map functions

Examples

• Blocklist URLs (extensions.blocklist.url):

SELECT bl, COUNT(bl)
FROM
  (SELECT element_at(settings, 1).user_prefs['extensions.blocklist.url'] AS bl
   FROM longitudinal)
GROUP BY bl

• Blocklist enabled/disabled (extensions.blocklist.enabled) count:

SELECT bl, COUNT(bl)
FROM
  (SELECT element_at(settings, 1).blocklist_enabled AS bl
   FROM longitudinal)
GROUP BY bl