Snowflake Example Queries

1
SELECT
2
    publisher_id,
3
    sum(requests) AS total,
4
    sum(ivt_bots) AS ivt,
5
    ivt / total AS percent_ivt,
6
    sum(sivt_bots) AS sivt,
7
    sivt / total AS percent_sivt,
8
    sum(givt_bots) AS givt,
9
    givt / total AS percent_givt,
10
    sum(valid_requests) AS valid,
11
    valid / total AS percent_valid
12
FROM mediaguard_prebid
13
GROUP BY 1;

1
SELECT
2
    supplier_id,
3
    sum(requests) AS total,
4
    sum(ivt_bots) AS ivt,
5
    ivt / total AS percent_ivt,
6
    sum(sivt_bots) AS sivt,
7
    sivt / total AS percent_sivt,
8
    sum(givt_bots) AS givt,
9
    givt / total AS percent_givt,
10
    sum(valid_requests) AS valid,
11
    valid / total AS percent_valid
12
FROM mediaguard_prebid
13
GROUP BY 1;

1
SELECT
2
    app_id,
3
    sum(requests) AS total,
4
    sum(ivt_bots) AS ivt,
5
    ivt / total AS percent_ivt,
6
    sum(sivt_bots) AS sivt,
7
    sivt / total AS percent_sivt,
8
    sum(givt_bots) AS givt,
9
    givt / total AS percent_givt,
10
    sum(valid_requests) AS valid,
11
    valid / total AS percent_valid
12
FROM mediaguard_prebid_sampled
13
GROUP BY 1;

1
SELECT
2
    environment,
3
    ua_os_name,
4
    ct:"de" AS device_type,
5
    count(*) AS total,
6
    count(*)/SUM(count(*)) OVER() AS percent_total,
7
    sum(iff(bot, 1, 0)) AS ivt,
8
    ivt / total AS percent_ivt,
9
    sum(iff(sivt, 1, 0)) AS sivt,
10
    sivt / total AS percent_sivt,
11
    sum(iff(givt, 1, 0)) AS givt,
12
    givt / total AS percent_givt,
13
    sum(iff(ivt, 0, 1)) AS valid,
14
    valid / total AS percent_valid
15
FROM mediaguard_postbid
16
WHERE platform = 'CTV'
17
GROUP BY 1, 2, 3
18
ORDER BY 1 nulls FIRST;

1
SELECT
2
    environment,
3
    ua_os_name,
4
    ct:"de" AS device_type,
5
    count(*) AS total,
6
    count(*)/SUM(count(*)) OVER() AS percent_total,
7
    sum(iff(bot, 1, 0)) AS ivt,
8
    ivt / total AS percent_ivt,
9
    sum(iff(sivt, 1, 0)) AS sivt,
10
    sivt / total AS percent_sivt,
11
    sum(iff(givt, 1, 0)) AS givt,
12
    givt / total AS percent_givt,
13
    sum(iff(ivt, 0, 1)) AS valid,
14
    valid / total AS percent_valid
15
FROM mediaguard_postbid
16
WHERE platform = 'Mobile'
17
GROUP BY 1, 2, 3
18
ORDER BY 1 nulls FIRST;

1
SELECT
2
    domains:"href_domain" AS href_domain,
3
    count(*) AS total,
4
    sum(iff(bot, 1, 0)) AS ivt,
5
    bot / total AS percent_ivt,
6
    sum(iff(sivt, 1, 0)) AS sivt,
7
    sivt / total AS percent_sivt,
8
    sum(iff(givt, 1, 0)) AS givt,
9
    givt / total AS percent_givt,
10
    sum(iff(bot, 0, 1)) AS valid,
11
    valid / total AS percent_valid
12
FROM mediaguard_postbid
13
GROUP BY 1
14
HAVING percent_sivt > 0.49;