WITH polis_locker AS ( SELECT -- flipsidecrypto has problems with mulitplying large numbers, so I had to divide total_pvp by 1000000 448.624859 as total_pvp, 50336 as daily_polis, 14527.130447 as total_atlas, 4268.5024 as daily_atlas, ----------------------------------------------------- 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 ----------------------------------------------------- ), polis_locked AS ( select --* 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 date_trunc('day', block_timestamp) > '2022-07-01' --AND date_trunc('day', block_timestamp) < '2023-01-01' AND (signers = ['{{Wallet1}}'] OR signers = ['{{Wallet2}}'] OR signers = ['{{Wallet3}}']) ), 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 date_trunc('day', block_timestamp) > '2022-07-01' --AND date_trunc('day', block_timestamp) < '2023-01-01' AND (signers = ['{{Wallet1}}'] OR signers = ['{{Wallet2}}'] OR signers = ['{{Wallet3}}']) group by 1,2,3 order by 1 desc ), apr AS ( SELECT y.date as date, polis_rewards_claimed as polis_claimed, polis_locked, 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 polis_locked > 0 AND days > 0 AND polis_claimed / polis_locked * 365 / days * 60 * 24 * 100 < apr_2w then '< 2 weeks' when polis_locked > 0 AND days > 0 AND polis_claimed / polis_locked * 365 / days * 60 * 24 * 100 < apr_1m then '> 2 weeks & < 1 month' when polis_locked > 0 AND days > 0 AND polis_claimed / polis_locked * 365 / days * 60 * 24 * 100 < apr_3m then '> 1 month & < 3 months' when polis_locked > 0 AND days > 0 AND polis_claimed / polis_locked * 365 / days * 60 * 24 * 100 < apr_6m then '> 3 months & < 6 months' when polis_locked > 0 AND days > 0 AND polis_claimed / polis_locked * 365 / days * 60 * 24 * 100 < apr_9m then '> 6 months & < 9 months' when polis_locked > 0 AND days > 0 AND polis_claimed / polis_locked * 365 / days * 60 * 24 * 100 < apr_1y then '> 9 months & < 1 year' when polis_locked > 0 AND days > 0 AND polis_claimed / polis_locked * 365 / days * 60 * 24 * 100 < apr_2y then '> 1 year & < 2 years' when polis_locked > 0 AND days > 0 AND polis_claimed / polis_locked * 365 / days * 60 * 24 * 100 < apr_3y then '> 2 years & < 3 years' when polis_locked > 0 AND days > 0 AND polis_claimed / polis_locked * 365 / days * 60 * 24 * 100 < apr_4y then '> 3 years & < 4 years' when polis_locked > 0 AND days > 0 AND polis_claimed / polis_locked * 365 / days * 60 * 24 * 100 < apr_5y then '> 4 years & < 5 years' when polis_locked > 0 AND days > 0 AND polis_claimed / polis_locked * 365 / days * 60 * 24 * 100 > apr_5y then '= 5 years' end as polis_locked_time, row_number() over (ORDER BY y.date) as row_num from polis_locked x left outer join polis_claimed y left outer join polis_locker z --order by 2,1 desc ), atlas_locked AS ( select --* zeroifnull(round(sum(case when value:parsed:info:authority = signers[0] then value:parsed:info:tokenAmount:uiAmount when value:parsed:info:authority != signers[0] then value:parsed:info:tokenAmount:uiAmount*-1 end)::integer, 0, 'half_to_even')) AS ATLAS_locked from solana.core.fact_events, lateral flatten(input => inner_instruction:instructions) WHERE program_id = 'ATLocKpzDbTokxgvnLew3d7drZkEzLzDpzwgrgWKDbmc' AND value:parsed:info:mint = 'ATLASXmbPQxBUYbxPsV97usA3fPQYEqzQBUHgiFCUsXx' AND date_trunc('day', block_timestamp) > '2022-07-01' --AND date_trunc('day', block_timestamp) < '2023-01-01' AND (signers = ['{{Wallet1}}'] OR signers = ['{{Wallet2}}'] OR signers = ['{{Wallet3}}']) ), atlas_price AS ( select --* --DATE(block_timestamp) AS date, sum(case when (swap_from_mint = 'ATLASXmbPQxBUYbxPsV97usA3fPQYEqzQBUHgiFCUsXx' AND swap_to_mint = 'EPjFWdd5AufqSSqeM2qN1xzybapC8G4wEGGkZwyTDt1v') then swap_from_amount when (swap_to_mint = 'ATLASXmbPQxBUYbxPsV97usA3fPQYEqzQBUHgiFCUsXx' AND swap_from_mint = 'EPjFWdd5AufqSSqeM2qN1xzybapC8G4wEGGkZwyTDt1v') then swap_to_amount end) as ATLAS, sum(case when (swap_from_mint = 'ATLASXmbPQxBUYbxPsV97usA3fPQYEqzQBUHgiFCUsXx' AND swap_to_mint = 'EPjFWdd5AufqSSqeM2qN1xzybapC8G4wEGGkZwyTDt1v') then swap_to_amount when (swap_to_mint = 'ATLASXmbPQxBUYbxPsV97usA3fPQYEqzQBUHgiFCUsXx' AND swap_from_mint = 'EPjFWdd5AufqSSqeM2qN1xzybapC8G4wEGGkZwyTDt1v') then swap_from_amount end) as USDC, USDC / ATLAS as atlas_price from solana.defi.fact_swaps WHERE succeeded = 'true' AND program_id IN ( 'JUP6LkbZbjS1jKKwapdHNy74zcZ3tLUZoi5QNyVTaV4', 'JUP5cHjnnCx2DppVsufsLrXs8EBZeEZzGtEK9Gdz6ow', 'JUP4Fb2cqiRUcaTHdrPC8h2gNsA2ETXiPDD33WcGuJB', 'JUP3c2Uh3WA4Ng34tw6kPd2G4C5BB21Xo36Je1s32Ph', 'JUP2jxvXaqu7NQY1GmNF4m1vodw12LVXYxbFL2uJvfo') AND block_timestamp > dateadd(hour, -24, current_date) --group by 1 ), polis_price AS ( select --* --DATE(block_timestamp) AS date, sum(case when (swap_from_mint = 'poLisWXnNRwC6oBu1vHiuKQzFjGL4XDSu4g9qjz9qVk' AND swap_to_mint = 'EPjFWdd5AufqSSqeM2qN1xzybapC8G4wEGGkZwyTDt1v') then swap_from_amount when (swap_to_mint = 'poLisWXnNRwC6oBu1vHiuKQzFjGL4XDSu4g9qjz9qVk' AND swap_from_mint = 'EPjFWdd5AufqSSqeM2qN1xzybapC8G4wEGGkZwyTDt1v') then swap_to_amount end) as POLIS, sum(case when (swap_from_mint = 'poLisWXnNRwC6oBu1vHiuKQzFjGL4XDSu4g9qjz9qVk' AND swap_to_mint = 'EPjFWdd5AufqSSqeM2qN1xzybapC8G4wEGGkZwyTDt1v') then swap_to_amount when (swap_to_mint = 'poLisWXnNRwC6oBu1vHiuKQzFjGL4XDSu4g9qjz9qVk' AND swap_from_mint = 'EPjFWdd5AufqSSqeM2qN1xzybapC8G4wEGGkZwyTDt1v') then swap_from_amount end) as USDC, USDC / POLIS as polis_price from solana.defi.fact_swaps WHERE succeeded = 'true' AND program_id IN ( 'JUP6LkbZbjS1jKKwapdHNy74zcZ3tLUZoi5QNyVTaV4', 'JUP5cHjnnCx2DppVsufsLrXs8EBZeEZzGtEK9Gdz6ow', 'JUP4Fb2cqiRUcaTHdrPC8h2gNsA2ETXiPDD33WcGuJB', 'JUP3c2Uh3WA4Ng34tw6kPd2G4C5BB21Xo36Je1s32Ph', 'JUP2jxvXaqu7NQY1GmNF4m1vodw12LVXYxbFL2uJvfo') AND block_timestamp > dateadd(hour, -24, current_date) --group by 1 ), donut (name, number, value_in_usd) as ( SELECT 'ATLAS', round(atlas_locked,0), round(atlas_locked * atlas_price,0) from atlas_locked, atlas_price UNION ALL SELECT 'POLIS', round(polis_locked,0), round(polis_locked * polis_price,0) from polis_locked, polis_price ) SELECT * FROM donut /* SELECT polis_locked, --polis_locked_time, atlas_locked, polis_locked * polis_price as polis_usd, atlas_locked * atlas_price as atlas_usd from atlas_locked, apr, atlas_price, polis_price QUALIFY ROW_NUMBER() OVER (ORDER BY date desc NULLS LAST) = 1 */

STAR ATLAS - My Star Atlas