carlesmontalaflow wallet dominance 2
Updated 2022-11-06
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
›
⌄
select
trunc(a.block_timestamp,'week') as date,
case
when payer = '0x39e42c67cc851cfb' then 'lilico'
when payer = '0x55ad22f01ef568a1' then 'Blocto'
when payer = '0x18eb4ee6b3c026d2' then 'Dapper'
when payer = '0x93615d25d14fa337' then 'ChainMonsters'
when payer = '0x1b65c33d7a352c61' then 'Cricket Moments'
when payer = '0x94bb84386cfc3b5d' then 'Everbloom'
when payer = '0x4bbff461fa8f6192' then 'Fantastec'
when payer = '0xecfad18ba9582d4f' then 'JoyRide'
when payer = '0x39e42c67cc851cfb' then 'Lilico'
when payer = '0x8234007b36f8113c' then 'Monsoon'
when payer = '0x6f649aee955bef6d' then 'RCRDSHP'
when payer = '0x9b00972a3ecb364b' then 'Seussibles'
else 'Others'
end as wallets,
count(distinct a.tx_id) as sales,
sum(sales) over(partition by wallets order by date) as cum_sales,
sum(price) as volume,
avg(price) as avg_price_per_nft,
count(distinct buyer) as buyers,
sum(buyers) over(partition by wallets order by date) as cum_buyers
from flow.core.fact_transactions a join flow.core.ez_nft_sales b on a.tx_id = b.tx_id
group by 1,2