select

date_trunc('day', recorded_at) as day,

address,

symbol,

avg(price) as price

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

group by 1, 2, 3

),

LP_type as (

SELECT

block_timestamp,

tx_id,

CASE WHEN deposits > 1 THEN 'both assets LP' else 'singleside LP' END as deposit_type--,

-- count(distinct tx_id) as tx_count,

-- count(distinct liquidity_provider_address) as unique_user,

-- sum(VOLUME) as volume

FROM (

SELECT --*

block_timestamp,

tx_id,

count(DISTINCT currency) as deposits--,

-- sum(price * (amount/power(10, decimal))) as volume

FROM osmosis.core.fact_liquidity_provider_actions --join price on address = currency and day = block_timestamp::date

WHERE action = 'pool_joined' and tx_succeeded = TRUE

GROUP BY 1, 2

select

date_trunc('week', a.block_timestamp) as date,

symbol,

case when symbol != 'INJ' then sum(price * (amount/power(10, decimal))) else sum(price * (amount/power(10, 18))) end as volume,

case when symbol != 'INJ' then avg(price * (amount/power(10, decimal))) else avg(price * (amount/power(10, 18))) end as swap_size_usd,

case when symbol != 'INJ' then avg(amount/power(10, decimal)) else avg(amount/power(10, 18)) end as swap_size_token,

count(distinct a.tx_id) as LP_count,