Abbas_ra2111/11/22 Pool 833 - stOSMO / OSMO pt1
Updated 2022-12-07Copy Reference Fork
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
›
⌄
select
'last 1-week' AS Time,
sum(case when ACTION='pool_joined' then (AMOUNT/power(10,DECIMAL))*price end) AS "IN Amount",
-sum(case when ACTION='pool_exited' then (AMOUNT/power(10,DECIMAL)*price) end) AS "OUT Amount",
"IN Amount"+"OUT Amount" AS "Net Amount",
count(distinct case when ACTION='pool_joined' then LIQUIDITY_PROVIDER_ADDRESS end) AS "LIQUIDITY PROVIDER number",
count(distinct case when ACTION='pool_exited' then LIQUIDITY_PROVIDER_ADDRESS end) AS "LIQUIDITY REMOVER number",
count(distinct case when ACTION='pool_joined' then TX_ID end) AS "ADD events number",
count(distinct case when ACTION='pool_exited' then TX_ID end) AS "REMOVE events number",
AVG(case when ACTION='' then (AMOUNT/power(10,DECIMAL))*price end) AS "INFLOW Average Amount",
AVG(case when ACTION='pool_exited' then (AMOUNT/power(10,DECIMAL)*price) end) AS "OUTFLOW Average Amount"
from osmosis.core.fact_liquidity_provider_actions
inner join (select date_trunc('day',RECORDED_AT) AS DAY,SYMBOL,avg(price) AS price from osmosis.core.dim_prices
where symbol='OSMO' group by 1,2) on DAY=date_trunc('day',BLOCK_TIMESTAMP)
where pool_id[0]='833'
and BLOCK_TIMESTAMP::date >= dateadd('week',-1,CURRENT_DATE)
union ALL
select
'last 2-week' AS Time,
sum(case when ACTION='pool_joined' then (AMOUNT/power(10,DECIMAL))*price end) AS "IN Amount",
-sum(case when ACTION='pool_exited' then (AMOUNT/power(10,DECIMAL)*price) end) AS "OUT Amount",
"IN Amount"+"OUT Amount" AS "Net Amount",
count(distinct case when ACTION='pool_joined' then LIQUIDITY_PROVIDER_ADDRESS end) AS "LIQUIDITY PROVIDER number",
count(distinct case when ACTION='pool_exited' then LIQUIDITY_PROVIDER_ADDRESS end) AS "LIQUIDITY REMOVER number",
count(distinct case when ACTION='pool_joined' then TX_ID end) AS "ADD events number",
count(distinct case when ACTION='pool_exited' then TX_ID end) AS "REMOVE events number",
AVG(case when ACTION='pool_joined' then (AMOUNT/power(10,DECIMAL))*price end) AS "INFLOW Average Amount",
AVG(case when ACTION='pool_exited' then (AMOUNT/power(10,DECIMAL)*price) end) AS "OUTFLOW Average Amount"
from osmosis.core.fact_liquidity_provider_actions
inner join (select date_trunc('day',RECORDED_AT) AS DAY,SYMBOL,avg(price) AS price from osmosis.core.dim_prices
where symbol='OSMO' group by 1,2) on DAY=date_trunc('day',BLOCK_TIMESTAMP)
where pool_id[0]='833'
and BLOCK_TIMESTAMP::date >= dateadd('week',-2,CURRENT_DATE)
union ALL
select
'from 22-11-11' AS Time,
Run a query to Download Data