hessTransfer Users activities on Osmosis
Updated 2023-02-25
99
1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
›
⌄
with 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, rank() over (partition by symbol order by volume desc) as rank
from price a left outer join transfer b on a.date = b.date and a.symbol = b.project_name
group by 1,2)
select date(block_timestamp) as date, concat(symbol,' Users') as token, count(DISTINCT(tx_id)) as total_tx
from osmosis.core.fact_transactions a join final b on a.tx_from = b.receiver
where date >= '2023-01-01'
group by 1,2
Run a query to Download Data