WITH user_balances AS (

SELECT

date,

address,

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

currency,

LTRIM(currency, 'gamm/pool/') 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

u.date,

address,

balance,

u.currency,

balance / pool_depth * 100 as percent_pool,

u.pool_id

FROM user_balances u

LEFT OUTER JOIN pool_liquidity l

ON u.date = l.date

AND u.currency = l.currency