bachiwallet1
Updated 2022-11-06Copy Reference Fork
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
›
⌄
with wallet_dtls as (
select *
from (values ('0x55ad22f01ef568a1','Blocto')
, ('0x8234007b36f8113c','Monsoon')
, ('0x18eb4ee6b3c026d2','Dapper')
, ('0x39e42c67cc851cfb','Lilico')
, ('0x94bb84386cfc3b5d','Everbloom')
, ('0xecfad18ba9582d4f','Joyride')
) tab (wallet_provider_address,
wallet_provider)
)
SELECT day,
wallet_provider,
count(DISTINCT(proposer)) AS no_of_users,
count(DISTINCT(tx_id)) AS no_of_txns,
round(SUM(asset_volume),2) AS total_volume,
round(avg(asset_volume),2) AS avg_volume,
(no_of_txns/no_of_users) AS avg_txns_per_wallet
FROM (
SELECT DATE(a.block_timestamp) AS day,
wallet_provider,
proposer,
a.tx_id,
split(event_contract, '.')[2] AS asset_name,
event_data:amount AS asset_volume
FROM flow.core.fact_events a
join flow.core.fact_transactions b
ON a.tx_id = b.tx_id
join wallet_dtls c
ON payer = c.wallet_provider_address
WHERE wallet_provider IS NOT NULL
AND a.tx_succeeded = 'TRUE'
AND payer IN
(
SELECT wallet_provider_address
Run a query to Download Data