WITH polis_locker AS ( SELECT -- flipsidecrypto has problems with mulitplying large numbers, so I had to divide total_pvp by 1000000 448.496282 as total_pvp, 50381 as daily_polis, 14447.386692 as total_atlas, 4271.8445 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 ----------------------------------------------------- ), 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 ('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 ('JUP3c2Uh3WA4Ng34tw6kPd2G4C5BB21Xo36Je1s32Ph', 'JUP2jxvXaqu7NQY1GmNF4m1vodw12LVXYxbFL2uJvfo') AND block_timestamp > dateadd(hour, -24, current_date) --group by 1 ), ships_in as ( select signers[0] as wallet, ----------------------------------------------------------------------------------------------------- sum(case when value:parsed:info:mint = 'Ev3xUhc1Leqi4qR2E5VoG9pcxCvHHmnAaSRVPg485xAT' then value:parsed:info:tokenAmount:uiAmount ELSE 0 end) AS OPALJ_in, sum(case when value:parsed:info:mint = '267DbhCypYzvTqv72ZG5UKHeFu56qXFsuoz3rw832eC5' then value:parsed:info:tokenAmount:uiAmount ELSE 0 end) AS PXFIVE_in, sum(case when value:parsed:info:mint = '9czEqEZ4EkRt7N3HWDcw9qqwys3xRRjGdbn8Jhk8Khwj' then value:parsed:info:tokenAmount:uiAmount ELSE 0 end) AS VZUSOP_in, sum(case when value:parsed:info:mint = 'AkNbg12E9PatjkiAWJ3tAbM479gtcoA1gi6Joa925WKi' then value:parsed:info:tokenAmount:uiAmount ELSE 0 end) AS CALCH_in, sum(case when value:parsed:info:mint = 'CWxNX9sTexuqvQefqskhP9f6AP5C8hq2VNkicRseqAT5' then value:parsed:info:tokenAmount:uiAmount ELSE 0 end) AS OGKATP_in, sum(case when value:parsed:info:mint = '2iMhgB4pbdKvwJHVyitpvX5z1NBNypFonUgaSAt9dtDt' then value:parsed:info:tokenAmount:uiAmount ELSE 0 end) AS PXFOUR_in, sum(case when value:parsed:info:mint = '9ABNesWj7NVdkDgko7UjVaDp5pTh8a6wfXHLWz3bZM6W' then value:parsed:info:tokenAmount:uiAmount ELSE 0 end) AS OPALJJ_in, sum(case when value:parsed:info:mint = '7V9C2XUQgCb31n7hGKqKGu4ENcvqXhJLJzU77CAQtXhw' then value:parsed:info:tokenAmount:uiAmount ELSE 0 end) AS FBLBPL_in, sum(case when value:parsed:info:mint = 'DdpXnnYsyUQgJby8TDHbmPwkKyGF4U6bXwCXTQZsrfKP' then value:parsed:info:tokenAmount:uiAmount ELSE 0 end) AS CALG_in, sum(case when value:parsed:info:mint = '9MvZS3TVfv4DZL9W2pT12po384aBHf7wi89KXQ9Z7uwW' then value:parsed:info:tokenAmount:uiAmount ELSE 0 end) AS PFFOUR_in, sum(case when value:parsed:info:mint = 'FpwV1Da6BZJnYPr1JSLUm14UwBmZHA7J5WLY4TXgbde8' then value:parsed:info:tokenAmount:uiAmount ELSE 0 end) AS FBLEGR_in, sum(case when value:parsed:info:mint = 'HzBx8PP86pyPrrboTHqPYWhxnEB5vXDHDBP8femWfPTS' then value:parsed:info:tokenAmount:uiAmount ELSE 0 end) AS OM_in, sum(case when value:parsed:info:mint = '2bCgKTo11QayWBy6QryHZMqZL2ZgWd5LEAZKiTGQi4g7' then value:parsed:info:tokenAmount:uiAmount ELSE 0 end) AS PREIGHT_in, sum(case when value:parsed:info:mint = 'H2jHqvXA2oxSpEp6dKkpK7WeszQEdFW5n25mNfrJFAc1' then value:parsed:info:tokenAmount:uiAmount ELSE 0 end) AS VZUSAM_in, sum(case when value:parsed:info:mint = '5f1jUARhtSypVA4uTpgpLp76WYGdB2dGr8zMbh4WjYRf' then value:parsed:info:tokenAmount:uiAmount ELSE 0 end) AS PCNINE_in, sum(case when value:parsed:info:mint = '9ifQ16N5DdUFoejCwsgR73ihUwadAe3srCo9HhQe2zL2' then value:parsed:info:tokenAmount:uiAmount ELSE 0 end) AS PCELEVEN_in, sum(case when value:parsed:info:mint = 'DsJHgpnNovjJ981QJJnqMggexAekNawbSavfV1QuTpis' then value:parsed:info:tokenAmount:uiAmount ELSE 0 end) AS CHI_in, sum(case when value:parsed:info:mint = 'HJBmBYyGR8z1oajAM4jiK46uobuxeJoKDYpFwzWHBvhb' then value:parsed:info:tokenAmount:uiAmount ELSE 0 end) AS OGKAJA_in, sum(case when value:parsed:info:mint = '4txpjHspP4usEsQTr3AcrpyHVjN4fi3d4taM6cmKJnd1' then value:parsed:info:tokenAmount:uiAmount ELSE 0 end) AS CALEV_in, sum(case when value:parsed:info:mint = 'HsdbLvZrEgN2ZhsrZs5ag4F2FNFCHjjuXPfbVAhkeJBZ' then value:parsed:info:tokenAmount:uiAmount ELSE 0 end) AS TUFAFE_in, sum(case when value:parsed:info:mint = '6SqLuwHNRC1qjo9KATLKJLszFHMWyYaNxDXraCEUtfdR' then value:parsed:info:tokenAmount:uiAmount ELSE 0 end) AS FBLBEA_in, sum(case when value:parsed:info:mint = '8RveLFEyteyL1vbCKPQJxjf3JT1ACyrzs46TXbJStrHG' then value:parsed:info:tokenAmount:uiAmount ELSE 0 end) AS PXSIX_in, sum(case when value:parsed:info:mint = '7M6RHgPiHXiZAin5ManH63cLYGt3miQ54KaGynUQoERS' then value:parsed:info:tokenAmount:uiAmount ELSE 0 end) AS FBLEBO_in, sum(case when value:parsed:info:mint = 'HqPN13pLUVJRiuGSsKjfWZvGKAagK98PshuKu51bnG4E' then value:parsed:info:tokenAmount:uiAmount ELSE 0 end) AS FBLETR_in, sum(case when value:parsed:info:mint = 'Fw8PqtznYtg4swMk7Yjj89Tsj23u5CJLfW5Bk8ro4G1s' then value:parsed:info:tokenAmount:uiAmount ELSE 0 end) AS FBLAIR_in, sum(case when value:parsed:info:mint = 'FMHHwUB6amLWYhWxtiZHC2g5azy9usPTLMq46N3HEgFU' then value:parsed:info:tokenAmount:uiAmount ELSE 0 end) AS OGKAMK_in, sum(case when value:parsed:info:mint = '9zrgra3XQkZPt8XNs4fowbqmj7B8bBx76aEmsKSnm9BW' then value:parsed:info:tokenAmount:uiAmount ELSE 0 end) AS FBLEUN_in, sum(case when value:parsed:info:mint = 'EbLBLN44BVLjifLNBbchXFr8QjEkAGYENKuNEaDuyVPL' then value:parsed:info:tokenAmount:uiAmount ELSE 0 end) AS ARC_in, sum(case when value:parsed:info:mint = '7Xs3yt9eJPuEexZrKSGVbQMXHwWUKHGeDZnM4ZksZmyS' then value:parsed:info:tokenAmount:uiAmount ELSE 0 end) AS FBLLOW_in, sum(case when value:parsed:info:mint = 'FTk1E5UoWkiZEUttCWSYYaVokxWNNp3yJ42HbNDCAkdt' then value:parsed:info:tokenAmount:uiAmount ELSE 0 end) AS THRILL_in, sum(case when value:parsed:info:mint = 'FFkPvwLDYuKDW9eAAr5UNfuX3U9PcTGeSk7gqNX7EpNc' then value:parsed:info:tokenAmount:uiAmount ELSE 0 end) AS VZUSBA_in, sum(case when value:parsed:info:mint = '4gR3ChfdQxR4BTbgeWSdf6b8kD8Ysu6WBAQqtJ9oLgbF' then value:parsed:info:tokenAmount:uiAmount ELSE 0 end) AS CALMED_in, sum(case when value:parsed:info:mint = '8pPDsMNcz4m8jaajFMFXHGcvaeVeiQhcenvSD6a4XNyq' then value:parsed:info:tokenAmount:uiAmount ELSE 0 end) AS OPALBB_in, sum(case when value:parsed:info:mint = '2XYd22LSFGxN7kWgoEeaXVZqgrsPeQLHLEgNhnS12Mny' then value:parsed:info:tokenAmount:uiAmount ELSE 0 end) AS CALATS_in, sum(case when value:parsed:info:mint = 'Fys8J53cquYsg5zYfeZStVGNwM9FopFw8QFkiE9CCR1J' then value:parsed:info:tokenAmount:uiAmount ELSE 0 end) AS PR6_in, sum(case when value:parsed:info:mint = '6HzZJwrcuBBmrE7SLDfxheZGAD3NYJ531C9JsNesL9BP' then value:parsed:info:tokenAmount:uiAmount ELSE 0 end) AS HEART_in, sum(case when value:parsed:info:mint = 'J8Q6jYsrhhaeczyPBo9xzVyy4GpfCnJwj14LJn2HnuKp' then value:parsed:info:tokenAmount:uiAmount ELSE 0 end) AS OGKATU_in, sum(case when value:parsed:info:mint = '6Zj61HuX1E7SCUCf9WsKXw1jdJCobAwK4RSjZvbv35tM' then value:parsed:info:tokenAmount:uiAmount ELSE 0 end) AS FBLMAM_in, sum(case when value:parsed:info:mint = 'HjFijcGWKgfDwGpFX2rqFwEU9jtEgFuRQAJe1ERXFsA3' then value:parsed:info:tokenAmount:uiAmount ELSE 0 end) AS VZUSSO_in, sum(case when value:parsed:info:mint = '4ns3shP4WunCtJbr2HFu31RjjxSJxDymEFcBZxiHr11s' then value:parsed:info:tokenAmount:uiAmount ELSE 0 end) AS FBLBTA_in, sum(case when value:parsed:info:mint = '4b4mhSySBcryzBPamw8v4xeneFRA6xTUA4JA99w6vqey' then value:parsed:info:tokenAmount:uiAmount ELSE 0 end) AS OGKASP_in, sum(case when value:parsed:info:mint = 'H3cgBXWpUiNsYjUWS7cNR5Bmehh7k5CgpJccc5wSfRbJ' then value:parsed:info:tokenAmount:uiAmount ELSE 0 end) AS PD9_in, sum(case when value:parsed:info:mint = 'DB8CSxoakPRtXhHcc2cA3iETWfGaYY6zE2T8huJTE2Nw' then value:parsed:info:tokenAmount:uiAmount ELSE 0 end) AS STAND_in, sum(case when value:parsed:info:mint = '2BMTgfgapuqYBwSRGu6HCafwDGRVTxMkQVhYrNWQGTWH' then value:parsed:info:tokenAmount:uiAmount ELSE 0 end) AS SUPER_in, sum(case when value:parsed:info:mint = '56nh4FvMJnSpkWJcmXc1nJtYhSQXuqb7nVMGALr31RVc' then value:parsed:info:tokenAmount:uiAmount ELSE 0 end) AS TITAN_in, sum(case when value:parsed:info:mint = 'F3HitKsp52UPqBMEWSeTFqrGgnfYbS9DMrJCz9dM3w6D' then value:parsed:info:tokenAmount:uiAmount ELSE 0 end) AS CALSCD_in, sum(case when value:parsed:info:mint = 'GxpbUDxYYvxiUejHcAMzeV2rzdHf6KZZvT86ACrpFgXa' then value:parsed:info:tokenAmount:uiAmount ELSE 0 end) AS CALMAX_in, sum(case when value:parsed:info:mint = '7SUoWHWWJCxCe5g9XqZkCRufGHXRV8nauuz69HPjuewr' then value:parsed:info:tokenAmount:uiAmount ELSE 0 end) AS OGKANR_in, sum(case when value:parsed:info:mint = 'DTbNmLWfu1pm4AuXRKYTApnDNfxMz73VET7nW5wizG5t' then value:parsed:info:tokenAmount:uiAmount ELSE 0 end) AS IMP1_in, sum(case when value:parsed:info:mint = 'ARNZXUQoBKx3JCX3UJB4aitSnvcjMMphN9YVDFy1PdKq' then value:parsed:info:tokenAmount:uiAmount ELSE 0 end) AS IMP2_in, sum(case when value:parsed:info:mint = 'GmVKV9W3qZcERxk7hjqwRDcn9Kgtz3XDi7KfFLdGqyaW' then value:parsed:info:tokenAmount:uiAmount ELSE 0 end) AS IMP3_in from solana.core.fact_events, lateral flatten(input => inner_instruction:instructions) WHERE program_id = 'FLEET1qqzpexyaDpqb2DGsSzE2sDCizewCg9WjrA6DBW' AND succeeded = 'true' --AND tx_id = 'SK9XbzbXVy7jgsEPp8Y8bzV8Y69CWnCJ5NJW2XHhAwGZnBhs5ABNtwMRXKqNfWPKN2eYS9fLewhWz3CmcWeddAb' AND value:parsed:info:authority = signers[0]::string --AND signers[0] = 'Cbhe953AS3FAMW6kjVG9J2saEKM46W3yiP2a3Ld8wVXN' --AND value:parsed:info:mint IN ('9ifQ16N5DdUFoejCwsgR73ihUwadAe3srCo9HhQe2zL2') --AND value:parsed:info:mint NOT IN ('ammoK8AkX2wnebQb35cDAZtTkvsXQbi82cGeTnUvvfK', 'foodQJAztMzX1DKpLaiounNe2BDMds5RNuPC6jsNrDG', 'fueL3hBZjLLLJHiFH9cqZoozTG3XQZ53diwFPwbzNim', 'tooLsNYLiVqzg8o4m3L2Uetbn62mvMWRqkog6PQeYKL') AND block_timestamp > '2021-12-16' --AND block_timestamp < '2023-06-01' group by 1 ), ships_out as ( select signers[0] as wallet, ----------------------------------------------------------------------------------------------------- sum(case when value:parsed:info:mint = 'Ev3xUhc1Leqi4qR2E5VoG9pcxCvHHmnAaSRVPg485xAT' then value:parsed:info:tokenAmount:uiAmount ELSE 0 end) AS OPALJ_out, sum(case when value:parsed:info:mint = '267DbhCypYzvTqv72ZG5UKHeFu56qXFsuoz3rw832eC5' then value:parsed:info:tokenAmount:uiAmount ELSE 0 end) AS PXFIVE_out, sum(case when value:parsed:info:mint = '9czEqEZ4EkRt7N3HWDcw9qqwys3xRRjGdbn8Jhk8Khwj' then value:parsed:info:tokenAmount:uiAmount ELSE 0 end) AS VZUSOP_out, sum(case when value:parsed:info:mint = 'AkNbg12E9PatjkiAWJ3tAbM479gtcoA1gi6Joa925WKi' then value:parsed:info:tokenAmount:uiAmount ELSE 0 end) AS CALCH_out, sum(case when value:parsed:info:mint = 'CWxNX9sTexuqvQefqskhP9f6AP5C8hq2VNkicRseqAT5' then value:parsed:info:tokenAmount:uiAmount ELSE 0 end) AS OGKATP_out, sum(case when value:parsed:info:mint = '2iMhgB4pbdKvwJHVyitpvX5z1NBNypFonUgaSAt9dtDt' then value:parsed:info:tokenAmount:uiAmount ELSE 0 end) AS PXFOUR_out, sum(case when value:parsed:info:mint = '9ABNesWj7NVdkDgko7UjVaDp5pTh8a6wfXHLWz3bZM6W' then value:parsed:info:tokenAmount:uiAmount ELSE 0 end) AS OPALJJ_out, sum(case when value:parsed:info:mint = '7V9C2XUQgCb31n7hGKqKGu4ENcvqXhJLJzU77CAQtXhw' then value:parsed:info:tokenAmount:uiAmount ELSE 0 end) AS FBLBPL_out, sum(case when value:parsed:info:mint = 'DdpXnnYsyUQgJby8TDHbmPwkKyGF4U6bXwCXTQZsrfKP' then value:parsed:info:tokenAmount:uiAmount ELSE 0 end) AS CALG_out, sum(case when value:parsed:info:mint = '9MvZS3TVfv4DZL9W2pT12po384aBHf7wi89KXQ9Z7uwW' then value:parsed:info:tokenAmount:uiAmount ELSE 0 end) AS PFFOUR_out, sum(case when value:parsed:info:mint = 'FpwV1Da6BZJnYPr1JSLUm14UwBmZHA7J5WLY4TXgbde8' then value:parsed:info:tokenAmount:uiAmount ELSE 0 end) AS FBLEGR_out, sum(case when value:parsed:info:mint = 'HzBx8PP86pyPrrboTHqPYWhxnEB5vXDHDBP8femWfPTS' then value:parsed:info:tokenAmount:uiAmount ELSE 0 end) AS OM_out, sum(case when value:parsed:info:mint = '2bCgKTo11QayWBy6QryHZMqZL2ZgWd5LEAZKiTGQi4g7' then value:parsed:info:tokenAmount:uiAmount ELSE 0 end) AS PREIGHT_out, sum(case when value:parsed:info:mint = 'H2jHqvXA2oxSpEp6dKkpK7WeszQEdFW5n25mNfrJFAc1' then value:parsed:info:tokenAmount:uiAmount ELSE 0 end) AS VZUSAM_out, sum(case when value:parsed:info:mint = '5f1jUARhtSypVA4uTpgpLp76WYGdB2dGr8zMbh4WjYRf' then value:parsed:info:tokenAmount:uiAmount ELSE 0 end) AS PCNINE_out, sum(case when value:parsed:info:mint = '9ifQ16N5DdUFoejCwsgR73ihUwadAe3srCo9HhQe2zL2' then value:parsed:info:tokenAmount:uiAmount ELSE 0 end) AS PCELEVEN_out, sum(case when value:parsed:info:mint = 'DsJHgpnNovjJ981QJJnqMggexAekNawbSavfV1QuTpis' then value:parsed:info:tokenAmount:uiAmount ELSE 0 end) AS CHI_out, sum(case when value:parsed:info:mint = 'HJBmBYyGR8z1oajAM4jiK46uobuxeJoKDYpFwzWHBvhb' then value:parsed:info:tokenAmount:uiAmount ELSE 0 end) AS OGKAJA_out, sum(case when value:parsed:info:mint = '4txpjHspP4usEsQTr3AcrpyHVjN4fi3d4taM6cmKJnd1' then value:parsed:info:tokenAmount:uiAmount ELSE 0 end) AS CALEV_out, sum(case when value:parsed:info:mint = 'HsdbLvZrEgN2ZhsrZs5ag4F2FNFCHjjuXPfbVAhkeJBZ' then value:parsed:info:tokenAmount:uiAmount ELSE 0 end) AS TUFAFE_out, sum(case when value:parsed:info:mint = '6SqLuwHNRC1qjo9KATLKJLszFHMWyYaNxDXraCEUtfdR' then value:parsed:info:tokenAmount:uiAmount ELSE 0 end) AS FBLBEA_out, sum(case when value:parsed:info:mint = '8RveLFEyteyL1vbCKPQJxjf3JT1ACyrzs46TXbJStrHG' then value:parsed:info:tokenAmount:uiAmount ELSE 0 end) AS PXSIX_out, sum(case when value:parsed:info:mint = '7M6RHgPiHXiZAin5ManH63cLYGt3miQ54KaGynUQoERS' then value:parsed:info:tokenAmount:uiAmount ELSE 0 end) AS FBLEBO_out, sum(case when value:parsed:info:mint = 'HqPN13pLUVJRiuGSsKjfWZvGKAagK98PshuKu51bnG4E' then value:parsed:info:tokenAmount:uiAmount ELSE 0 end) AS FBLETR_out, sum(case when value:parsed:info:mint = 'Fw8PqtznYtg4swMk7Yjj89Tsj23u5CJLfW5Bk8ro4G1s' then value:parsed:info:tokenAmount:uiAmount ELSE 0 end) AS FBLAIR_out, sum(case when value:parsed:info:mint = 'FMHHwUB6amLWYhWxtiZHC2g5azy9usPTLMq46N3HEgFU' then value:parsed:info:tokenAmount:uiAmount ELSE 0 end) AS OGKAMK_out, sum(case when value:parsed:info:mint = '9zrgra3XQkZPt8XNs4fowbqmj7B8bBx76aEmsKSnm9BW' then value:parsed:info:tokenAmount:uiAmount ELSE 0 end) AS FBLEUN_out, sum(case when value:parsed:info:mint = 'EbLBLN44BVLjifLNBbchXFr8QjEkAGYENKuNEaDuyVPL' then value:parsed:info:tokenAmount:uiAmount ELSE 0 end) AS ARC_out, sum(case when value:parsed:info:mint = '7Xs3yt9eJPuEexZrKSGVbQMXHwWUKHGeDZnM4ZksZmyS' then value:parsed:info:tokenAmount:uiAmount ELSE 0 end) AS FBLLOW_out, sum(case when value:parsed:info:mint = 'FTk1E5UoWkiZEUttCWSYYaVokxWNNp3yJ42HbNDCAkdt' then value:parsed:info:tokenAmount:uiAmount ELSE 0 end) AS THRILL_out, sum(case when value:parsed:info:mint = 'FFkPvwLDYuKDW9eAAr5UNfuX3U9PcTGeSk7gqNX7EpNc' then value:parsed:info:tokenAmount:uiAmount ELSE 0 end) AS VZUSBA_out, sum(case when value:parsed:info:mint = '4gR3ChfdQxR4BTbgeWSdf6b8kD8Ysu6WBAQqtJ9oLgbF' then value:parsed:info:tokenAmount:uiAmount ELSE 0 end) AS CALMED_out, sum(case when value:parsed:info:mint = '8pPDsMNcz4m8jaajFMFXHGcvaeVeiQhcenvSD6a4XNyq' then value:parsed:info:tokenAmount:uiAmount ELSE 0 end) AS OPALBB_out, sum(case when value:parsed:info:mint = '2XYd22LSFGxN7kWgoEeaXVZqgrsPeQLHLEgNhnS12Mny' then value:parsed:info:tokenAmount:uiAmount ELSE 0 end) AS CALATS_out, sum(case when value:parsed:info:mint = 'Fys8J53cquYsg5zYfeZStVGNwM9FopFw8QFkiE9CCR1J' then value:parsed:info:tokenAmount:uiAmount ELSE 0 end) AS PR6_out, sum(case when value:parsed:info:mint = '6HzZJwrcuBBmrE7SLDfxheZGAD3NYJ531C9JsNesL9BP' then value:parsed:info:tokenAmount:uiAmount ELSE 0 end) AS HEART_out, sum(case when value:parsed:info:mint = 'J8Q6jYsrhhaeczyPBo9xzVyy4GpfCnJwj14LJn2HnuKp' then value:parsed:info:tokenAmount:uiAmount ELSE 0 end) AS OGKATU_out, sum(case when value:parsed:info:mint = '6Zj61HuX1E7SCUCf9WsKXw1jdJCobAwK4RSjZvbv35tM' then value:parsed:info:tokenAmount:uiAmount ELSE 0 end) AS FBLMAM_out, sum(case when value:parsed:info:mint = 'HjFijcGWKgfDwGpFX2rqFwEU9jtEgFuRQAJe1ERXFsA3' then value:parsed:info:tokenAmount:uiAmount ELSE 0 end) AS VZUSSO_out, sum(case when value:parsed:info:mint = '4ns3shP4WunCtJbr2HFu31RjjxSJxDymEFcBZxiHr11s' then value:parsed:info:tokenAmount:uiAmount ELSE 0 end) AS FBLBTA_out, sum(case when value:parsed:info:mint = '4b4mhSySBcryzBPamw8v4xeneFRA6xTUA4JA99w6vqey' then value:parsed:info:tokenAmount:uiAmount ELSE 0 end) AS OGKASP_out, sum(case when value:parsed:info:mint = 'H3cgBXWpUiNsYjUWS7cNR5Bmehh7k5CgpJccc5wSfRbJ' then value:parsed:info:tokenAmount:uiAmount ELSE 0 end) AS PD9_out, sum(case when value:parsed:info:mint = 'DB8CSxoakPRtXhHcc2cA3iETWfGaYY6zE2T8huJTE2Nw' then value:parsed:info:tokenAmount:uiAmount ELSE 0 end) AS STAND_out, sum(case when value:parsed:info:mint = '2BMTgfgapuqYBwSRGu6HCafwDGRVTxMkQVhYrNWQGTWH' then value:parsed:info:tokenAmount:uiAmount ELSE 0 end) AS SUPER_out, sum(case when value:parsed:info:mint = '56nh4FvMJnSpkWJcmXc1nJtYhSQXuqb7nVMGALr31RVc' then value:parsed:info:tokenAmount:uiAmount ELSE 0 end) AS TITAN_out, sum(case when value:parsed:info:mint = 'F3HitKsp52UPqBMEWSeTFqrGgnfYbS9DMrJCz9dM3w6D' then value:parsed:info:tokenAmount:uiAmount ELSE 0 end) AS CALSCD_out, sum(case when value:parsed:info:mint = 'GxpbUDxYYvxiUejHcAMzeV2rzdHf6KZZvT86ACrpFgXa' then value:parsed:info:tokenAmount:uiAmount ELSE 0 end) AS CALMAX_out, sum(case when value:parsed:info:mint = '7SUoWHWWJCxCe5g9XqZkCRufGHXRV8nauuz69HPjuewr' then value:parsed:info:tokenAmount:uiAmount ELSE 0 end) AS OGKANR_out, sum(case when value:parsed:info:mint = 'DTbNmLWfu1pm4AuXRKYTApnDNfxMz73VET7nW5wizG5t' then value:parsed:info:tokenAmount:uiAmount ELSE 0 end) AS IMP1_out, sum(case when value:parsed:info:mint = 'ARNZXUQoBKx3JCX3UJB4aitSnvcjMMphN9YVDFy1PdKq' then value:parsed:info:tokenAmount:uiAmount ELSE 0 end) AS IMP2_out, sum(case when value:parsed:info:mint = 'GmVKV9W3qZcERxk7hjqwRDcn9Kgtz3XDi7KfFLdGqyaW' then value:parsed:info:tokenAmount:uiAmount ELSE 0 end) AS IMP3_out from solana.core.fact_events, lateral flatten(input => inner_instruction:instructions) WHERE program_id = 'FLEET1qqzpexyaDpqb2DGsSzE2sDCizewCg9WjrA6DBW' AND succeeded = 'true' --AND tx_id = 'SK9XbzbXVy7jgsEPp8Y8bzV8Y69CWnCJ5NJW2XHhAwGZnBhs5ABNtwMRXKqNfWPKN2eYS9fLewhWz3CmcWeddAb' AND value:parsed:info:authority != signers[0]::string --AND signers[0] = 'Cbhe953AS3FAMW6kjVG9J2saEKM46W3yiP2a3Ld8wVXN' --AND value:parsed:info:mint IN ('9ifQ16N5DdUFoejCwsgR73ihUwadAe3srCo9HhQe2zL2') --AND value:parsed:info:mint NOT IN ('ammoK8AkX2wnebQb35cDAZtTkvsXQbi82cGeTnUvvfK', 'foodQJAztMzX1DKpLaiounNe2BDMds5RNuPC6jsNrDG', 'fueL3hBZjLLLJHiFH9cqZoozTG3XQZ53diwFPwbzNim', 'tooLsNYLiVqzg8o4m3L2Uetbn62mvMWRqkog6PQeYKL') AND block_timestamp > '2021-12-16' --AND block_timestamp < '2023-06-01' group by 1 ), /* cs_in as ( select tx_to as wallet, sum(case when mint = 'HzUBawF9xxTy4mTuvSkk1a4voJcm65tSHZz6voCDUB33' then amount * 32 when mint = '2piSPCxbuibsraBnnK4M5rGeHSraNe2oiD8hDw42bPKq' then amount * 243 when mint = 'C2uF4fECabWryVCV1bDuxP7jMspbf2gei3YAP2UBn292' then amount * 1024 when mint = 'EBEJj1LKuo1k1J2ZvNJxsXATdGYnfaLWzqxck5p4PXSz' then amount * 3125 when mint = '3aTW9zvPz5f7vt2Mfnr5zWA2RcgNTtDSSrBwaxyZ6t7d' then amount * 7776 when mint = 'DTbNmLWfu1pm4AuXRKYTApnDNfxMz73VET7nW5wizG5t' then amount * 928.8 when mint = 'ARNZXUQoBKx3JCX3UJB4aitSnvcjMMphN9YVDFy1PdKq' then amount * 2624.4 when mint = 'GmVKV9W3qZcERxk7hjqwRDcn9Kgtz3XDi7KfFLdGqyaW' then amount * 29700 ELSE 0 end) as cs_wallet_in from solana.core.fact_transfers where tx_to != 'HrPdPZ1oPNHkbab847dp4AGUMW8eaNAr5oNeRmYZnuwY' --AND block_timestamp > '2022-01-01' --AND block_timestamp < '2021-12-18' group by 1 ), cs_out as ( select tx_from as wallet, sum(case when mint = 'HzUBawF9xxTy4mTuvSkk1a4voJcm65tSHZz6voCDUB33' then amount * 32 when mint = '2piSPCxbuibsraBnnK4M5rGeHSraNe2oiD8hDw42bPKq' then amount * 243 when mint = 'C2uF4fECabWryVCV1bDuxP7jMspbf2gei3YAP2UBn292' then amount * 1024 when mint = 'EBEJj1LKuo1k1J2ZvNJxsXATdGYnfaLWzqxck5p4PXSz' then amount * 3125 when mint = '3aTW9zvPz5f7vt2Mfnr5zWA2RcgNTtDSSrBwaxyZ6t7d' then amount * 7776 when mint = 'DTbNmLWfu1pm4AuXRKYTApnDNfxMz73VET7nW5wizG5t' then amount * 928.8 when mint = 'ARNZXUQoBKx3JCX3UJB4aitSnvcjMMphN9YVDFy1PdKq' then amount * 2624.4 when mint = 'GmVKV9W3qZcERxk7hjqwRDcn9Kgtz3XDi7KfFLdGqyaW' then amount * 29700 ELSE 0 end) as cs_wallet_out from solana.core.fact_transfers where tx_from != 'HrPdPZ1oPNHkbab847dp4AGUMW8eaNAr5oNeRmYZnuwY' --AND block_timestamp > '2022-01-01' --AND block_timestamp < '2021-12-18' group by 1 ), cs_wallet as ( SELECT x.wallet as wallet, zeroifnull(cs_wallet_in) - zeroifnull(cs_wallet_out) as cs_wallet FROM cs_in x left outer join cs_out y on x.wallet = y.wallet group by 1,2 ), */ cs_staking_in as ( select signers[0] as wallet, ----------------------------------------------------------------------------------------------------- sum(case when instruction::string LIKE '%HzUBawF9xxTy4mTuvSkk1a4voJcm65tSHZz6voCDUB33%' then value:parsed:info:amount ELSE 0 end) as CS1_in, sum(case when instruction::string LIKE '%2piSPCxbuibsraBnnK4M5rGeHSraNe2oiD8hDw42bPKq%' then value:parsed:info:amount ELSE 0 end) as CS2_in, sum(case when instruction::string LIKE '%C2uF4fECabWryVCV1bDuxP7jMspbf2gei3YAP2UBn292%' then value:parsed:info:amount ELSE 0 end) as CS3_in, sum(case when instruction::string LIKE '%EBEJj1LKuo1k1J2ZvNJxsXATdGYnfaLWzqxck5p4PXSz%' then value:parsed:info:amount ELSE 0 end) as CS4_in, sum(case when instruction::string LIKE '%3aTW9zvPz5f7vt2Mfnr5zWA2RcgNTtDSSrBwaxyZ6t7d%' then value:parsed:info:amount ELSE 0 end) as CS5_in, sum(case when instruction::string LIKE '%DTbNmLWfu1pm4AuXRKYTApnDNfxMz73VET7nW5wizG5t%' then value:parsed:info:amount ELSE 0 end) as IMP1_in, sum(case when instruction::string LIKE '%ARNZXUQoBKx3JCX3UJB4aitSnvcjMMphN9YVDFy1PdKq%' then value:parsed:info:amount ELSE 0 end) as IMP2_in, sum(case when instruction::string LIKE '%GmVKV9W3qZcERxk7hjqwRDcn9Kgtz3XDi7KfFLdGqyaW%' then value:parsed:info:amount ELSE 0 end) as IMP3_in from solana.core.fact_events, lateral flatten(input => inner_instruction:instructions) WHERE program_id = 'STAKEr4Bh8sbBMoAVmTDBRqouPzgdocVrvtjmhJhd65' AND succeeded = 'true' AND value:parsed:info:authority = signers[0]::string AND block_timestamp > '2023-04-12' --AND block_timestamp < '2023-04-01' group by 1 ), cs_staking_out as ( select signers[0] as wallet, ----------------------------------------------------------------------------------------------------- sum(case when instruction::string LIKE '%HzUBawF9xxTy4mTuvSkk1a4voJcm65tSHZz6voCDUB33%' then value:parsed:info:amount ELSE 0 end) as CS1_out, sum(case when instruction::string LIKE '%2piSPCxbuibsraBnnK4M5rGeHSraNe2oiD8hDw42bPKq%' then value:parsed:info:amount ELSE 0 end) as CS2_out, sum(case when instruction::string LIKE '%C2uF4fECabWryVCV1bDuxP7jMspbf2gei3YAP2UBn292%' then value:parsed:info:amount ELSE 0 end) as CS3_out, sum(case when instruction::string LIKE '%EBEJj1LKuo1k1J2ZvNJxsXATdGYnfaLWzqxck5p4PXSz%' then value:parsed:info:amount ELSE 0 end) as CS4_out, sum(case when instruction::string LIKE '%3aTW9zvPz5f7vt2Mfnr5zWA2RcgNTtDSSrBwaxyZ6t7d%' then value:parsed:info:amount ELSE 0 end) as CS5_out, sum(case when instruction::string LIKE '%DTbNmLWfu1pm4AuXRKYTApnDNfxMz73VET7nW5wizG5t%' then value:parsed:info:amount ELSE 0 end) as IMP1_out, sum(case when instruction::string LIKE '%ARNZXUQoBKx3JCX3UJB4aitSnvcjMMphN9YVDFy1PdKq%' then value:parsed:info:amount ELSE 0 end) as IMP2_out, sum(case when instruction::string LIKE '%GmVKV9W3qZcERxk7hjqwRDcn9Kgtz3XDi7KfFLdGqyaW%' then value:parsed:info:amount ELSE 0 end) as IMP3_out from solana.core.fact_events, lateral flatten(input => inner_instruction:instructions) WHERE program_id = 'STAKEr4Bh8sbBMoAVmTDBRqouPzgdocVrvtjmhJhd65' AND succeeded = 'true' AND value:parsed:info:source::string NOT IN ('4RoeBTjsMyMf7wJ1a3Hi3NsppphgvpVLHaC5Tb4FKUQ8', 'Fj1fDGX77KoFLbB7tLL5xg9xR7DvJxNmU3yo3QR1hgm', '9Dp9Rjh6mmDFPDkLVhp6SbJp2Xj2EDGAWy46yVrfzAFG', 'CdQHUngrpj21e5Pi7WD21Uj5w6wDWX4AK1McuedHMDga') AND value:parsed:info:authority != signers[0] AND block_timestamp > '2023-04-12' --AND block_timestamp < '2023-04-01' group by 1 ), 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 ('JUP3c2Uh3WA4Ng34tw6kPd2G4C5BB21Xo36Je1s32Ph', 'JUP2jxvXaqu7NQY1GmNF4m1vodw12LVXYxbFL2uJvfo') AND block_timestamp > dateadd(hour, -24, current_date) --group by 1 ), r4_prices as ( select --* --date_trunc('day', block_timestamp::date) as date, ---------------------------------------------------- sum(case when inner_instruction:instructions[2]:parsed:info:mint::string = 'fueL3hBZjLLLJHiFH9cqZoozTG3XQZ53diwFPwbzNim' then inner_instruction:instructions[2]:parsed:info:tokenAmount:uiAmount end) AS fuel_amount, sum(case when inner_instruction:instructions[2]:parsed:info:mint::string = 'fueL3hBZjLLLJHiFH9cqZoozTG3XQZ53diwFPwbzNim' then (inner_instruction:instructions[0]:parsed:info:tokenAmount:uiAmount + inner_instruction:instructions[1]:parsed:info:tokenAmount:uiAmount) end) AS fuel_atlas_amount, fuel_atlas_amount / fuel_amount as fuel_price, ---------------------------------------------------- sum(case when inner_instruction:instructions[2]:parsed:info:mint::string = 'foodQJAztMzX1DKpLaiounNe2BDMds5RNuPC6jsNrDG' then inner_instruction:instructions[2]:parsed:info:tokenAmount:uiAmount end) AS food_amount, sum(case when inner_instruction:instructions[2]:parsed:info:mint::string = 'foodQJAztMzX1DKpLaiounNe2BDMds5RNuPC6jsNrDG' then (inner_instruction:instructions[0]:parsed:info:tokenAmount:uiAmount + inner_instruction:instructions[1]:parsed:info:tokenAmount:uiAmount) end) AS food_atlas_amount, food_atlas_amount / food_amount as food_price, ---------------------------------------------------- sum(case when inner_instruction:instructions[2]:parsed:info:mint::string = 'ammoK8AkX2wnebQb35cDAZtTkvsXQbi82cGeTnUvvfK' then inner_instruction:instructions[2]:parsed:info:tokenAmount:uiAmount end) AS ammo_amount, sum(case when inner_instruction:instructions[2]:parsed:info:mint::string = 'ammoK8AkX2wnebQb35cDAZtTkvsXQbi82cGeTnUvvfK' then (inner_instruction:instructions[0]:parsed:info:tokenAmount:uiAmount + inner_instruction:instructions[1]:parsed:info:tokenAmount:uiAmount) end) AS ammo_atlas_amount, ammo_atlas_amount / ammo_amount as ammo_price, ------------------------------------------------------ sum(case when inner_instruction:instructions[2]:parsed:info:mint::string = 'tooLsNYLiVqzg8o4m3L2Uetbn62mvMWRqkog6PQeYKL' then inner_instruction:instructions[2]:parsed:info:tokenAmount:uiAmount end) AS tool_amount, sum(case when inner_instruction:instructions[2]:parsed:info:mint::string = 'tooLsNYLiVqzg8o4m3L2Uetbn62mvMWRqkog6PQeYKL' then (inner_instruction:instructions[0]:parsed:info:tokenAmount:uiAmount + inner_instruction:instructions[1]:parsed:info:tokenAmount:uiAmount) end) AS tool_atlas_amount, tool_atlas_amount / tool_amount as tool_price --*/ from solana.core.fact_events--, lateral flatten(input => inner_instruction) WHERE program_id = 'traderDnaR5w6Tcoi3NFm53i48FTDNbGjBSZwWXDRrg' AND succeeded = 'true' --AND inner_instruction:instructions[0]:parsed:info:tokenAmount:uiAmount != 0 AND inner_instruction:instructions[1]:parsed:info:tokenAmount:uiAmount != 0 AND inner_instruction:instructions[2]:parsed:info:tokenAmount:uiAmount != 0 --AND inner_instruction[0]:instructions[2]:parsed:info:mint::string = 'fueL3hBZjLLLJHiFH9cqZoozTG3XQZ53diwFPwbzNim' --AND log_messages::string LIKE '%ProcessExchange%' --AND tx_id = 'yVyU5pWLe5Uh8gMj2nwMSWZp1G8sGFdhpurG9mirgNNHTm8kG6Benk7m1gPParfZJvY8W8vTiaZWZchkDZKmVjL' --AND inner_instructions[0]:instructions[1]:parsed:info:mint::string = 'ATLASXmbPQxBUYbxPsV97usA3fPQYEqzQBUHgiFCUsXx' AND block_timestamp > dateadd(hour, -24, current_date) --limit 100 --group by 1 --order by 1 desc ), r4_data AS ( SELECT ----------------------------------------------------------------------------------------------------- 26 as opalj_ammo, 21 as opalj_food, 37 as opalj_fuel, 36 as opalj_tool, ----------------------------------------------------------------------------------------------------- 292 as pxfive_ammo, 173 as pxfive_food, 216 as pxfive_fuel, 303 as pxfive_tool, ----------------------------------------------------------------------------------------------------- 1671.02 as vzusop_ammo, 3610.08 as vzusop_food, 2841.08 as vzusop_fuel, 2340 as vzusop_tool, ----------------------------------------------------------------------------------------------------- 3426.12 as calch_ammo, 3510.05 as calch_food, 3092.01 as calch_fuel, 3844.1 as calch_tool, ----------------------------------------------------------------------------------------------------- 13569.97 as ogkatp_ammo, 8513.15 as ogkatp_food, 11707.32 as ogkatp_fuel, 14899.12 as ogkatp_tool, ----------------------------------------------------------------------------------------------------- 39 as PXFOUR_ammo, 23 as PXFOUR_food, 29 as PXFOUR_fuel, 41 as PXFOUR_tool, ----------------------------------------------------------------------------------------------------- 216 as OPALJJ_ammo, 148 as OPALJJ_food, 444 as OPALJJ_fuel, 433 as OPALJJ_tool, ----------------------------------------------------------------------------------------------------- 3510.05 as FBLBPL_ammo, 3510.05 as FBLBPL_food, 5014.22 as FBLBPL_fuel, 2841.08 as FBLBPL_tool, ----------------------------------------------------------------------------------------------------- 24066.85 as CALG_ammo, 24650.5 as CALG_food, 21714 as CALG_fuel, 27000 as CALG_tool, ----------------------------------------------------------------------------------------------------- 4513.16 as PFFOUR_ammo, 2674.01 as PFFOUR_food, 3343.01 as PFFOUR_fuel, 4680.14 as PFFOUR_tool, ----------------------------------------------------------------------------------------------------- 10643.02 as FBLEGR_ammo, 9471.61 as FBLEGR_food, 9578.71 as FBLEGR_fuel, 13835.07 as FBLEGR_tool, ----------------------------------------------------------------------------------------------------- 3176 as OM_ammo, 3510.05 as OM_food, 2006.04 as OM_fuel, 3844.10 as OM_tool, ----------------------------------------------------------------------------------------------------- 9578.71 as PREIGHT_ammo, 10643.02 as PREIGHT_food, 11707.32 as PREIGHT_fuel, 13835.07 as PREIGHT_tool, ----------------------------------------------------------------------------------------------------- 1352.01 as VZUSAM_ammo, 832 as VZUSAM_food, 1248.01 as VZUSAM_fuel, 1456.02 as VZUSAM_tool, ----------------------------------------------------------------------------------------------------- 31694.79 as PCNINE_ammo, 18782.61 as PCNINE_food, 23478.26 as PCNINE_fuel, 32864.21 as PCNINE_tool, ----------------------------------------------------------------------------------------------------- 95364.24 as PCELEVEN_ammo, 56470.59 as PCELEVEN_food, 70588.2446 as PCELEVEN_fuel, 98855.84 as PCELEVEN_tool, ----------------------------------------------------------------------------------------------------- 1404.01 as CHI_ammo, 822 as CHI_food, 624 as CHI_fuel, 1456.02 as CHI_tool, ----------------------------------------------------------------------------------------------------- 20542.08 as OGKAJA_ammo, 32287 as OGKAJA_food, 25821.88 as OGKAJA_fuel, 25821.88 as OGKAJA_tool, ----------------------------------------------------------------------------------------------------- 3092.01 as CALEV_ammo, 4346.08 as CALEV_food, 2925.05 as CALEV_fuel, 4011.14 as CALEV_tool, ----------------------------------------------------------------------------------------------------- 1404.01 as TUFAFE_ammo, 416 as TUFAFE_food, 1144.01 as TUFAFE_fuel, 1456.02 as TUFAFE_tool, ----------------------------------------------------------------------------------------------------- 1404.01 as FBLBEA_ammo, 832 as FBLBEA_food, 1144.01 as FBLBEA_fuel, 1144.01 as FBLBEA_tool, ----------------------------------------------------------------------------------------------------- 1404.01 as PXSIX_ammo, 843 as PXSIX_food, 1040.01 as PXSIX_fuel, 1456.02 as PXSIX_tool, ----------------------------------------------------------------------------------------------------- 31694.79 as FBLEBO_ammo, 20894.8 as FBLEBO_food, 21129.86 as FBLEBO_fuel, 32864.21 as FBLEBO_tool, ----------------------------------------------------------------------------------------------------- 95364.24 as FBLETR_ammo, 62836.36 as FBLETR_food, 63529.41 as FBLETR_fuel, 98855.84 as FBLETR_tool, ----------------------------------------------------------------------------------------------------- 26 as FBLAIR_ammo, 23 as FBLAIR_food, 37 as FBLAIR_fuel, 36 as FBLAIR_tool, ----------------------------------------------------------------------------------------------------- 1326.01 as OGKAMK_ammo, 806 as OGKAMK_food, 1144.01 as OGKAMK_fuel, 1456.02 as OGKAMK_tool, ----------------------------------------------------------------------------------------------------- 39 as FBLEUN_ammo, 26 as FBLEUN_food, 26 as FBLEUN_fuel, 41 as FBLEUN_tool, ----------------------------------------------------------------------------------------------------- 10111.18 as ARC_ammo, 11174.34 as ARC_food, 6385.34 as ARC_fuel, 12239.69 as ARC_tool, ----------------------------------------------------------------------------------------------------- 201 as FBLLOW_ammo, 299 as FBLLOW_food, 190 as FBLLOW_fuel, 234 as FBLLOW_tool, ----------------------------------------------------------------------------------------------------- 1300.01 as THRILL_ammo, 832 as THRILL_food, 1144.01 as THRILL_fuel, 1456.02 as THRILL_tool, ----------------------------------------------------------------------------------------------------- 31694.79 as VZUSBA_ammo, 16432.1 as VZUSBA_food, 25821.88 as VZUSBA_fuel, 32864.21 as VZUSBA_tool, ----------------------------------------------------------------------------------------------------- 962.01 as CALMED_ammo, 1352.01 as CALMED_food, 910 as CALMED_fuel, 1248.01 as CALMED_tool, ----------------------------------------------------------------------------------------------------- 10111.18 as OPALBB_ammo, 14101.52 as OPALBB_food, 9312.35 as OPALBB_fuel, 11707.32 as OPALBB_tool, ----------------------------------------------------------------------------------------------------- 4429.18 as CALATS_ammo, 2674.01 as CALATS_food, 3426.12 as CALATS_fuel, 4680.14 as CALATS_tool, ----------------------------------------------------------------------------------------------------- 936.01 as PR6_ammo, 1040.01 as PR6_food, 1144.01 as PR6_fuel, 1352.01 as PR6_tool, ----------------------------------------------------------------------------------------------------- 29347.83 as HEART_ammo, 18782.61 as HEART_food, 25821.88 as HEART_fuel, 32864.21 as HEART_tool, ----------------------------------------------------------------------------------------------------- 4262.04 as OGKATU_ammo, 2591.02 as OGKATU_food, 3677.07 as OGKATU_fuel, 4680.14 as OGKATU_tool, ----------------------------------------------------------------------------------------------------- 4279.13 as FBLMAM_ammo, 2942.08 as FBLMAM_food, 3945.03 as FBLMAM_fuel, 4613.17 as FBLMAM_tool, ----------------------------------------------------------------------------------------------------- 26 as VZUSSO_ammo, 20 as VZUSSO_food, 38 as VZUSSO_fuel, 36 as VZUSSO_tool, ----------------------------------------------------------------------------------------------------- 95364.24 as FBLBTA_ammo, 56470.59 as FBLBTA_food, 77697.84 as FBLBTA_fuel, 77697.84 as FBLBTA_tool, ----------------------------------------------------------------------------------------------------- 9312.35 as OGKASP_ammo, 10909.09 as OGKASP_food, 14899.12 as OGKASP_fuel, 12239.69 as OGKASP_tool, ----------------------------------------------------------------------------------------------------- 16432.10 as PD9_ammo, 21129.86 as PD9_food, 23478.26 as PD9_fuel, 28170.85 as PD9_tool, ----------------------------------------------------------------------------------------------------- 3600000 as STAND_ammo, 2335135.135 as STAND_food, 4114285.71 as STAND_fuel, 4114285.71 as STAND_tool, ----------------------------------------------------------------------------------------------------- 3927272.727 as SUPER_ammo, 2618181.818 as SUPER_food, 2618181.818 as SUPER_fuel, 4114285.71 as SUPER_tool, ----------------------------------------------------------------------------------------------------- 3927272.727 as TITAN_ammo, 2335135.135 as TITAN_food, 2880000 as TITAN_fuel, 4114285.71 as TITAN_tool, ----------------------------------------------------------------------------------------------------- 189 as CALSCD_ammo, 173 as CALSCD_food, 265 as CALSCD_fuel, 271 as CALSCD_tool, ----------------------------------------------------------------------------------------------------- 200 as CALMAX_ammo, 173 as CALMAX_food, 265 as CALMAX_fuel, 216 as CALMAX_tool, ----------------------------------------------------------------------------------------------------- 189 as OGKANR_ammo, 298 as OGKANR_food, 195 as OGKANR_fuel, 238 as OGKANR_tool, ----------------------------------------------------------------------------------------------------- 104 as IMP1_ammo, 1404 as IMP1_food, 1404 as IMP1_fuel, 1248 as IMP1_tool, ----------------------------------------------------------------------------------------------------- 334 as IMP2_ammo, 4513 as IMP2_food, 4513 as IMP2_fuel, 4011 as IMP2_tool, ----------------------------------------------------------------------------------------------------- 2347 as IMP3_ammo, 31690 as IMP3_food, 31690 as IMP3_fuel, 28168 as IMP3_tool ), ships_data AS ( SELECT ----------------------------------------------------------------------------------------------------- 20 as opalj_vwap, 2 as opalj_fp, 'racer' as opalj_role, 0.85 as opalj_rewards, ----------------------------------------------------------------------------------------------------- 140.48 as pxfive_vwap, 16.33 as pxfive_fp, 'fighter' as pxfive_role, 6.25 as pxfive_rewards, ----------------------------------------------------------------------------------------------------- 1666.67 as vzusop_vwap, 100.74 as vzusop_fp, 'data runner' as vzusop_role, 69.51 as vzusop_rewards, ----------------------------------------------------------------------------------------------------- 3786.67 as calch_vwap, 120.89 as calch_fp, 'multi-role' as calch_role, 146.87 as calch_rewards, ----------------------------------------------------------------------------------------------------- 8506.43 as ogkatp_vwap, 438.59 as ogkatp_fp, 'fighter' as ogkatp_role, 360.67 as ogkatp_rewards, ----------------------------------------------------------------------------------------------------- 22.66 as PXFOUR_vwap, 4 as PXFOUR_fp, 'fighter' as PXFOUR_role, 0.97 as PXFOUR_rewards, ----------------------------------------------------------------------------------------------------- 308.91 as OPALJJ_vwap, 8.16 as OPALJJ_fp, 'racer' as OPALJJ_role, 12.24 as opaljj_rewards, ----------------------------------------------------------------------------------------------------- 3513.13 as FBLBPL_vwap, 80.59 as FBLBPL_fp, 'freight' as FBLBPL_role, 138.94 as FBLBPL_rewards, ----------------------------------------------------------------------------------------------------- 30883.91 as CALG_vwap, 1044.27 as CALG_fp, 'multi-role' as CALG_role, 1174.47 as CALG_rewards, ----------------------------------------------------------------------------------------------------- 2840.35 as PFFOUR_vwap, 190.24 as PFFOUR_fp, 'fighter' as PFFOUR_role, 118.98 as PFFOUR_rewards, ----------------------------------------------------------------------------------------------------- 8603.14 as FBLEGR_vwap, 419.25 as FBLEGR_fp, 'bomber' as FBLEGR_role, 352.98 as FBLEGR_rewards, ----------------------------------------------------------------------------------------------------- 2807.16 as OM_vwap, 95.4 as OM_fp, 'freight' as OM_role, 111.64 as OM_rewards, ----------------------------------------------------------------------------------------------------- 11104.67 as PREIGHT_vwap, 219.3 as PREIGHT_fp, 'refuel/repair' as PREIGHT_role, 434 as PREIGHT_rewards, ----------------------------------------------------------------------------------------------------- 1200 as VZUSAM_vwap, 35.06 as VZUSAM_fp, 'bounty hunter' as VZUSAM_role, 47.74 as VZUSAM_rewards, ----------------------------------------------------------------------------------------------------- 31829.19 as PCNINE_vwap, 1926.25 as PCNINE_fp, 'fighter' as PCNINE_role, 1210.45 as PCNINE_rewards, ----------------------------------------------------------------------------------------------------- 90090.83 as PCELEVEN_vwap, 4790.85 as PCELEVEN_fp, 'fighter' as PCELEVEN_role, 3462.06 as PCELEVEN_rewards, ----------------------------------------------------------------------------------------------------- 995 as CHI_vwap, 44.43 as CHI_fp, 'fighter' as CHI_role, 40.12 as CHI_rewards, ----------------------------------------------------------------------------------------------------- 30920.58 as OGKAJA_vwap, 895.09 as OGKAJA_fp, 'transport' as OGKAJA_role, 1145.28 as OGKAJA_rewards, ----------------------------------------------------------------------------------------------------- 2601.1 as CALEV_vwap, 55.10 as CALEV_fp, 'rescue' as CALEV_role, 106.56 as CALEV_rewards, ----------------------------------------------------------------------------------------------------- 795 as TUFAFE_vwap, 37.02 as TUFAFE_fp, 'fighter' as TUFAFE_role, 33.95 as TUFAFE_rewards, ----------------------------------------------------------------------------------------------------- 895 as FBLBEA_vwap, 55.1 as FBLBEA_fp, 'fighter' as FBLBEA_role, 36.99 as FBLBEA_rewards, ----------------------------------------------------------------------------------------------------- 930 as PXSIX_vwap, 55.31 as PXSIX_fp, 'fighter' as PXSIX_role, 38.56 as PXSIX_rewards, ----------------------------------------------------------------------------------------------------- 36563.69 as FBLEBO_vwap, 517.66 as FBLEBO_fp, 'bomber' as FBLEBO_role, 1374.6 as FBLEBO_rewards, ----------------------------------------------------------------------------------------------------- 105250 as FBLETR_vwap, 3475.07 as FBLETR_fp, 'bomber' as FBLETR_role, 3980.10 as FBLETR_rewards, ----------------------------------------------------------------------------------------------------- 16.5 as FBLAIR_vwap, 0 as FBLAIR_fp, 'racer' as FBLAIR_role, 0.74 as FBLAIR_rewards, ----------------------------------------------------------------------------------------------------- 770 as OGKAMK_vwap, 27.66 as OGKAMK_fp, 'fighter' as OGKAMK_role, 33.19 as OGKAMK_rewards, ----------------------------------------------------------------------------------------------------- 35 as FBLEUN_vwap, 2 as FBLEUN_fp, 'bomber' as FBLEUN_role, 1.37 as FBLEUN_rewards, ----------------------------------------------------------------------------------------------------- 13721.40 as ARC_vwap, 259.59 as ARC_fp, 'freight' as ARC_role, 516.44 as ARC_rewards, ----------------------------------------------------------------------------------------------------- 60 as FBLLOW_vwap, 0 as FBLLOW_fp, 'transport' as FBLLOW_role, 3.30 as FBLLOW_rewards, ----------------------------------------------------------------------------------------------------- 946 as THRILL_vwap, 44.43 as THRILL_fp, 'fighter' as THRILL_role, 39.01 as THRILL_rewards, ----------------------------------------------------------------------------------------------------- 33286 as VZUSBA_vwap, 1926.25 as VZUSBA_fp, 'fighter' as VZUSBA_role, 1282 as VZUSBA_rewards, ----------------------------------------------------------------------------------------------------- 942.8 as CALMED_vwap, 10.88 as CALMED_fp, 'rescue' as CALMED_role, 37.81 as CALMED_rewards, ----------------------------------------------------------------------------------------------------- 10728 as OPALBB_vwap, 328.94 as OPALBB_fp, 'transport' as OPALBB_role, 421.73 as OPALBB_rewards, ----------------------------------------------------------------------------------------------------- 2423 as CALATS_vwap, 270.07 as CALATS_fp, 'fighter' as CALATS_role, 104.99 as CALATS_rewards, ----------------------------------------------------------------------------------------------------- 1091.8 as PR6_vwap, 20.25 as PR6_fp, 'repair' as PR6_role, 43.05 as PR6_rewards, ----------------------------------------------------------------------------------------------------- 32585.75 as HEART_vwap, 1114.38 as HEART_fp, 'fighter' as HEART_role, 1255.05 as HEART_rewards, ----------------------------------------------------------------------------------------------------- 2772 as OGKATU_vwap, 110.21 as OGKATU_fp, 'fighter' as OGKATU_role, 115.74 as OGKATU_rewards, ----------------------------------------------------------------------------------------------------- 1881 as FBLMAM_vwap, 190.24 as FBLMAM_fp, 'bounty hunter' as FBLMAM_role, 87.39 as FBLMAM_rewards, ----------------------------------------------------------------------------------------------------- 20 as VZUSSO_vwap, 2.53 as VZUSSO_fp, 'racer' as VZUSSO_role, 0.85 as VZUSSO_rewards, ----------------------------------------------------------------------------------------------------- 102118 as FBLBTA_vwap, 5653.28 as FBLBTA_fp, 'fighter' as FBLBTA_role, 3890.04 as FBLBTA_rewards, ----------------------------------------------------------------------------------------------------- 10176 as OGKASP_vwap, 161.18 as OGKASP_fp, 'freight' as OGKASP_role, 408.59 as OGKASP_rewards, ----------------------------------------------------------------------------------------------------- 31043.48 as PD9_vwap, 580.43 as PD9_fp, 'salvage' as PD9_role, 1165.77 as PD9_rewards, ----------------------------------------------------------------------------------------------------- 5000000 as STAND_vwap, 13794 as STAND_fp, 'fighter' as STAND_role, 172499.17 as STAND_rewards, ----------------------------------------------------------------------------------------------------- 5000000 as SUPER_vwap, 13914 as SUPER_fp, 'bomber' as SUPER_role, 172506.99 as SUPER_rewards, ----------------------------------------------------------------------------------------------------- 5000000 as TITAN_vwap, 16377.75 as TITAN_fp, 'fighter' as TITAN_role, 172665.58 as TITAN_rewards, ----------------------------------------------------------------------------------------------------- 0 as CALSCD_vwap, 7 as CALSCD_fp, 'racer' as CALSCD_role, 7.2 as CALSCD_rewards, ----------------------------------------------------------------------------------------------------- 0 as CALMAX_vwap, 10.67 as CALMAX_fp, 'transport' as CALMAX_role, 6.46 as CALMAX_rewards, ----------------------------------------------------------------------------------------------------- 0 as OGKANR_vwap, 7.78 as OGKANR_fp, 'transport' as OGKANR_role, 7.78 as OGKANR_rewards, ----------------------------------------------------------------------------------------------------- 928.8 as IMP1_vwap, 0 as IMP1_fp, 'miner' as IMP1_role, 36.235 as IMP1_rewards, ----------------------------------------------------------------------------------------------------- 2624.4 as IMP2_vwap, 0 as IMP2_fp, 'miner' as IMP2_role, 104.443 as IMP2_rewards, ----------------------------------------------------------------------------------------------------- 29700 as IMP3_vwap, 0 as IMP3_fp, 'miner' as IMP3_role, 1107.804 as IMP3_rewards ), ---------------------------------------------------------------------------------------------------------------------- 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 ), 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, row_number() over (PARTITION BY x.wallet ORDER BY y.date) as row_num 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 ), PVP as ( SELECT wallet, polis_locked, pvp as est_PVP FROM apr QUALIFY ROW_NUMBER() OVER (PARTITION BY WALLET ORDER BY date desc) = 1 --WHERE row_num = 1 order by 2 desc ), atlas_locked AS ( select --* signers[0] as wallet, 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' group by 1 order by 2 desc ), --------------------------------------------------------------------------------------------- ships_staked as ( select x.wallet as wallet, OPALJ_IN - OPALJ_OUT AS OPALJ, PXFIVE_IN - PXFIVE_OUT AS PXFIVE, VZUSOP_IN - VZUSOP_OUT AS VZUSOP, CALCH_IN - CALCH_OUT AS CALCH, OGKATP_IN - OGKATP_out AS OGKATP, --- PXFOUR_in - PXFOUR_out AS PXFOUR, OPALJJ_in - OPALJJ_out AS OPALJJ, FBLBPL_in - FBLBPL_out AS FBLBPL, CALG_in - CALG_out AS CALG, PFFOUR_in - PFFOUR_out AS PFFOUR, --- FBLEGR_in - FBLEGR_out AS FBLEGR, OM_in - OM_out AS OM, PREIGHT_in - PREIGHT_out AS PREIGHT, VZUSAM_in - VZUSAM_out AS VZUSAM, PCNINE_in - PCNINE_out AS PCNINE, --- PCELEVEN_in - PCELEVEN_out AS PCELEVEN, CHI_in - CHI_out AS CHI, OGKAJA_in - OGKAJA_out AS OGKAJA, CALEV_in - CALEV_out AS CALEV, TUFAFE_in - TUFAFE_out AS TUFAFE, --- FBLBEA_in - FBLBEA_out AS FBLBEA, PXSIX_in - PXSIX_out AS PXSIX, FBLEBO_in - FBLEBO_out AS FBLEBO, FBLETR_in - FBLETR_out AS FBLETR, FBLAIR_in - FBLAIR_out AS FBLAIR, --- OGKAMK_in - OGKAMK_out AS OGKAMK, FBLEUN_in - FBLEUN_out AS FBLEUN, ARC_in - ARC_out AS ARC, FBLLOW_in - FBLLOW_out AS FBLLOW, THRILL_in - THRILL_out AS THRILL, --- VZUSBA_in - VZUSBA_out AS VZUSBA, CALMED_in - CALMED_out AS CALMED, OPALBB_in - OPALBB_out AS OPALBB, CALATS_in - CALATS_out AS CALATS, PR6_in - PR6_out AS PR6, --- HEART_in - HEART_out AS HEART, OGKATU_in - OGKATU_out AS OGKATU, FBLMAM_in - FBLMAM_out AS FBLMAM, VZUSSO_in - VZUSSO_out AS VZUSSO, FBLBTA_in - FBLBTA_out AS FBLBTA, --- OGKASP_in - OGKASP_out AS OGKASP, PD9_in - PD9_out AS PD9, STAND_in - STAND_out AS STAND, SUPER_in - SUPER_out AS SUPER, TITAN_in - TITAN_out AS TITAN, --- CALSCD_in - CALSCD_out AS CALSCD, CALMAX_in - CALMAX_out AS CALMAX, OGKANR_in - OGKANR_out AS OGKANR, IMP1_in - IMP1_out AS IMP1, IMP2_in - IMP2_out AS IMP2, --- IMP3_in - IMP3_out AS IMP3 FROM ships_in x left outer join ships_out y on x.wallet = y.wallet group by 1,2,3,4,5,6,7,8,9,10,11,12,13,14,15,16,17,18,19,20,21,22,23,24,25,26,27,28,29,30,31,32,33,34,35,36,37,38,39,40,41,42,43,44,45,46,47,48,49,50,51,52 ), faction_fleet AS ( Select x.wallet as wallet, -----VWAP---------------------------------------- zeroifnull(OPALJ)* OPALJ_vwap as OPALJ_vwap_total, zeroifnull(PXFIVE)* PXFIVE_vwap as PXFIVE_vwap_total, zeroifnull(VZUSOP)* VZUSOP_vwap as VZUSOP_vwap_total, zeroifnull(CALCH)* CALCH_vwap as CALCH_vwap_total, zeroifnull(OGKATP)* OGKATP_vwap as OGKATP_vwap_total, zeroifnull(PXFOUR)* PXFOUR_vwap as PXFOUR_vwap_total, zeroifnull(OPALJJ)* OPALJJ_vwap as OPALJJ_vwap_total, zeroifnull(FBLBPL)* FBLBPL_vwap as FBLBPL_vwap_total, zeroifnull(CALG)* CALG_vwap as CALG_vwap_total, zeroifnull(PFFOUR)* PFFOUR_vwap as PFFOUR_vwap_total, zeroifnull(FBLEGR)* FBLEGR_vwap as FBLEGR_vwap_total, zeroifnull(OM)* OM_vwap as OM_vwap_total, zeroifnull(PREIGHT)* PREIGHT_vwap as PREIGHT_vwap_total, zeroifnull(VZUSAM)* VZUSAM_vwap as VZUSAM_vwap_total, zeroifnull(PCNINE)* PCNINE_vwap as PCNINE_vwap_total, zeroifnull(PCELEVEN)* PCELEVEN_vwap as PCELEVEN_vwap_total, zeroifnull(CHI)* CHI_vwap as CHI_vwap_total, zeroifnull(OGKAJA)* OGKAJA_vwap as OGKAJA_vwap_total, zeroifnull(CALEV)* CALEV_vwap as CALEV_vwap_total, zeroifnull(TUFAFE)* TUFAFE_vwap as TUFAFE_vwap_total, zeroifnull(FBLBEA)* FBLBEA_vwap as FBLBEA_vwap_total, zeroifnull(PXSIX)* PXSIX_vwap as PXSIX_vwap_total, zeroifnull(FBLEBO)* FBLEBO_vwap as FBLEBO_vwap_total, zeroifnull(FBLETR)* FBLETR_vwap as FBLETR_vwap_total, zeroifnull(FBLAIR)* FBLAIR_vwap as FBLAIR_vwap_total, zeroifnull(OGKAMK)* OGKAMK_vwap as OGKAMK_vwap_total, zeroifnull(FBLEUN)* FBLEUN_vwap as FBLEUN_vwap_total, zeroifnull(ARC)* ARC_vwap as ARC_vwap_total, zeroifnull(FBLLOW)* FBLLOW_vwap as FBLLOW_vwap_total, zeroifnull(THRILL)* THRILL_vwap as THRILL_vwap_total, zeroifnull(VZUSBA)* VZUSBA_vwap as VZUSBA_vwap_total, zeroifnull(CALMED)* CALMED_vwap as CALMED_vwap_total, zeroifnull(OPALBB)* OPALBB_vwap as OPALBB_vwap_total, zeroifnull(CALATS)* CALATS_vwap as CALATS_vwap_total, zeroifnull(PR6)* PR6_vwap as PR6_vwap_total, zeroifnull(HEART)* HEART_vwap as HEART_vwap_total, zeroifnull(OGKATU)* OGKATU_vwap as OGKATU_vwap_total, zeroifnull(FBLMAM)* FBLMAM_vwap as FBLMAM_vwap_total, zeroifnull(VZUSSO)* VZUSSO_vwap as VZUSSO_vwap_total, zeroifnull(FBLBTA)* FBLBTA_vwap as FBLBTA_vwap_total, zeroifnull(OGKASP)* OGKASP_vwap as OGKASP_vwap_total, zeroifnull(PD9)* PD9_vwap as PD9_vwap_total, zeroifnull(STAND)* STAND_vwap as STAND_vwap_total, zeroifnull(SUPER)* SUPER_vwap as SUPER_vwap_total, zeroifnull(TITAN)* TITAN_vwap as TITAN_vwap_total, zeroifnull(CALSCD)* CALSCD_vwap as CALSCD_vwap_total, zeroifnull(CALMAX)* CALMAX_vwap as CALMAX_vwap_total, zeroifnull(OGKANR)* OGKANR_vwap as OGKANR_vwap_total, zeroifnull(IMP1)* IMP1_vwap as IMP1_vwap_total, zeroifnull(IMP2)* IMP2_vwap as IMP2_vwap_total, zeroifnull(IMP3)* IMP3_vwap as IMP3_vwap_total, ---- x.OPALJ*OPALJ_ammo + x.PXFIVE*PXFIVE_ammo + x.VZUSOP*VZUSOP_ammo + x.CALCH*CALCH_ammo + x.OGKATP*OGKATP_ammo + x.PXFOUR*PXFOUR_ammo + x.OPALJJ*OPALJJ_ammo + x.FBLBPL*FBLBPL_ammo + x.CALG*CALG_ammo + x.PFFOUR*PFFOUR_ammo + x.FBLEGR*FBLEGR_ammo + x.OM*OM_ammo + x.PREIGHT*PREIGHT_ammo + x.VZUSAM*VZUSAM_ammo + x.PCNINE*PCNINE_ammo + x.PCELEVEN*PCELEVEN_ammo + x.CHI*CHI_ammo + x.OGKAJA*OGKAJA_ammo + x.CALEV*CALEV_ammo + x.TUFAFE*TUFAFE_ammo + x.FBLBEA*FBLBEA_ammo + x.PXSIX*PXSIX_ammo + x.FBLEBO*FBLEBO_ammo + x.FBLETR*FBLETR_ammo + x.FBLAIR*FBLAIR_ammo + x.OGKAMK*OGKAMK_ammo + x.FBLEUN*FBLEUN_ammo + x.ARC*ARC_ammo + x.FBLLOW*FBLLOW_ammo + x.THRILL*THRILL_ammo + x.VZUSBA*VZUSBA_ammo + x.CALMED*CALMED_ammo + x.OPALBB*OPALBB_ammo + x.CALATS*CALATS_ammo + x.PR6*PR6_ammo + x.HEART*HEART_ammo + x.OGKATU*OGKATU_ammo + x.FBLMAM*FBLMAM_ammo + x.VZUSSO*VZUSSO_ammo + x.FBLBTA*FBLBTA_ammo + x.OGKASP*OGKASP_ammo + x.PD9*PD9_ammo + x.STAND*STAND_ammo + x.SUPER*SUPER_ammo + x.TITAN*TITAN_ammo + x.CALSCD*CALSCD_ammo + x.CALMAX*CALMAX_ammo + x.OGKANR*OGKANR_ammo + x.IMP1*IMP1_ammo + x.IMP2*IMP2_ammo + x.IMP3*IMP3_ammo as ammo_daily_burn, x.OPALJ*OPALJ_food + x.PXFIVE*PXFIVE_food + x.VZUSOP*VZUSOP_food + x.CALCH*CALCH_food + x.OGKATP*OGKATP_food + x.PXFOUR*PXFOUR_food + x.OPALJJ*OPALJJ_food + x.FBLBPL*FBLBPL_food + x.CALG*CALG_food + x.PFFOUR*PFFOUR_food + x.FBLEGR*FBLEGR_food + x.OM*OM_food + x.PREIGHT*PREIGHT_food + x.VZUSAM*VZUSAM_food + x.PCNINE*PCNINE_food + x.PCELEVEN*PCELEVEN_food + x.CHI*CHI_food + x.OGKAJA*OGKAJA_food + x.CALEV*CALEV_food + x.TUFAFE*TUFAFE_food + x.FBLBEA*FBLBEA_food + x.PXSIX*PXSIX_food + x.FBLEBO*FBLEBO_food + x.FBLETR*FBLETR_food + x.FBLAIR*FBLAIR_food + x.OGKAMK*OGKAMK_food + x.FBLEUN*FBLEUN_food + x.ARC*ARC_food + x.FBLLOW*FBLLOW_food + x.THRILL*THRILL_food + x.VZUSBA*VZUSBA_food + x.CALMED*CALMED_food + x.OPALBB*OPALBB_food + x.CALATS*CALATS_food + x.PR6*PR6_food + x.HEART*HEART_food + x.OGKATU*OGKATU_food + x.FBLMAM*FBLMAM_food + x.VZUSSO*VZUSSO_food + x.FBLBTA*FBLBTA_food + x.OGKASP*OGKASP_food + x.PD9*PD9_food + x.STAND*STAND_food + x.SUPER*SUPER_food + x.TITAN*TITAN_food + x.CALSCD*CALSCD_food + x.CALMAX*CALMAX_food + x.OGKANR*OGKANR_food + x.IMP1*IMP1_food + x.IMP2*IMP2_food + x.IMP3*IMP3_food as food_daily_burn, x.OPALJ*OPALJ_fuel + x.PXFIVE*PXFIVE_fuel + x.VZUSOP*VZUSOP_fuel + x.CALCH*CALCH_fuel + x.OGKATP*OGKATP_fuel + x.PXFOUR*PXFOUR_fuel + x.OPALJJ*OPALJJ_fuel + x.FBLBPL*FBLBPL_fuel + x.CALG*CALG_fuel + x.PFFOUR*PFFOUR_fuel + x.FBLEGR*FBLEGR_fuel + x.OM*OM_fuel + x.PREIGHT*PREIGHT_fuel + x.VZUSAM*VZUSAM_fuel + x.PCNINE*PCNINE_fuel + x.PCELEVEN*PCELEVEN_fuel + x.CHI*CHI_fuel + x.OGKAJA*OGKAJA_fuel + x.CALEV*CALEV_fuel + x.TUFAFE*TUFAFE_fuel + x.FBLBEA*FBLBEA_fuel + x.PXSIX*PXSIX_fuel + x.FBLEBO*FBLEBO_fuel + x.FBLETR*FBLETR_fuel + x.FBLAIR*FBLAIR_fuel + x.OGKAMK*OGKAMK_fuel + x.FBLEUN*FBLEUN_fuel + x.ARC*ARC_fuel + x.FBLLOW*FBLLOW_fuel + x.THRILL*THRILL_fuel + x.VZUSBA*VZUSBA_fuel + x.CALMED*CALMED_fuel + x.OPALBB*OPALBB_fuel + x.CALATS*CALATS_fuel + x.PR6*PR6_fuel + x.HEART*HEART_fuel + x.OGKATU*OGKATU_fuel + x.FBLMAM*FBLMAM_fuel + x.VZUSSO*VZUSSO_fuel + x.FBLBTA*FBLBTA_fuel + x.OGKASP*OGKASP_fuel + x.PD9*PD9_fuel + x.STAND*STAND_fuel + x.SUPER*SUPER_fuel + x.TITAN*TITAN_fuel + x.CALSCD*CALSCD_fuel + x.CALMAX*CALMAX_fuel + x.OGKANR*OGKANR_fuel + x.IMP1*IMP1_fuel + x.IMP2*IMP2_fuel + x.IMP3*IMP3_fuel as fuel_daily_burn, x.OPALJ*OPALJ_tool + x.PXFIVE*PXFIVE_tool + x.VZUSOP*VZUSOP_tool + x.CALCH*CALCH_tool + x.OGKATP*OGKATP_tool + x.PXFOUR*PXFOUR_tool + x.OPALJJ*OPALJJ_tool + x.FBLBPL*FBLBPL_tool + x.CALG*CALG_tool + x.PFFOUR*PFFOUR_tool + x.FBLEGR*FBLEGR_tool + x.OM*OM_tool + x.PREIGHT*PREIGHT_tool + x.VZUSAM*VZUSAM_tool + x.PCNINE*PCNINE_tool + x.PCELEVEN*PCELEVEN_tool + x.CHI*CHI_tool + x.OGKAJA*OGKAJA_tool + x.CALEV*CALEV_tool + x.TUFAFE*TUFAFE_tool + x.FBLBEA*FBLBEA_tool + x.PXSIX*PXSIX_tool + x.FBLEBO*FBLEBO_tool + x.FBLETR*FBLETR_tool + x.FBLAIR*FBLAIR_tool + x.OGKAMK*OGKAMK_tool + x.FBLEUN*FBLEUN_tool + x.ARC*ARC_tool + x.FBLLOW*FBLLOW_tool + x.THRILL*THRILL_tool + x.VZUSBA*VZUSBA_tool + x.CALMED*CALMED_tool + x.OPALBB*OPALBB_tool + x.CALATS*CALATS_tool + x.PR6*PR6_tool + x.HEART*HEART_tool + x.OGKATU*OGKATU_tool + x.FBLMAM*FBLMAM_tool + x.VZUSSO*VZUSSO_tool + x.FBLBTA*FBLBTA_tool + x.OGKASP*OGKASP_tool + x.PD9*PD9_tool + x.STAND*STAND_tool + x.SUPER*SUPER_tool + x.TITAN*TITAN_tool + x.CALSCD*CALSCD_tool + x.CALMAX*CALMAX_tool + x.OGKANR*OGKANR_tool + x.IMP1*IMP1_tool + x.IMP2*IMP2_tool + x.IMP3*IMP3_tool as tool_daily_burn, --- (OPALJ_vwap_total + PXFIVE_vwap_total + VZUSOP_vwap_total + CALCH_vwap_total + OGKATP_vwap_total + PXFOUR_vwap_total + OPALJJ_vwap_total + FBLBPL_vwap_total + CALG_vwap_total + PFFOUR_vwap_total + FBLEGR_vwap_total + OM_vwap_total + PREIGHT_vwap_total + VZUSAM_vwap_total + PCNINE_vwap_total + PCELEVEN_vwap_total + CHI_vwap_total + OGKAJA_vwap_total + CALEV_vwap_total + TUFAFE_vwap_total + FBLBEA_vwap_total + PXSIX_vwap_total + FBLEBO_vwap_total + FBLETR_vwap_total + FBLAIR_vwap_total + OGKAMK_vwap_total + FBLEUN_vwap_total + ARC_vwap_total + FBLLOW_vwap_total + THRILL_vwap_total + VZUSBA_vwap_total + CALMED_vwap_total + OPALBB_vwap_total + CALATS_vwap_total + PR6_vwap_total + HEART_vwap_total + OGKATU_vwap_total + FBLMAM_vwap_total + VZUSSO_vwap_total + FBLBTA_vwap_total + OGKASP_vwap_total + PD9_vwap_total + STAND_vwap_total + SUPER_vwap_total + TITAN_vwap_total + CALSCD_vwap_total + CALMAX_vwap_total + OGKANR_vwap_total + IMP1_vwap_total + IMP2_vwap_total + IMP3_vwap_total) as FF_vwap_total, ----------------------------- ---EARNINGS---------------------- zeroifnull(OPALJ)* OPALJ_rewards as OPALJ_rewards_total, zeroifnull(PXFIVE)* PXFIVE_rewards as PXFIVE_rewards_total, zeroifnull(VZUSOP)* VZUSOP_rewards as VZUSOP_rewards_total, zeroifnull(CALCH)* CALCH_rewards as CALCH_rewards_total, zeroifnull(OGKATP)* OGKATP_rewards as OGKATP_rewards_total, zeroifnull(PXFOUR)* PXFOUR_rewards as PXFOUR_rewards_total, zeroifnull(OPALJJ)* OPALJJ_rewards as OPALJJ_rewards_total, zeroifnull(FBLBPL)* FBLBPL_rewards as FBLBPL_rewards_total, zeroifnull(CALG)* CALG_rewards as CALG_rewards_total, zeroifnull(PFFOUR)* PFFOUR_rewards as PFFOUR_rewards_total, zeroifnull(FBLEGR)* FBLEGR_rewards as FBLEGR_rewards_total, zeroifnull(OM)* OM_rewards as OM_rewards_total, zeroifnull(PREIGHT)* PREIGHT_rewards as PREIGHT_rewards_total, zeroifnull(VZUSAM)* VZUSAM_rewards as VZUSAM_rewards_total, zeroifnull(PCNINE)* PCNINE_rewards as PCNINE_rewards_total, zeroifnull(PCELEVEN)* PCELEVEN_rewards as PCELEVEN_rewards_total, zeroifnull(CHI)* CHI_rewards as CHI_rewards_total, zeroifnull(OGKAJA)* OGKAJA_rewards as OGKAJA_rewards_total, zeroifnull(CALEV)* CALEV_rewards as CALEV_rewards_total, zeroifnull(TUFAFE)* TUFAFE_rewards as TUFAFE_rewards_total, zeroifnull(FBLBEA)* FBLBEA_rewards as FBLBEA_rewards_total, zeroifnull(PXSIX)* PXSIX_rewards as PXSIX_rewards_total, zeroifnull(FBLEBO)* FBLEBO_rewards as FBLEBO_rewards_total, zeroifnull(FBLETR)* FBLETR_rewards as FBLETR_rewards_total, zeroifnull(FBLAIR)* FBLAIR_rewards as FBLAIR_rewards_total, zeroifnull(OGKAMK)* OGKAMK_rewards as OGKAMK_rewards_total, zeroifnull(FBLEUN)* FBLEUN_rewards as FBLEUN_rewards_total, zeroifnull(ARC)* ARC_rewards as ARC_rewards_total, zeroifnull(FBLLOW)* FBLLOW_rewards as FBLLOW_rewards_total, zeroifnull(THRILL)* THRILL_rewards as THRILL_rewards_total, zeroifnull(VZUSBA)* VZUSBA_rewards as VZUSBA_rewards_total, zeroifnull(CALMED)* CALMED_rewards as CALMED_rewards_total, zeroifnull(OPALBB)* OPALBB_rewards as OPALBB_rewards_total, zeroifnull(CALATS)* CALATS_rewards as CALATS_rewards_total, zeroifnull(PR6)* PR6_rewards as PR6_rewards_total, zeroifnull(HEART)* HEART_rewards as HEART_rewards_total, zeroifnull(OGKATU)* OGKATU_rewards as OGKATU_rewards_total, zeroifnull(FBLMAM)* FBLMAM_rewards as FBLMAM_rewards_total, zeroifnull(VZUSSO)* VZUSSO_rewards as VZUSSO_rewards_total, zeroifnull(FBLBTA)* FBLBTA_rewards as FBLBTA_rewards_total, zeroifnull(OGKASP)* OGKASP_rewards as OGKASP_rewards_total, zeroifnull(PD9)* PD9_rewards as PD9_rewards_total, zeroifnull(STAND)* STAND_rewards as STAND_rewards_total, zeroifnull(SUPER)* SUPER_rewards as SUPER_rewards_total, zeroifnull(TITAN)* TITAN_rewards as TITAN_rewards_total, zeroifnull(CALSCD)* CALSCD_rewards as CALSCD_rewards_total, zeroifnull(CALMAX)* CALMAX_rewards as CALMAX_rewards_total, zeroifnull(OGKANR)* OGKANR_rewards as OGKANR_rewards_total, zeroifnull(IMP1)* IMP1_rewards as IMP1_rewards_total, zeroifnull(IMP2)* IMP2_rewards as IMP2_rewards_total, zeroifnull(IMP3)* IMP3_rewards as IMP3_rewards_total, --- (OPALJ_rewards_total + PXFIVE_rewards_total + VZUSOP_rewards_total + CALCH_rewards_total + OGKATP_rewards_total + PXFOUR_rewards_total + OPALJJ_rewards_total + FBLBPL_rewards_total + CALG_rewards_total + PFFOUR_rewards_total + FBLEGR_rewards_total + OM_rewards_total + PREIGHT_rewards_total + VZUSAM_rewards_total + PCNINE_rewards_total + PCELEVEN_rewards_total + CHI_rewards_total + OGKAJA_rewards_total + CALEV_rewards_total + TUFAFE_rewards_total + FBLBEA_rewards_total + PXSIX_rewards_total + FBLEBO_rewards_total + FBLETR_rewards_total + FBLAIR_rewards_total + OGKAMK_rewards_total + FBLEUN_rewards_total + ARC_rewards_total + FBLLOW_rewards_total + THRILL_rewards_total + VZUSBA_rewards_total + CALMED_rewards_total + OPALBB_rewards_total + CALATS_rewards_total + PR6_rewards_total + HEART_rewards_total + OGKATU_rewards_total + FBLMAM_rewards_total + VZUSSO_rewards_total + FBLBTA_rewards_total + OGKASP_rewards_total + PD9_rewards_total + STAND_rewards_total + SUPER_rewards_total + TITAN_rewards_total + CALSCD_rewards_total + CALMAX_rewards_total + OGKANR_rewards_total + IMP1_rewards_total + IMP2_rewards_total + IMP3_rewards_total) as FF_rewards_total FROM ships_staked x, ships_data, r4_data ), faction_claims as ( SELECT x.wallet as wallet, zeroifnull(cs1_in) - zeroifnull(cs1_out) as cs1, zeroifnull(cs2_in) - zeroifnull(cs2_out) as cs2, zeroifnull(cs3_in) - zeroifnull(cs3_out) as cs3, zeroifnull(cs4_in) - zeroifnull(cs4_out) as cs4, zeroifnull(cs5_in) - zeroifnull(cs5_out) as cs5, zeroifnull(IMP1_in) - zeroifnull(IMP1_out) as IMP1, zeroifnull(IMP2_in) - zeroifnull(IMP2_out) as IMP2, zeroifnull(IMP3_in) - zeroifnull(IMP3_out) as IMP3, cs1 * 32 + CS2 * 243 + CS3 * 1024 + CS4 * 3125 + CS5 * 7776 + IMP1 * 928.8 + IMP2 * 2624.4 + IMP3 * 29700 as FC_vwap, cs1 * 2160 + cs2 * 11002 + cs3 * 28382 + cs4 * 82296 + cs5 * 214675 + IMP1 * 22299.15 + IMP2 * 62984.91 + IMP3 * 712900.86 as daily_fuel, cs1 * 1454 + cs2 * 11923 + cs3 * 24883 + cs4 * 77890 + cs5 * 268344 + IMP1 * 22713.36 + IMP2 * 64154.86 + IMP3 * 726143 as daily_food, cs1 * 1426 + cs2 * 12384 + cs3 * 25546 + cs4 * 74952 + cs5 * 272808 + IMP1 * 23539.61 + IMP2 * 66488.63 + IMP3 * 752558.02 as daily_ammo, cs1 * 2102 + cs2 * 12845 + cs3 * 26496 + cs4 * 82296 + cs5 * 263894 + IMP1 * 20547.87 + IMP2 * 58038.34 + IMP3 * 656912.65 as daily_tools FROM cs_staking_in x left outer join cs_staking_out y on x.wallet = y.wallet group by 1,2,3,4,5,6,7,8,9,10,11,12,13,14 ), r4_selling AS ( SELECT --* --DATE(block_timestamp) AS date, sum(case when value:parsed:info:authority = 'CPnhL83FXTgvRXXtZ2SvDfvNMiJWCqG9rFS75gqoyucP' then value:parsed:info:tokenAmount:uiAmount --when value:parsed:info:authority = signers[0]::string --then value:parsed:info:tokenAmount:uiAmount*-1 end) AS amount from solana.core.fact_events, lateral flatten(input => inner_instruction:instructions) WHERE program_id = 'traderDnaR5w6Tcoi3NFm53i48FTDNbGjBSZwWXDRrg' AND value:parsed:info:mint = 'fueL3hBZjLLLJHiFH9cqZoozTG3XQZ53diwFPwbzNim' AND signers[0] NOT IN ('NPCxfjPxh6pvRJbGbWZjxfkqWfGBvKkqPbtiJar3mom') AND succeeded = 'true' AND block_timestamp > current_date-7 --group by 1 ), final AS ( SELECT --* wallet as wallet, zeroifnull(x.FF_vwap_total) as FF_VWAP, zeroifnull(y.FC_vwap) as FC_vwap, zeroifnull(y.daily_fuel) as FC_fuel, zeroifnull(y.daily_food) as FC_food, zeroifnull(y.daily_ammo) as FC_ammo, zeroifnull(y.daily_tools) as FC_tool, zeroifnull(x.fuel_daily_burn) as FF_fuel, zeroifnull(x.food_daily_burn) as FF_food, zeroifnull(x.ammo_daily_burn) as FF_ammo, zeroifnull(x.tool_daily_burn) as FF_tool, zeroifnull(x.FF_rewards_total) as FF_rewards FROM faction_fleet x full outer join faction_claims y USING (wallet) --on x.wallet = y.wallet --where x.wallet = y.wallet --group by 1,2,3,4,5,6,7,8,9,10,11,12,13,14,15 order by FF_VWAP desc ) --SELECT * FROM PVP --/* SELECT --* wallet, zeroifnull(FF_VWAP) as FF_VWAP, --FF_rewards - FF_fuel*fuel_price - FF_food*food_price - FF_ammo*ammo_price - FF_tool*tool_price as FF_earnings_atlas, (zeroifnull(FF_rewards) - zeroifnull(FF_fuel)*fuel_price - zeroifnull(FF_food)*food_price - zeroifnull(FF_ammo)*ammo_price - zeroifnull(FF_tool)*tool_price) * atlas_price as FF_earnings, --case when FF_VWAP > 0 then FF_earnings_usd*365*100 / FF_VWAP ELSE 0 end as FF_ROI, zeroifnull(FC_VWAP) as FC_VWAP, --FC_fuel*fuel_price + FC_food*food_price + FC_ammo*ammo_price + FC_tool*tool_price as FC_earnings_atlas, (zeroifnull(FC_fuel)*fuel_price + zeroifnull(FC_food)*food_price + zeroifnull(FC_ammo)*ammo_price + zeroifnull(FC_tool)*tool_price) * atlas_price as FC_earnings, --case when FC_VWAP > 0 then FC_earnings_usd*365*100 / FC_VWAP ELSE 0 end as FC_ROI, zeroifnull(y.polis_locked) as POLIS, --est_pvp, zeroifnull(est_PVP) * daily_polis / (total_pvp * 1000000) * polis_price as polis_locker_earnings, zeroifnull(z.atlas_locked) as ATLAS, zeroifnull(z.atlas_locked) * daily_atlas / (total_atlas *1000000) * polis_price as atlas_locker_earnings, -------------------------------------- --FF_VWAP + FC_VWAP as VWAP_total, --FF_earnings_atlas + FC_earnings_atlas as earnings_atlas_total, FF_earnings + FC_earnings + polis_locker_earnings + atlas_locker_earnings as total_earnings --case when VWAP_total > 0 then earnings_usd_total*365*100 / VWAP_total ELSE 0 end as ROI_total from final x full outer join polis_locked y USING (wallet) --on x.wallet = y.wallet full outer join atlas_locked z USING (wallet) --on x.wallet = z.wallet left outer join r4_prices left outer join atlas_price left outer join polis_price full outer join pvp a USING (wallet) --on x.wallet = a.wallet left outer join polis_locker group by 1,2,3,4,5,6,7,8,9,10--,11,12,13 order by total_earnings desc --*/

Query	Creator	Created
My mining solana	Opod Fan Club	Dec 13, 2023
My PlayerProfile solana	Opod Fan Club	Dec 13, 2023
My mining average solana	Opod Fan Club	Dec 13, 2023
My crafting solana	Opod Fan Club	Dec 13, 2023
My crafting average solana	Opod Fan Club	Dec 13, 2023
Earnings leaderboard solana swaps	Opod Fan Club	Dec 13, 2023
My scanning solana	Opod Fan Club	Dec 13, 2023
myWallet-general-ledger solana	Opod Fan Club	Dec 20, 2023

Opod Fan Club

3 Members