WITH stake_claims AS ( SELECT --TRUNC(block_timestamp, 'hour') as hour, TRUNC(block_timestamp, 'day') as date, delegator_address, SUM(amount) / 1e6 as osmo_claimed FROM osmosis.core.fact_staking_rewards GROUP BY /* hour, */date, delegator_address HAVING osmo_claimed >= {{min_claimed}} AND osmo_claimed < {{max_claimed}} ), bonds AS ( SELECT block_timestamp::date as date, pooler, SUM(amount) as amount, denom FROM ( SELECT block_timestamp, sender as pooler, REGEXP_SUBSTR(amount_ori,'^\\d+') as amount, REPLACE(amount_ori, amount, '') as denom FROM ( SELECT block_timestamp, tx_id, msg_index, MAX(IFF(attribute_key = 'owner', attribute_value, '')) as sender, MAX(IFF(attribute_key = 'period_lock_id', attribute_value, '')) as period_lock_id, MAX(IFF(attribute_key = 'amount', attribute_value, '')) as amount_ori FROM osmosis.core.fact_msg_attributes a WHERE (msg_type = 'add_tokens_to_lock' OR msg_type = 'lock_tokens') AND EXISTS ( SELECT 1 FROM osmosis.core.fact_msgs m WHERE tx_status = 'SUCCEEDED' AND a.tx_id = m.tx_id ) GROUP BY block_timestamp, tx_id, msg_index ) ) GROUP BY date, pooler, denom ), lp_minted AS ( SELECT block_timestamp::date as date, liquidity_provider_address, SUM(amount_osmo) / 1e6 as amount_osmo_pooled, SUM(amount_pool) as amount_gamm_minted FROM ( SELECT block_timestamp, tx_id, liquidity_provider_address, MAX(IFF(currency = 'uosmo', IFF(action = 'pool_exited', -amount, amount), null)) as amount_osmo, MAX(IFF(currency LIKE ('gamm/pool%'), IFF(action = 'lp_tokens_burned', -amount, amount), null)) as amount_pool FROM osmosis.core.fact_liquidity_provider_actions GROUP BY block_timestamp, tx_id, liquidity_provider_address HAVING amount_osmo <> 0 ) GROUP BY date, liquidity_provider_address ), asym_pools_either AS ( SELECT TRUNC(block_timestamp, 'day') as date, liquidity_provider_address, SUM(asym_pooled) as amount_asym_pooled FROM ( SELECT block_timestamp, liquidity_provider_address, tx_id, COUNT(*) as token_count, BOOLOR_AGG(currency = 'uosmo') as is_osmo, SUM(IFF(currency = 'uosmo' AND action = 'pool_joined', amount, 0) / 1e6) as osmo_asym_pooled, SUM(IFF(currency = 'uosmo' AND action = 'pool_exited', amount, 0) / 1e6) as osmo_asym_exited, osmo_asym_pooled - osmo_asym_exited as asym_pooled FROM osmosis.core.fact_liquidity_provider_actions l WHERE action IN ('pool_joined', 'pool_exited') AND tx_status = 'SUCCEEDED' AND (EXISTS ( SELECT 1 FROM stake_claims st WHERE l.liquidity_provider_address = st.delegator_address ) OR EXISTS ( SELECT 1 FROM bonds b WHERE l.liquidity_provider_address = b.pooler AND l.block_timestamp > b.date )) GROUP BY block_timestamp, liquidity_provider_address, tx_id HAVING token_count = 1 AND is_osmo = TRUE ) GROUP BY date, liquidity_provider_address ), swaps AS ( SELECT TRUNC(block_timestamp, 'day') as date, trader, SUM(IFF(from_currency = 'uosmo', from_amount, -to_amount)) / 1e6 as osmo_sold FROM osmosis.core.fact_swaps sw WHERE (EXISTS ( SELECT 1 FROM stake_claims st WHERE sw.trader = st.delegator_address ) OR EXISTS ( SELECT 1 FROM bonds b WHERE sw.trader = b.pooler AND sw.block_timestamp > b.date )) AND (from_currency = 'uosmo' OR to_currency = 'uosmo') AND tx_status = 'SUCCEEDED' GROUP BY date, trader ), stakes AS ( SELECT --TRUNC(block_timestamp, 'hour') as hour, TRUNC(block_timestamp, 'day') as date, delegator_address, SUM(amount) / 1e6 as osmo_staked FROM osmosis.core.fact_staking sw WHERE EXISTS ( SELECT 1 FROM stake_claims st WHERE sw.delegator_address = st.delegator_address ) AND action = 'delegate' AND tx_status = 'SUCCEEDED' GROUP BY /* hour, */date, delegator_address ) SELECT date, SUM(osmo_claimed) as total_claimed, SUM(osmo_sold) as total_sold_by_swap, SUM(osmo_staked) as total_staked, SUM(osmo_pooled) as total_pooled, SUM(osmo_asym_pooled) as total_asym_pooled, total_sold_by_swap + total_asym_pooled / 2 as total_sold, --pcts MEDIAN(pct_restaked) as median_pct_restaked, MEDIAN(pct_pooled) as median_pct_pooled, MEDIAN(pct_both) as median_pct_both, --pct restaked SUM(IFF(pct_restaked < 30, 1, 0)) as "< 30%", SUM(IFF(pct_restaked >= 30 AND pct_restaked < 50, 1, 0)) as "30 ≤ x < 50%", SUM(IFF(pct_restaked >= 50 AND pct_restaked < 80, 1, 0)) as "50 ≤ x < 80%", SUM(IFF(pct_restaked >= 80 AND pct_restaked < 100, 1, 0)) as "80 ≤ x < 100%", SUM(IFF(pct_restaked >= 100, 1, 0)) as "≥ 100%", SUM(IFF(pct_restaked < 30, osmo_sold, 0)) as "< 30% (OSMO)", SUM(IFF(pct_restaked >= 30 AND pct_restaked < 50, osmo_sold, 0)) as "30 ≤ x < 50% (OSMO)", SUM(IFF(pct_restaked >= 50 AND pct_restaked < 80, osmo_sold, 0)) as "50 ≤ x < 80% (OSMO)", SUM(IFF(pct_restaked >= 80 AND pct_restaked < 100, osmo_sold, 0)) as "80 ≤ x < 100% (OSMO)", SUM(IFF(pct_restaked >= 100, osmo_sold, 0)) as "≥ 100% (OSMO)", --pct pooled SUM(IFF(pct_pooled < 30, 1, 0)) as "< 30% Pooled", SUM(IFF(pct_pooled >= 30 AND pct_pooled < 50, 1, 0)) as "30 ≤ x < 50% Pooled", SUM(IFF(pct_pooled >= 50 AND pct_pooled < 80, 1, 0)) as "50 ≤ x < 80% Pooled", SUM(IFF(pct_pooled >= 80 AND pct_pooled < 100, 1, 0)) as "80 ≤ x < 100% Pooled", SUM(IFF(pct_pooled >= 100, 1, 0)) as "≥ 100% Pooled", SUM(IFF(pct_pooled < 30, osmo_sold, 0)) as "< 30% Pooled (OSMO)", SUM(IFF(pct_pooled >= 30 AND pct_pooled < 50, osmo_sold, 0)) as "30 ≤ x < 50% Pooled (OSMO)", SUM(IFF(pct_pooled >= 50 AND pct_pooled < 80, osmo_sold, 0)) as "50 ≤ x < 80% Pooled (OSMO)", SUM(IFF(pct_pooled >= 80 AND pct_pooled < 100, osmo_sold, 0)) as "80 ≤ x < 100% Pooled (OSMO)", SUM(IFF(pct_pooled >= 100, osmo_sold, 0)) as "≥ 100% Pooled (OSMO)", --pct both SUM(IFF(pct_both < 30, 1, 0)) as "< 30% Both", SUM(IFF(pct_both >= 30 AND pct_both < 50, 1, 0)) as "30 ≤ x < 50% Both", SUM(IFF(pct_both >= 50 AND pct_both < 80, 1, 0)) as "50 ≤ x < 80% Both", SUM(IFF(pct_both >= 80 AND pct_both < 100, 1, 0)) as "80 ≤ x < 100 Both", SUM(IFF(pct_both >= 100, 1, 0)) as "≥ 100% Both", SUM(IFF(pct_both < 30, osmo_sold, 0)) as "< 30% Both (OSMO)", SUM(IFF(pct_both >= 30 AND pct_both < 50, osmo_sold, 0)) as "30 ≤ x < 50% Both (OSMO)", SUM(IFF(pct_both >= 50 AND pct_both < 80, osmo_sold, 0)) as "50 ≤ x < 80% Both (OSMO)", SUM(IFF(pct_both >= 80 AND pct_both < 100, osmo_sold, 0)) as "80 ≤ x < 100% Both (OSMO)", SUM(IFF(pct_both >= 100, osmo_sold, 0)) as "≥ 100% Both (OSMO)" FROM ( SELECT s.date, s.trader, NVL(osmo_claimed, 0) as osmo_claimed, NVL(osmo_sold, 0) as osmo_sold, NVL(osmo_staked, 0) as osmo_staked, NVL(amount_osmo_pooled, 0) as osmo_pooled, NVL(amount_asym_pooled, 0) as osmo_asym_pooled, IFF(NVL(osmo_claimed, 0) = 0, null, (NVL(osmo_staked, 0) / osmo_claimed)) * 100 as pct_restaked, IFF(NVL(osmo_claimed, 0) = 0, null, (NVL(amount_osmo_pooled, 0) / osmo_claimed)) * 100 as pct_pooled, IFF(NVL(osmo_claimed, 0) = 0, null, ((NVL(amount_osmo_pooled, 0) + NVL(osmo_staked, 0))/ osmo_claimed)) * 100 as pct_both FROM swaps s LEFT JOIN stake_claims sc ON sc.delegator_address = s.trader AND sc.date = s.date LEFT JOIN stakes sk ON s.trader = sk.delegator_address AND s.date = sk.date --AND sc.hour >= sk.hour LEFT JOIN lp_minted l ON s.trader = l.liquidity_provider_address AND s.date = l.date LEFT JOIN asym_pools_either al ON s.trader = al.liquidity_provider_address AND s.date = al.date ) st GROUP BY date ORDER BY date

Osmosis Rewards Analysis