select trunc(block_timestamp,'day') as date,

tx_id,

liquidity_provider_address as providers,

pool_id,

amount/pow(10,6) as amounts

,case when CURRENCY = 'uosmo' then 'Osmo'

when CURRENCY = 'ibc/987C17B11ABC2B20019178ACE62929FE9840202CE79498E29FE8E5CB02B7C0A4' then 'stars'

end as token,

from osmosis.core.fact_liquidity_provider_actions

where pool_id = '604'

and TX_STATUS = 'SUCCEEDED' and (CURRENCY = 'ibc/987C17B11ABC2B20019178ACE62929FE9840202CE79498E29FE8E5CB02B7C0A4'

or CURRENCY in ('osmo','uosmo'))

and block_timestamp >= CURRENT_DATE - 7)

select date,

token,

count(distinct(providers)) as total_user,

count(distinct(tx_id)) as total_transactions,

sum(amounts) as total_amount,

avg(amounts) as avg_amount,

sum(total_user) over (partition by token order by date asc) as cumu_user,

sum(total_transactions) over (partition by token order by date asc) as cumu_tx,

sum(total_amount) over (partition by token order by date asc) as cumu_amount

from liquidity

where action = 'pool_joined'

group by 1,2