WITH polis_locked AS ( select --* signers[0] as wallet, zeroifnull(round(sum(case when inner_instruction:instructions[1]:programId = 'Lock7kBijGCQLEFAmXcengzXKA88iDNQPriQ7TbgeyG' AND value:parsed:info:authority = signers[0] then value:parsed:info:amount / POW(10,8) when inner_instruction:instructions[0]:programId = 'Lock7kBijGCQLEFAmXcengzXKA88iDNQPriQ7TbgeyG' AND value:parsed:info:authority != signers[0] then value:parsed:info:amount*-1/2 / POW(10,8) end)::integer, 0, 'half_to_even')) AS POLIS_locked from solana.core.fact_events, lateral flatten(input => inner_instruction:instructions) WHERE program_id = 'gateVwTnKyFrE8nxUUgfzoZTPKgJQZUbLsEidpG4Dp2' --AND signers[0] IN ('3oAYkw4SHTTwgQ9fX5WqZYCBAF1MF1MRUjHaay33hCCv', '4F8g6pCBAo3KWEBai8EZbrQ8bVcYG3kxRrAGsDCTDWFL') AND date_trunc('day', block_timestamp) > '2022-07-01' --AND date_trunc('day', block_timestamp) < '2023-01-01' group by 1 order by 2 desc ), polis_claimed AS ( select --* case when value:programId = 'TokenkegQfeZyiNwAJbNbGKPFXCWuBvf9Ss623VQ5DA' AND value:parsed:info:authority = '2Jo8wqijXveVjra1DAknfoH1imS7XaJqLA1eQF5hzRUE' AND value:parsed:info:amount::decimal(20,2) > 0 then block_timestamp end AS date, case when value:programId = 'TokenkegQfeZyiNwAJbNbGKPFXCWuBvf9Ss623VQ5DA' AND value:parsed:info:authority = '2Jo8wqijXveVjra1DAknfoH1imS7XaJqLA1eQF5hzRUE' AND value:parsed:info:amount::decimal(20,2) > 0 then signers[0] end as wallet, case when value:programId = 'TokenkegQfeZyiNwAJbNbGKPFXCWuBvf9Ss623VQ5DA' AND value:parsed:info:authority = '2Jo8wqijXveVjra1DAknfoH1imS7XaJqLA1eQF5hzRUE' AND value:parsed:info:amount::decimal(20,2) > 0 then value:parsed:info:amount / POW(10,8) end::decimal(20,2) AS POLIS_rewards_claimed, row_number() OVER (partition by wallet order by date desc) as row_number from solana.core.fact_events, lateral flatten(input => inner_instruction:instructions) WHERE program_id = 'gateVwTnKyFrE8nxUUgfzoZTPKgJQZUbLsEidpG4Dp2' --AND signers[0] IN ('3oAYkw4SHTTwgQ9fX5WqZYCBAF1MF1MRUjHaay33hCCv', '4F8g6pCBAo3KWEBai8EZbrQ8bVcYG3kxRrAGsDCTDWFL') AND date_trunc('day', block_timestamp) > '2022-07-01' --AND date_trunc('day', block_timestamp) < '2023-01-01' group by 1,2,3 order by 1 desc ), polis_locker AS ( SELECT -- flipsidecrypto has problems with mulitplying large numbers, so I had to divide total_pvp by 1000000 389.211047 as total_pvp, 50774 as daily_polis, ----------------------------------------------------- round(daily_polis/total_pvp*0.08*365*100/1000000, 2, 'HALF_TO_EVEN') as apr_2w, round(daily_polis/total_pvp*0.16*365*100/1000000, 2, 'HALF_TO_EVEN') as apr_1m, round(daily_polis/total_pvp*0.5*365*100/1000000, 2, 'HALF_TO_EVEN') as apr_3m, round(daily_polis/total_pvp*1*365*100/1000000, 2, 'HALF_TO_EVEN') as apr_6m, round(daily_polis/total_pvp*1.5*365*100/1000000, 2, 'HALF_TO_EVEN') as apr_9m, round(daily_polis/total_pvp*2*365*100/1000000, 2, 'HALF_TO_EVEN') as apr_1y, round(daily_polis/total_pvp*4*365*100/1000000, 2, 'HALF_TO_EVEN') as apr_2y, round(daily_polis/total_pvp*6*365*100/1000000, 2, 'HALF_TO_EVEN') as apr_3y, round(daily_polis/total_pvp*8*365*100/1000000, 2, 'HALF_TO_EVEN') as apr_4y, round(daily_polis/total_pvp*10*365*100/1000000, 2, 'HALF_TO_EVEN') as apr_5y ----------------------------------------------------- ), apr AS ( SELECT y.date as date, x.wallet as wallet, polis_rewards_claimed as polis_claimed, polis_locked, /*case when last_value(POLIS_rewards_claimed) OVER (ORDER BY y.date ROWS BETWEEN 10 PRECEDING AND CURRENT ROW) > 0 then last_value(POLIS_rewards_claimed) OVER (ORDER BY y.date ROWS BETWEEN 10 PRECEDING AND CURRENT ROW) ELSE last_value(POLIS_rewards_claimed) OVER (ORDER BY y.date ROWS BETWEEN 10 PRECEDING AND 1 PRECEDING) end as polis_claimed,*/ case when last_value(POLIS_rewards_claimed) OVER (PARTITION BY y.wallet ORDER BY y.date ROWS BETWEEN 10 PRECEDING AND CURRENT ROW) > 0 then TIMESTAMPDIFF (minute, last_value(y.date) OVER (PARTITION BY y.wallet ORDER BY y.date ROWS BETWEEN 10 PRECEDING AND 1 PRECEDING), last_value(y.date) OVER (PARTITION BY y.wallet ORDER BY y.date ROWS BETWEEN 10 PRECEDING AND CURRENT ROW)) ELSE TIMESTAMPDIFF (minute, last_value(y.date) OVER (PARTITION BY y.wallet ORDER BY y.date ROWS BETWEEN 10 PRECEDING AND 2 PRECEDING), last_value(y.date) OVER (PARTITION BY y.wallet ORDER BY y.date ROWS BETWEEN 10 PRECEDING AND 1 PRECEDING)) end as days, /*case when y.row_number = 1 then y.date end as date1, case when y.row_number = 2 then y.date end as date2, TIMESTAMPDIFF (minute, date1, date2) as days, */ case when polis_locked > 0 AND days > 0 AND polis_claimed / polis_locked * 365 / days * 60 * 24 * 100 < apr_2w then polis_claimed / polis_locked * 365 / days * 60 * 24 * 100 / apr_2w * 0.08 when polis_locked > 0 AND days > 0 AND polis_claimed / polis_locked * 365 / days * 60 * 24 * 100 < apr_1m then polis_claimed / polis_locked * 365 / days * 60 * 24 * 100 / apr_1m * 0.16 when polis_locked > 0 AND days > 0 AND polis_claimed / polis_locked * 365 / days * 60 * 24 * 100 < apr_3m then polis_claimed / polis_locked * 365 / days * 60 * 24 * 100 / apr_3m * 0.5 when polis_locked > 0 AND days > 0 AND polis_claimed / polis_locked * 365 / days * 60 * 24 * 100 < apr_6m then polis_claimed / polis_locked * 365 / days * 60 * 24 * 100 / apr_6m * 1 when polis_locked > 0 AND days > 0 AND polis_claimed / polis_locked * 365 / days * 60 * 24 * 100 < apr_9m then polis_claimed / polis_locked * 365 / days * 60 * 24 * 100 / apr_9m * 1.5 when polis_locked > 0 AND days > 0 AND polis_claimed / polis_locked * 365 / days * 60 * 24 * 100 < apr_1y then polis_claimed / polis_locked * 365 / days * 60 * 24 * 100 / apr_1y * 2 when polis_locked > 0 AND days > 0 AND polis_claimed / polis_locked * 365 / days * 60 * 24 * 100 < apr_2y then polis_claimed / polis_locked * 365 / days * 60 * 24 * 100 / apr_2y * 4 when polis_locked > 0 AND days > 0 AND polis_claimed / polis_locked * 365 / days * 60 * 24 * 100 < apr_3y then polis_claimed / polis_locked * 365 / days * 60 * 24 * 100 / apr_3y * 6 when polis_locked > 0 AND days > 0 AND polis_claimed / polis_locked * 365 / days * 60 * 24 * 100 < apr_4y then polis_claimed / polis_locked * 365 / days * 60 * 24 * 100 / apr_4y * 8 when polis_locked > 0 AND days > 0 AND polis_claimed / polis_locked * 365 / days * 60 * 24 * 100 < apr_5y then polis_claimed / polis_locked * 365 / days * 60 * 24 * 100 / apr_5y * 10 when polis_locked > 0 AND days > 0 AND polis_claimed / polis_locked * 365 / days * 60 * 24 * 100 > apr_5y then 10 end as pvp_multi, polis_locked * pvp_multi as PVP from polis_locked x left outer join polis_claimed y on x.wallet = y.wallet left outer join polis_locker z --QUALIFY ROW_NUMBER() OVER (PARTITION BY X.WALLET ORDER BY Y.date desc) = 1 --group by 1,2,3,4,7,8 order by 2,1 desc ) --SELECT * FROM apr --order by date desc SELECT wallet, polis_locked, round(PVP, 0) as est_PVP --polis_locked * pvp_multi as PVP FROM apr QUALIFY ROW_NUMBER() OVER (PARTITION BY WALLET ORDER BY date desc) = 1 order by 2 desc

AEP dashboard