54
edits
Telemetry/LongitudinalExamples: Difference between revisions
Jump to navigation
Jump to search
Adding some notes from my recent experience with STMO
(Adding a new intro) |
(Adding some notes from my recent experience with STMO) |
||
| Line 60: | Line 60: | ||
* [https://prestodb.io/docs/current/functions/map.html Documentation on map functions] | * [https://prestodb.io/docs/current/functions/map.html Documentation on map functions] | ||
=== | === Example Queries === | ||
* Blocklist URLs (extensions.blocklist.url): | * Blocklist URLs (extensions.blocklist.url): | ||
SELECT bl, COUNT(bl) | SELECT bl, COUNT(bl) | ||
| Line 74: | Line 74: | ||
FROM longitudinal) | FROM longitudinal) | ||
GROUP BY bl | GROUP BY bl | ||
* Parsing most recent submission_date | |||
SELECT DATE_PARSE(submission_date[1], '%Y-%m-%dT00:00:00.000Z') as parsed_submission date | |||
FROM longitudinal | |||
* Limiting to most recent ping in the last 7 days | |||
SELECT * FROM longitudinal | |||
WHERE DATE_DIFF('day', DATE_PARSE(submission_date[1], '%Y-%m-%dT00:00:00.000Z'), current_date) < 7 | |||
=== Using Views === | |||
If you find yourself copy/pasting SQL between different queries, consider using a Presto VIEW to allow for code reuse. Views create logical tables which you can reuse in other queries. For example, [https://sql.telemetry.mozilla.org/queries/776/source this view] defines some important filters and derived variables which are then used in [https://sql.telemetry.mozilla.org/queries/777/source#1311 this downstream query]. | |||
You can define a view by prefixing your query with | |||
CREATE OR REPLACE VIEW view_name AS ... | |||
Be careful not to overwrite an existing view! Using a unique name is important. | |||
=== FAQ === | |||
==== I'm getting an error, "... cannot be resolved" ==== | |||
For some reason, re:dash has trouble parsing SQL strings with double quotes. Try using single quotes instead. | |||