Sandeshliq providers cosmoverse
Updated 2022-10-12
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 stars_price as
(
select
recorded_at::date as "date",
symbol as stars_symbol,
avg(price) as stars_price,
avg(volume_24h) as stars_volume
from osmosis.core.dim_prices
where 1=1
and symbol='STARS'
and recorded_at > '2022-09-12'
group by "date",stars_symbol
)
select
lp.block_timestamp::date as "date",
count(distinct lp.LIQUIDITY_PROVIDER_ADDRESS) as users,
case
when lp.block_timestamp::date < '2022-09-26' and lp.action = 'pool_exited' then 'exited pool before cosmoverse'
when lp.block_timestamp::date < '2022-09-26' and lp.action = 'pool_joined' then 'joined pool before cosmoverse'
when lp.block_timestamp::date >= '2022-09-26' and lp.action = 'pool_joined' then 'joined pool after cosmoverse'
when lp.block_timestamp::date >= '2022-09-26' and lp.action = 'pool_exited' then 'exited pool after cosmoverse'
else 'after cosmerverse'
end as event,
case
when event='exited pool before cosmoverse' then count(distinct lp.tx_id)*-1
when event='joined pool before cosmoverse' then count(distinct lp.tx_id)
when event='exited pool after cosmoverse' then count(distinct lp.tx_id)*-1
when event='joined pool after cosmoverse' then count(distinct lp.tx_id)
end as number_of_transactions,
case
when event='exited pool before cosmoverse' then count(distinct lp.LIQUIDITY_PROVIDER_ADDRESS)*-1
when event='joined pool before cosmoverse' then count(distinct lp.LIQUIDITY_PROVIDER_ADDRESS)
when event='exited pool after cosmoverse' then count(distinct lp.LIQUIDITY_PROVIDER_ADDRESS)*-1
when event='joined pool after cosmoverse' then count(distinct lp.LIQUIDITY_PROVIDER_ADDRESS)
end as number_of_users,
Run a query to Download Data