39
edits
Telemetry/LongitudinalExamples: Difference between revisions
Jump to navigation
Jump to search
no edit summary
Gfritzsche (talk | contribs) (Add sampling example, change page structuring) |
No edit summary |
||
| Line 4: | Line 4: | ||
Get an overview of the longitudinal data table: | Get an overview of the longitudinal data table: | ||
describe | describe longitudinal | ||
That table has a row for each client, with columns for the different parts of the ping. | 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: | Some properties are directly available to query on: | ||
SELECT count(*) AS count | SELECT count(*) AS count | ||
FROM | FROM longitudinal | ||
WHERE os = 'Linux' | WHERE os = 'Linux' | ||
| Line 21: | Line 21: | ||
Other properties are arrays, which contain one entry for each submission from that client (newest first): | Other properties are arrays, which contain one entry for each submission from that client (newest first): | ||
SELECT reason[1] AS newest_reason | SELECT reason[1] AS newest_reason | ||
FROM | FROM longitudinal | ||
WHERE os = 'Linux' | WHERE os = 'Linux' | ||
| Line 27: | Line 27: | ||
WITH lengths AS | WITH lengths AS | ||
(SELECT os, greatest(-1, least(31, sl / (24*60*60))) AS days | (SELECT os, greatest(-1, least(31, sl / (24*60*60))) AS days | ||
FROM | FROM longitudinal | ||
CROSS JOIN UNNEST(session_length, reason) AS t(sl, r) | CROSS JOIN UNNEST(session_length, reason) AS t(sl, r) | ||
WHERE r = 'shutdown' OR r = 'aborted-session') | WHERE r = 'shutdown' OR r = 'aborted-session') | ||
| Line 44: | Line 44: | ||
(SELECT active_addons[1]['{d10d0bf8-f5b5-c8b4-a8b2-2b9879e08c5d}'] | (SELECT active_addons[1]['{d10d0bf8-f5b5-c8b4-a8b2-2b9879e08c5d}'] | ||
IS NOT null AS has_adblockplus | IS NOT null AS has_adblockplus | ||
FROM | FROM longitudinal) | ||
SELECT has_adblockplus, count(*) AS count | SELECT has_adblockplus, count(*) AS count | ||
FROM adp GROUP BY 1 ORDER BY 2 DESC | FROM adp GROUP BY 1 ORDER BY 2 DESC | ||
| Line 56: | Line 56: | ||
FROM | FROM | ||
(SELECT element_at(settings, 1).user_prefs['extensions.blocklist.url'] AS bl | (SELECT element_at(settings, 1).user_prefs['extensions.blocklist.url'] AS bl | ||
FROM | FROM longitudinal) | ||
GROUP BY bl | GROUP BY bl | ||
| Line 63: | Line 63: | ||
FROM | FROM | ||
(SELECT element_at(settings, 1).blocklist_enabled AS bl | (SELECT element_at(settings, 1).blocklist_enabled AS bl | ||
FROM | FROM longitudinal) | ||
GROUP BY bl | GROUP BY bl | ||