jackguyAutomatic Staking LP Rewards Analysis 1
    Updated 2023-01-20
    SELECT --*
    sum(CASE WHEN claim_events > withdraw_events then 1 ELSE 0 end) as wallets,
    median(CASE WHEN claim_events > withdraw_events then claim_events - withdraw_events end) as median_amt,
    avg(CASE WHEN claim_events > withdraw_events then claim_events - withdraw_events end) as avg_amt
    FROM (
    SELECT
    DELEGATOR_ADDRESS,
    sum(CASE WHEN action LIKE '%claim%' THEN AMOUNT else 0 END/ power(10,6)) as claim_events,
    sum(CASE WHEN action LIKE '%withdraw%' THEN AMOUNT else 0 END/ power(10,6)) as withdraw_events
    FROM osmosis.core.fact_staking_rewards
    WHERE currency LIKE 'uosmo'
    GROUP BY 1
    )

    LIMIT 100
    Run a query to Download Data