saeedmznPool of The Week -- Pool 833 - stOSMO / OSMO over time
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 stOSMO_price as (
select date_trunc(day,recorded_at)::date day,
'ibc/D176154B0C63D1F9C6DCFB4F70349EBF2E2B5A87A05902F57A6AE92B863E9AEC' address,
'stOSMO' price,
avg (price) as USDPrice
from osmosis.core.dim_prices t1 join osmosis.core.dim_labels t2 on t1.symbol = t2.project_name
where symbol = 'OSMO'
and date_trunc(day,recorded_at)::date >= '2022-11-01'
group by 1,2,3
),
prices as (
select date_trunc(day,recorded_at)::date day,
address,
symbol,
avg (price) price
from osmosis.core.dim_prices join osmosis.core.dim_labels on project_name = symbol
where date_trunc(day,recorded_at)::date >= '2022-11-01'
and symbol <> 'IOV'
group by 1,2,3
),
ALL_prices as (
select * from stOSMO_price
UNION
select * from prices
)
select block_Timestamp::date daily,
action,
case when daily < '2022-11-11' then 'Before November 11'
when daily >= '2022-11-11' then 'After November 11'
end range,
COUNT (DISTINCT tx_id) num_transactions ,
COUNT (DISTINCT liquidity_provider_address) num_LPers,
SUM (amount*price/pow(10,decimal)) volume,
avg (amount*price/pow(10,decimal)) avg_volume
from osmosis.core.fact_liquidity_provider_actions join ALL_prices on currency = address and block_timestamp::Date = day
where tx_status = 'SUCCEEDED'
Run a query to Download Data