hessUsers Breakdown Based on Total Transferred Volume per token
Updated 2023-02-25Copy 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 new_users as ( select min(block_timestamp) as date, receiver
from osmosis.core.fact_transfers
group by 2)
,
new as ( select DISTINCT receiver
from new_users
where date >= CURRENT_DATE - 30)
,
transfer as ( select date(block_timestamp) as date, project_name, amount/pow(10,a.decimal) as amounts, tx_id, receiver
FROM osmosis.core.fact_transfers a LEFT outer JOIN osmosis.core.dim_tokens b on a.currency = b.address
where receiver ilike 'osmo%'
and (project_name ilike '%bnb%' or project_name ilike '%avax%' or project_name ilike '%mars%' or project_name ilike '%avax%' or
project_name ilike '%ACRE%' or project_name ilike '%ARUSD%' or project_name ilike '%ngm%' or project_name ilike '%luna%' or
project_name ilike '%matic%' or project_name ilike '%WETH%' or project_name ilike '%btc%')
and block_timestamp >= '2023-01-01')
,
price as ( select date(RECORDED_HOUR) as date, symbol, avg(price) as avg_price
from osmosis.core.ez_prices
where recorded_hour >= '2023-01-01'
and symbol in (select project_name from transfer)
group by 1,2)
,
final as ( select receiver, symbol, count(DISTINCT(tx_id)) as total_transfer, count(DISTINCT(receiver)) as total_receiver, sum(amounts*avg_price) as volume,
sum(amounts) as total_amount, avg(amounts*avg_price) as avg_volume
from price a left outer join transfer b on a.date = b.date and a.symbol = b.project_name
where receiver not in (select receiver from new)
group by 1,2)
select count(DISTINCT(receiver)) as total_user, symbol,
case when volume <= 1 then 'Below 1$'
when volume <= 5 then '1-5$'
when volume <= 10 then '5-10$'
when volume <= 25 then '10-25$'
when volume <= 50 then '25-50$'
when volume <= 100 then '50-100$'
when volume <= 250 then '100-250$'
Run a query to Download Data