Scans daily

1-- core.qrfy.scans_daily
2-- Scans per QR code per day: QRFY's Statistics chart, total and unique scans, by code.
3--
4-- Grain: one row per day (UTC) x QR code (scan_url).
5-- Depends on: staging.qrfy.analytics
6--
7-- `unique_scans` counts scans QRFY flagged as a visitor's first. Unique visitors are
8-- per code, so unique scans summed across codes count a person once per code they
9-- scanned.
10-- `qr_name` and `folder` are the latest values seen in the scan rows for that code, so a
11-- renamed code shows its current name on every day.
12
13WITH latest_label AS (
14    SELECT
15        scan_url,
16        ARRAY_AGG(STRUCT(qr_name, folder) ORDER BY scanned_at DESC LIMIT 1)[OFFSET(0)] AS label
17    FROM {{staging.qrfy.analytics}}
18    GROUP BY 1
19),
20
21daily AS (
22    SELECT
23        DATE(scanned_at)                                        AS date,
24        scan_url,
25        COUNT(*)                                                AS scans,
26        COUNTIF(is_unique_visitor)                              AS unique_scans,
27        COUNT(DISTINCT country_iso)                             AS countries
28    FROM {{staging.qrfy.analytics}}
29    GROUP BY 1, 2
30)
31
32SELECT
33    d.date,
34    d.scan_url,
35    l.label.qr_name                                             AS qr_name,
36    l.label.folder                                              AS folder,
37    d.scans,
38    d.unique_scans,
39    d.countries
40FROM daily d