mlhCompare weekly count of deposits and withdrawals trxs
Updated 2023-01-13Copy 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
›
⌄
(SELECT
trunc(block_timestamp,'week') as week,
'withdraw' as type,
pool,
sum(1) as trxs,
count(distinct tx_to) as daily_users,
sum(amount_usd) as daily_amount
from (select * from (
SELECT
distinct address_name as pool,
block_timestamp,
tx_id,
tx_to,
amount*close as amount_usd
from solana.core.fact_transfers x
join solana.core.dim_labels y on x.tx_from=y.address
join solana.core.ez_token_prices_hourly z on x.mint=z.token_address and trunc(x.block_timestamp,'hour')=z.recorded_hour
where block_timestamp>='2022-03-23' -- orca whirlpools launch
and label_type = 'dex' and label_subtype = 'pool' and label ='orca' and address_name not like '%aquafarm%'
--and x.index like '%4%'
)
where tx_id in (select tx_id from solana.core.fact_events where program_id='whirLbMiicVdio4qvUfM5KAg6Ct8VwpYzGff3uctyCc')
)
group by 1,3 having daily_amount>1000
order by 1 asc)
UNION ALL
(SELECT trunc(block_timestamp,'week') as week,
'deposit' as type,
pool,
sum(1) as trxs,
count(distinct tx_from) as daily_users,
sum(amount_usd) as daily_amount
from (select *
from (SELECT distinct address_name as pool,
Run a query to Download Data