MLDZMNcosmo3
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
›
⌄
with tb1 as (select
dayname(to_date(block_timestamp::date)) as week_day,
'LP provide' as actions,
sum(AMOUNT/1e18) as volume,
count(distinct tx_id) as action_count
from osmosis.core.fact_liquidity_provider_actions
where CURRENCY='ibc/6AE98883D4D5D5FF9E50D7130F1305DA2FFA0C652D1DD9C123657C6B4EB2DF8A'
and action in ('pool_joined')
and TX_STATUS='SUCCEEDED'
and BLOCK_TIMESTAMP>=CURRENT_DATE-7
group by 1
union all
select
dayname(to_date(block_timestamp::date)) as week_day,
'LP remove' as actions,
sum(AMOUNT/1e18) as volume,
count(distinct tx_id) as action_count
from osmosis.core.fact_liquidity_provider_actions
where CURRENCY='ibc/6AE98883D4D5D5FF9E50D7130F1305DA2FFA0C652D1DD9C123657C6B4EB2DF8A'
and action in ('pool_exited')
and TX_STATUS='SUCCEEDED'
and BLOCK_TIMESTAMP>=CURRENT_DATE-7
group by 1 )
select
week_day,
actions,
case when week_day in ('Sat', 'Sun') then 'WeekEnds'
else 'WeekDays' END as date_type,
avg(volume) as avg_volume,
avg(action_count)
from tb1
group by 1,2
Run a query to Download Data