hessDaily Add stOSMO / OSMO
Updated 2022-12-08
99
1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
›
⌄
-- 11/11/22 Pool 833 - stOSMO / OSMO
with price as ( select RECORDED_AT::date as date, avg(price) as avg_price
from osmosis.core.dim_prices
where RECORDED_AT >= '2022-10-20'
and symbol ilike '%osmo%'
group by 1)
,
pool as ( select date(block_timestamp) as date, currency , case when date < '2022-11-11' then 'Before program' else 'After Program'
end as type, tx_id, LIQUIDITY_PROVIDER_ADDRESS ,amount/pow(10,decimal) as amounts
from osmosis.core.fact_liquidity_provider_actions
where action = 'pool_joined' and tx_status = 'SUCCEEDED' and pool_id[0] = '833' and date >= '2022-10-20')
select a.date, type , count(DISTINCT(tx_id)) as total_tx, count(DISTINCT(LIQUIDITY_PROVIDER_ADDRESS)) as total_address,
sum(amounts*avg_price) as amount_usd, avg(amounts*avg_price) as avg_price
from pool a left outer join price b on a.date = b.date
group by 1,2
Run a query to Download Data