TryGhost/Ghost

NODE analytics_sources_1
DESCRIPTION >
    Aggregate by referral and calculate session and views

SQL >
    WITH (SELECT domainWithoutWWW(href) FROM analytics_hits LIMIT 1) AS current_domain
    SELECT
        site_uuid,
        toDate(timestamp) AS date,
        device,
        browser,
        location,
        source,
        pathname,
        maxIf(
            member_status,
            member_status IN ('paid', 'free', 'undefined')
        ) AS member_status,
        uniqState(session_id) AS visits,
        countState() AS pageviews
    FROM analytics_hits
    WHERE source != current_domain
    GROUP BY date, device, browser, location, source, pathname, site_uuid

TYPE MATERIALIZED
DATASOURCE analytics_sources_mv