select

--*

date_trunc('day', block_timestamp::date) as date,

sum(zeroifnull(value:instructions[0]:parsed:info:amount)) / POW(10,8) AS POLIS_locked00,

sum(zeroifnull(value:instructions[1]:parsed:info:amount)) / POW(10,8) AS POLIS_locked01,

sum(zeroifnull(value:instructions[2]:parsed:info:amount)) / POW(10,8) AS POLIS_locked02,

sum(zeroifnull(value:instructions[3]:parsed:info:amount)) / POW(10,8) AS POLIS_locked03,

sum(zeroifnull(value:instructions[4]:parsed:info:amount)) / POW(10,8) AS POLIS_locked04,

sum(zeroifnull(value:instructions[5]:parsed:info:amount)) / POW(10,8) AS POLIS_locked05,

sum(zeroifnull(value:instructions[6]:parsed:info:amount)) / POW(10,8) AS POLIS_locked06,

sum(zeroifnull(value:instructions[7]:parsed:info:amount)) / POW(10,8) AS POLIS_locked07,

sum(zeroifnull(value:instructions[8]:parsed:info:amount)) / POW(10,8) AS POLIS_locked08,

sum(zeroifnull(value:instructions[9]:parsed:info:amount)) / POW(10,8) AS POLIS_locked09,

(POLIS_locked00 + POLIS_locked01 + POLIS_locked02 + POLIS_locked03 + POLIS_locked04 + POLIS_locked05 + POLIS_locked06 + POLIS_locked07 + POLIS_locked08 + POLIS_locked09) / 2

AS POLIS_locked

from solana.core.fact_transactions, lateral flatten(input => inner_instructions)

WHERE --tx_id = 'KXooDgk6DMEwRr4V7nXhpGwfUM1MXRVH3V4WiK7gJPfUTHPdW1ugQeJcGgnmXDdtqst88i6tmojwfCP75EtUKgZ'

instructions[0]:programId = 'gateVwTnKyFrE8nxUUgfzoZTPKgJQZUbLsEidpG4Dp2'

--AND value:owner != '6ZRCB7AAqGre6c72PRz3MHLC73VMYvJ8bi9KHf1HFpNk'

--AND value:owner = signers[0]::string

--AND value:uiTokenAmount:uiAmount > 10000000

AND log_messages::string LIKE '%Proxy%'

--AND value:mint IN ('Ev3xUhc1Leqi4qR2E5VoG9pcxCvHHmnAaSRVPg485xAT', '267DbhCypYzvTqv72ZG5UKHeFu56qXFsuoz3rw832eC5', '9czEqEZ4EkRt7N3HWDcw9qqwys3xRRjGdbn8Jhk8Khwj', 'AkNbg12E9PatjkiAWJ3tAbM479gtcoA1gi6Joa925WKi', 'CWxNX9sTexuqvQefqskhP9f6AP5C8hq2VNkicRseqAT5', '2iMhgB4pbdKvwJHVyitpvX5z1NBNypFonUgaSAt9dtDt', '9ABNesWj7NVdkDgko7UjVaDp5pTh8a6wfXHLWz3bZM6W', '7V9C2XUQgCb31n7hGKqKGu4ENcvqXhJLJzU77CAQtXhw', 'DdpXnnYsyUQgJby8TDHbmPwkKyGF4U6bXwCXTQZsrfKP', '9MvZS3TVfv4DZL9W2pT12po384aBHf7wi89KXQ9Z7uwW', 'FpwV1Da6BZJnYPr1JSLUm14UwBmZHA7J5WLY4TXgbde8', 'HzBx8PP86pyPrrboTHqPYWhxnEB5vXDHDBP8femWfPTS', '2bCgKTo11QayWBy6QryHZMqZL2ZgWd5LEAZKiTGQi4g7', 'H2jHqvXA2oxSpEp6dKkpK7WeszQEdFW5n25mNfrJFAc1', '5f1jUARhtSypVA4uTpgpLp76WYGdB2dGr8zMbh4WjYRf', '9ifQ16N5DdUFoejCwsgR73ihUwadAe3srCo9HhQe2zL2', 'DsJHgpnNovjJ981QJJnqMggexAekNawbSavfV1QuTpis', 'HJBmBYyGR8z1oajAM4jiK46uobuxeJoKDYpFwzWHBvhb', '4txpjHspP4usEsQTr3AcrpyHVjN4fi3d4taM6cmKJnd1', 'HsdbLvZrEgN2ZhsrZs5ag4F2FNFCHjjuXPfbVAhkeJBZ', '6SqLuwHNRC1qjo9KATLKJLszFHMWyYaNxDXraCEUtfdR', '8RveLFEyteyL1vbCKPQJxjf3JT1ACyrzs46TXbJStrHG', '7M6RHgPiHXiZAin5ManH63cLYGt3miQ54KaGynUQoERS', 'HqPN13pLUVJRiuGSsKjfWZvGKAagK98PshuKu51bnG4E', 'Fw8PqtznYtg4swMk7Yjj89Tsj23u5CJLfW5Bk8ro4G1s', 'FMHHwUB6amLWYhWxtiZHC2g5azy9usPTLMq46N3HEgFU', '9zrgra3XQkZPt8XNs4fowbqmj7B8bBx76aEmsKSnm9BW', 'EbLBLN44BVLjifLNBbchXFr8QjEkAGYENKuNEaDuyVPL', '7Xs3yt9eJPuEexZrKSGVbQMXHwWUKHGeDZnM4ZksZmyS', 'FTk1E5UoWkiZEUttCWSYYaVokxWNNp3yJ42HbNDCAkdt', 'FFkPvwLDYuKDW9eAAr5UNfuX3U9PcTGeSk7gqNX7EpNc', '4gR3ChfdQxR4BTbgeWSdf6b8kD8Ysu6WBAQqtJ9oLgbF', '8pPDsMNcz4m8jaajFMFXHGcvaeVeiQhcenvSD6a4XNyq', '2XYd22LSFGxN7kWgoEeaXVZqgrsPeQLHLEgNhnS12Mny', 'Fys8J53cquYsg5zYfeZStVGNwM9FopFw8QFkiE9CCR1J', '6HzZJwrcuBBmrE7SLDfxheZGAD3NYJ531C9JsNesL9BP', 'J8Q6jYsrhhaeczyPBo9xzVyy4GpfCnJwj14LJn2HnuKp', '6Zj61HuX1E7SCUCf9WsKXw1jdJCobAwK4RSjZvbv35tM', 'HjFijcGWKgfDwGpFX2rqFwEU9jtEgFuRQAJe1ERXFsA3')

AND date_trunc('day', block_timestamp) > '2022-07-20' AND date_trunc('day', block_timestamp) < current_date --'2022-07-31'

group by 1

order by 1 desc

),

polis_rewards AS (

select

--*