flipsidecryptoCollab User Conversions
Updated 2023-09-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 User Conversions @ https://flipsidecrypto.xyz/edit/queries/fee59470-5956-4348-972a-306bc23da5eb
-- Since 8/17/23
with app_users AS (
select
count(context_request_ip) AS total_app_visits,
count(distinct context_request_ip) AS app_users,
context_page_path
from BI_ANALYTICS.SCIENCE_FLIPSIDECRYPTO_XYZ_PROD.PAGES
where timestamp::DATE between '2023-08-17' and '2023-09-14'
AND
context_page_path LIKE '%flowscored%'
GROUP BY context_page_path
),
collab_wallets AS (
select
dynamic_id,
COUNT(DISTINCT CHAIN) AS chain_count
from BI_ANALYTICS.BRONZE_API.DYNAMIC_API
where environment_id = 'c6ef9d8c-6b8d-441a-9f67-72b728cef538' --Flowscored environment
and wallet_type not in ('blocto', 'lilico')
and chain in ('FLOW', 'EVM')
and last_visit::DATE >= '2023-08-17'
group by 1
having chain_count > 1
),
connected_users AS (
SELECT
count(distinct dynamic_id) as connected_users
from collab_wallets
)
select
total_app_visits,
Run a query to Download Data