flipsidecryptoAXELScore App Visits - Layer3 %
Updated 2023-07-26
99
1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
28
29
30
31
32
33
34
35
36
›
⌄
-- 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