with daily_total as ( select date, substr(currency, 11,3) as pool_id, sum(balance/pow(10, decimal)) as daily_total_balance from osmosis.core.fact_daily_balances where currency like 'gamm/pool%' and date in (select max(date) from osmosis.core.fact_daily_balances) group by 1, 2 ), daily_per_address as ( SELECT date, address, substr(currency, 11,3) as pool_id, sum(balance/pow(10, decimal)) as daily_total_balance_address FROM osmosis.core.fact_daily_balances WHERE currency like 'gamm/pool%' and date in (select max(date) from osmosis.core.fact_daily_balances) group by 1, 2, 3 ), daily_per_address_percent as ( select a.date, a.address, a.pool_id, daily_total_balance_address, daily_total_balance, (daily_total_balance_address/daily_total_balance)*100 as percentage, nvl(staked_osmo, 0) as staked_osmo, case when percentage > 10 then 'More than 10%' when percentage > 1 and percentage <= 10 then 'Between 1% and 10%' when percentage > 0.1 and percentage <= 1 then 'Between 0.1% and 1%' when percentage > 0.01 and percentage <= 0.1 then 'Between 0.01% and 0.1%' when percentage > 0.001 and percentage <= 0.01 then 'Between 0.001% and 0.01%' when percentage < 0.001 then 'Less than 0.001%' else 'Other' end as percentage_str, case when percentage > 10 then 6 when percentage > 1 and percentage <= 10 then 5 when percentage > 0.1 and percentage <= 1 then 4 when percentage > 0.01 and percentage <= 0.1 then 3 when percentage > 0.001 and percentage <= 0.01 then 2 when percentage < 0.001 then 1 else 0 end as percentage_numeric from daily_per_address a left join daily_total b on a.date = b.date and a.pool_id = b.pool_id left join ( select address, balance/pow(10, decimal) as staked_osmo from osmosis.core.fact_daily_balances where balance_type = 'staked' and currency = 'uosmo' and date in (select max(date) from osmosis.core.fact_daily_balances) ) c on a.address = c.address ) select date, pool_id, percentage_str, percentage_numeric, avg(staked_osmo) as average_staked_osmo from daily_per_address_percent where pool_id = '{{pool_id}}' group by 1, 2, 3, 4

Osmosis staking rewards