mz01112023-06-02 10:57 PM
Updated 2023-06-02
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
›
⌄
with tab1 as (select
RECORDED_HOUR,
avg (price) as usd_price,
currency
from osmosis.core.ez_prices
where CURRENCY = 'uosmo'
and RECORDED_HOUR >= CURRENT_DATE - 19
group by 1 , 3
order by 1 DESC
)
SELECT
DISTINCT liquidity_provider_address as user1,
count(distinct tx_id) as lp_actions,
sum((amount/pow(10,DECIMAL))* usd_price) as USD_Volume
from osmosis.core.fact_liquidity_provider_actions a
join tab1 b on trunc(a.block_timestamp,'day') = date_trunc('day', b.RECORDED_HOUR)
where a.CURRENCY= 'uosmo'
and block_timestamp:: date >= CURRENT_DATE - 9
and action in ('pool_exited')
and tx_succeeded = 'true'
GROUP BY 1
order by 3 DESC
limit 10
Run a query to Download Data