SELECT

date,

address,

balance / POW(10, coalesce(decimal, 18)) as balance,

currency,

CASE WHEN currency like 'gamm/pool/%' THEN

LTRIM(currency, 'gamm/pool/')

ELSE NULL END as pool_id

FROM osmosis.core.fact_daily_balances

WHERE

address = '{{address}}'

AND balance_type = 'locked liquidity'

),

pool_liquidity AS (

SELECT

date,

currency,

SUM(balance / POW(10, coalesce(decimal, 18))) as pool_depth

FROM osmosis.core.fact_daily_balances

GROUP BY date, currency

),

percentage_pool as (

SELECT

u.date,

address,

balance,

u.currency,

balance / pool_depth as percent_pool,

u.pool_id

FROM user_balances u

INNER JOIN pool_liquidity l

ON u.date = l.date

AND u.currency = l.currency

WHERE u.pool_id = {{pool_id}}

),