action,

count (distinct tx_id) as trxs,

count (distinct liquidity_provider_address) as LP_provider,

count (distinct new_lper) as new_LP_providers,

sum (amount*USDPrice/pow(10,decimal)) as volume,

avg (amount*USDPrice/pow(10,decimal)) as avg_volume,

case when date < '2022-11-22' then 'before announcement of being selected as pool of the week'

when date >= '2022-11-22' then 'after announcement of being selected as pool of the week'

end as period

from osmosis.core.fact_liquidity_provider_actions a join (select liquidity_provider_address as New_LPer,

min (block_timestamp) as mindate

from osmosis.core.fact_liquidity_provider_actions

where tx_status = 'SUCCEEDED'

and pool_id = '840'

and action = 'pool_joined'

group by 1) b on a.block_timestamp::Date = b.mindate::date

full outer join (select recorded_at::date as day,

address,

symbol,

avg (price) as USDPrice

from osmosis.core.dim_prices a join osmosis.core.dim_labels b on a.symbol = b.project_name

where symbol != 'IOV'

group by 1,2,3

union all

select hour::date,--calculate price of wbnb

'ibc/F4A070A6D78496D53127EA85C094A9EC87DFC1F36071B8CCDDBD020F933D213D' as address1,

symbol,

avg (price) as USDPrice

from ethereum.core.fact_hourly_token_prices

where symbol = 'WBNB'

group by 1,2,3) c on a.currency = c.address and a.block_timestamp::Date = c.day

where tx_status = 'SUCCEEDED'

and pool_id = '840'