select recorded_at::date as price_date

, symbol as token_name

, dim_labels.address as token_address

, avg(price) as token_price

from osmosis.core.dim_prices

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

where symbol = 'OSMO'

group by 1, 2, 3

select count(case when ROI < 1 then liquidity_provider_address else null end) as "ROI < 1%"

, count(case when ROI between 1 and 20 then liquidity_provider_address else null end) as "ROI 1%-20%"

, count(case when ROI > 20 then liquidity_provider_address else null end) as "ROI > 20%"

select liquidity_provider_address

, lp_value_added

, lp_value_removed

, lp_value_added - lp_value_removed as lp_value

, ((lp_value_added - lp_value_removed) / (sum(lp_value_added - lp_value_removed) over ())) * 100 as ROI

from

(

select fact_liquidity_provider_actions.liquidity_provider_address

, sum(case when action = 'pool_joined' then fact_liquidity_provider_actions.amount * cte_token_price.token_price else 0 end) as lp_value_added

, sum(case when action = 'pool_exited' then fact_liquidity_provider_actions.amount * cte_token_price.token_price else 0 end) as lp_value_removed

from osmosis.core.fact_liquidity_provider_actions

join cte_token_price on fact_liquidity_provider_actions.currency = cte_token_price.token_address

and fact_liquidity_provider_actions.block_timestamp::date = cte_token_price.price_date

where fact_liquidity_provider_actions.block_timestamp between CURRENT_DATE - 60 and CURRENT_DATE

group by 1

) liquidity_provider

where lp_value_added - lp_value_removed > 0

) ROI_result