flipsidecryptoAXELScore App Visits - Layer3 %
    Updated 2023-07-26
    -- forked from AXELScore App Visits - Layer3 @ https://flipsidecrypto.xyz/edit/queries/0fdc0337-f8a5-42df-8356-777246925a11

    -- forked from AXELScore App Visits @ https://flipsidecrypto.xyz/edit/queries/452649ff-1ce5-489a-a37d-58dc43b059dc
    -- forked from flowscored app visits @ https://flipsidecrypto.xyz/edit/queries/4a9f326b-fb5a-4c8f-b7a4-89f5e7cde2da
    -- 17 app visitors before 4/21
    with
    l3 as (
    select
    count(context_request_ip) AS l3_app_visits,
    count(distinct context_request_ip) + 17 AS l3_unique_app_users
    from
    BI_ANALYTICS.SCIENCE_FLIPSIDECRYPTO_XYZ_PROD.PAGES
    where
    timestamp > '2023-04-21'
    AND context_page_path LIKE '%axelscore%'
    AND initial_referrer LIKE '%layer3%'
    ),
    total as (
    select
    count(context_request_ip) AS app_visits,
    count(distinct context_request_ip) + 17 AS unique_app_users,
    context_page_path
    from
    BI_ANALYTICS.SCIENCE_FLIPSIDECRYPTO_XYZ_PROD.PAGES
    where
    timestamp > '2023-04-21'
    AND context_page_path LIKE '%axelscore%'
    GROUP BY
    context_page_path
    )
    SELECT
    l3_app_visits / app_visits as percent_from_l3,
    l3_unique_app_users / unique_app_users as percent_users_l3
    FROM total
    join l3


    Run a query to Download Data