pouya_22Hop In the Pool - Volume stats
Updated 2022-11-28
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 single_asset as (select
tx_id, count(tx_id)
from osmosis.core.fact_liquidity_provider_actions
where action = 'pool_joined'
and tx_status = 'SUCCEEDED'
group by 1
having count(tx_id) < 2),
both_assets as (select
tx_id, count(tx_id)
from osmosis.core.fact_liquidity_provider_actions
where action = 'pool_joined'
and tx_status = 'SUCCEEDED'
group by 1
having count(tx_id) > 1),
prices as (select
recorded_at::date as date,
symbol,
address,
avg(price) as price_usd
from osmosis.core.dim_prices a
join osmosis.core.dim_labels b on a.symbol = b.project_name
group by 1,2,3)
select
'single_asset' as type,
sum((amount / power(10, decimal)) * price_usd) as volume
from osmosis.core.fact_liquidity_provider_actions a
join prices b on a.currency = b.address and b.date = a.block_timestamp::date
where tx_id in (select tx_id from single_asset)
and symbol not in ('INJ', 'IOV')
union
select
Run a query to Download Data