Query editor
withall_chains as(select evt_tx_hash,evt_block_time,_reward as amount,_token as tokenfrom magpie_arbitrum.vlMGPBaseRewarder_evt_RewardPaid-- where _user in (-- 0xA746B456A137Ac6acC413F3C16D3EF2eA2D0514C,-- 0x39054e5dBD799366AcC1DE66d8c466Db17334ac1,-- 0x0CdB34e6a4D635142BB92fe403D38F636BbB77b8)union allselect evt_tx_hash,evt_block_time,_reward as amount,_token as tokenfrom magpie_bnb.vlMGPBaseRewarder_evt_RewardPaid-- where _user in (-- 0xA746B456A137Ac6acC413F3C16D3EF2eA2D0514C,-- 0x39054e5dBD799366AcC1DE66d8c466Db17334ac1,-- 0x0CdB34e6a4D635142BB92fe403D38F636BbB77b8)union allselect evt_tx_hash,evt_block_time,rewardAmount as amount,rewardToken as tokenfrom magpie_arbitrum.vlStreamRewarder_evt_RewardQueuedunion allselect evt_tx_hash,evt_block_time,rewardAmount as amount,rewardToken as tokenfrom magpie_bnb.vlStreamRewarder_evt_RewardQueued),prices as (select avg(token_price_usd_raw) as price,token_address from dex.prices_latest where token_addressin (select distinct token from all_chains)group by 2),reward_income as (selectcasewhen token in (select distinct token_address from prices)then (amount)*pricewhen token = 0x509fd25ee2ac7833a017f17ee8a6fb4aaf947876then amount/1e18*0.07134else 0 end as reward,date_trunc('month',evt_block_time) as month,--Token,price*1e18 as price, amount/1e18 as amountfrom all_chainsleft join prices on cast(all_chains.token as varchar) = cast(prices.token_address as varchar)order by 2 desc),monthly as (select month,sum(amount) as reward,token from reward_incomegroup by 1,3),final as (select reward, month, token, casewhen token=0x3BC5AC0dFdC871B365d159f728dd1B9A0B5481E8 then 'SD'when token=0xd06716e1ff2e492cc5034c2e81805562dd3b45fa then 'mgp'when token=0x0465aad9da170798433f4ab7fa7ec8b9b9bf0bb1 then 'mpendle'when token=0xa61F74247455A40b01b0559ff6274441FAfa22A3 then 'mgp'when token=0x2ac2b254bc18cd4999f64773a966e4f4869c34ee then 'pnp'when token=0x5012c90f14d190607662ca8344120812aaa2639d then 'pnp'when token=0x2b5d9adea07b590b638ffc165792b2c610eda649 then 'CKP'when token=0x54BDBF3cE36f451Ec61493236b8E6213ac87c0f6 then 'rdp'when token =0x27c073e8427aa493a90b8dC8b73A89e670FD77bB then 'rdp'when token=0x027a9d301fb747cd972cfb29a63f3bda551dfc5c then 'mwom'