Delamir-6014osmo06 3-2
Updated 2022-12-04
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
›
⌄
with pool_1 as (
select
tx_id,
pool_id,
count (tx_id) as TXs_1
from osmosis.core.fact_liquidity_provider_actions
where pool_id = '840'
and block_timestamp::date >= '2022-11-22'
and action = 'pool_joined'
and TX_STATUS = 'SUCCEEDED'
group by 1,2
)
, Price as (
select
date(RECORDED_AT) as date,
avg(price) as Price,
address,
symbol
from osmosis.core.dim_prices a
join osmosis.core.dim_labels b on a.symbol = b.PROJECT_NAME
group by 1,3,4
)
select
LIQUIDITY_PROVIDER_ADDRESS,
count(distinct a.tx_id) as TXs,
sum(amount / pow(10,decimal)) as Total_volume,
sum(amount * Price / pow(10,decimal)) as Total_USD_volume
from osmosis.core.fact_liquidity_provider_actions a
join price b on a.currency = b.address and a.BLOCK_TIMESTAMP::date = b.date
where tx_id in (select tx_id from pool_1)
group by 1
order by 4 desc
limit 5
Run a query to Download Data