ELAYDistribution of actions volume
Updated 2022-11-29Copy 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
›
⌄
with price as (
select recorded_at::date as day, address,
symbol, avg (price) as USD_Price
from osmosis.core.dim_prices t1
join osmosis.core.dim_labels t2 on t1.symbol = t2.project_name
where symbol != 'OSMO'
group by 1,2,3
),
Both_asset as (
select tx_id,
count (tx_id) as TXS_Count
from osmosis.core.fact_liquidity_provider_actions
where action = 'pool_joined'
and tx_status = 'SUCCEEDED'
group by 1
having TXS_Count > 1
)
select '(Both Asset)' as join_type,
case
when amount*usd_price/pow(10,decimal) < 10 then 'Less Than $10'
when amount*usd_price/pow(10,decimal) >= 10 and amount*usd_price/pow(10,decimal) < 100 then '$10 - $100'
when amount*usd_price/pow(10,decimal) >= 100 and amount*usd_price/pow(10,decimal) < 1000 then '$100 - $1000'
when amount*usd_price/pow(10,decimal) >= 1000 and amount*usd_price/pow(10,decimal) < 10000 then '$1000 - $10000' else 'More Than $10000' end as type,
count (distinct tx_id) as TX_Count
from osmosis.core.fact_liquidity_provider_actions t1
join price t2 on t1.block_timestamp::date = t2.day and t1.currency = t2.address
where tx_status = 'SUCCEEDED'
and tx_id in (select tx_id from Both_asset)
group by 1,2
Run a query to Download Data