Subchapter 103.22

Example Web Traffic By Device Type

references/example-web-traffic-by-device-type.mdMarkdown1 KBView on GitHub

Web traffic views by device type

Example Web Traffic By Device Type · Querying PostHog Data · PostHog/skills · Skills Docs

SELECT
    breakdown_value AS `context.columns.breakdown_value`,
    tuple(uniq(filtered_person_id), NULL) AS `context.columns.visitors`,
    tuple(sum(filtered_pageview_count), NULL) AS `context.columns.views`,
    divide(`context.columns.visitors`.1, sum(`context.columns.visitors`.1) OVER ()) AS `context.columns.ui_fill_fraction`
FROM
    (SELECT
        any(person_id) AS filtered_person_id,
        count() AS filtered_pageview_count,
        properties.$device_type AS breakdown_value,
        if(equals(bitAnd(bitShiftRight(events.$session_id_uuid, 76), 15), 7), events.$session_id, NULL) AS session_id,
        any(if(equals(bitAnd(bitShiftRight(events.$session_id_uuid, 76), 15), 7), fromUnixTimestamp(intDiv(toInt(bitShiftRight(events.$session_id_uuid, 80)), 1000)), NULL)) AS start_timestamp
    FROM
        events
    WHERE
        and(or(and(greaterOrEquals(timestamp, assumeNotNull(toDateTime('2025-12-03 00:00:00'))), lessOrEquals(timestamp, assumeNotNull(toDateTime('2025-12-10 23:59:59')))), false), or(equals(event, '$pageview'), equals(event, '$screen')), 1)
    GROUP BY
        session_id,
        breakdown_value)
GROUP BY
    `context.columns.breakdown_value`
ORDER BY
    `context.columns.visitors` DESC,
    `context.columns.views` DESC,
    `context.columns.breakdown_value` ASC
LIMIT 50000