Query editor
WITH rewardsDuration as (select 'avalanche_c' as blockchain,evt_block_time,rewardDuration,evt_block_time + interval '1' second * CAST(rewardDuration AS DOUBLE) as end_time,token,evt_tx_hash from betswirl_avalanche_c.SingleStaking_v1_evt_AddRewardTokenUNION ALLselect 'bnb' as blockchain,evt_block_time,rewardDuration,evt_block_time + interval '1' second * CAST(rewardDuration AS DOUBLE) as end_time,token,evt_tx_hash from betswirl_bnb.SingleStaking_v1_evt_AddRewardTokenUNION ALLselect 'polygon' as blockchain,evt_block_time,rewardDuration,evt_block_time + interval '1' second * CAST(rewardDuration AS DOUBLE) as end_time,token,evt_tx_hash from betswirl_polygon.SingleStaking_v1_evt_AddRewardTokenUNION ALLselect 'arbitrum' as blockchain,evt_block_time,rewardDuration,evt_block_time + interval '1' second * CAST(rewardDuration AS DOUBLE) as end_time,token,evt_tx_hash from betswirl_arbitrum.SingleStaking_v1_evt_AddRewardTokenUNION ALLselect 'avalanche_c' as blockchain,evt_block_time,rewardDuration,evt_block_time + interval '1' second * CAST(rewardDuration AS DOUBLE) as end_time,token,evt_tx_hash from betswirl_avalanche_c.SingleStaking_v2_evt_AddRewardTokenUNION ALLselect 'bnb' as blockchain,evt_block_time,rewardDuration,evt_block_time + interval '1' second * CAST(rewardDuration AS DOUBLE) as end_time,token,evt_tx_hash from betswirl_bnb.SingleStaking_v2_evt_AddRewardTokenUNION ALLselect 'polygon' as blockchain,evt_block_time,rewardDuration,evt_block_time + interval '1' second * CAST(rewardDuration AS DOUBLE) as end_time,token,evt_tx_hash from betswirl_polygon.SingleStaking_v2_evt_AddRewardTokenUNION ALLselect 'arbitrum' as blockchain,evt_block_time,rewardDuration,evt_block_time + interval '1' second * CAST(rewardDuration AS DOUBLE) as end_time,token,evt_tx_hash from betswirl_arbitrum.SingleStaking_v2_evt_AddRewardTokenUNION ALLselect 'ethereum' as blockchain,evt_block_time,rewardDuration,evt_block_time + interval '1' second * CAST(rewardDuration AS DOUBLE) as end_time,token,evt_tx_hash from betswirl_ethereum."8020Staking_v1_evt_AddRewardToken"),rewardsAdded as (SELECT evt_block_time as reward_deposited_date,a.blockchain,x.token,symbol,reward/POW(10,decimals) as reward_amountFROM (SELECT 'avalanche_c' as blockchain,evt_block_time,token,evt_tx_hash,reward FROM betswirl_avalanche_c.SingleStaking_v1_evt_RewardAddedUNION ALLSELECT 'bnb' as blockchain,evt_block_time,token,evt_tx_hash,reward FROM betswirl_bnb.SingleStaking_v1_evt_RewardAddedUNION ALLSELECT 'arbitrum' as blockchain,evt_block_time,token,evt_tx_hash,reward FROM betswirl_arbitrum.SingleStaking_v1_evt_RewardAddedUNION ALLSELECT 'polygon' as blockchain,evt_block_time,token,evt_tx_hash,reward FROM betswirl_polygon.SingleStaking_v1_evt_RewardAddedUNION ALLSELECT 'avalanche_c' as blockchain,evt_block_time,token,evt_tx_hash,reward FROM betswirl_avalanche_c.SingleStaking_v2_evt_RewardAddedUNION ALLSELECT 'bnb' as blockchain,evt_block_time,token,evt_tx_hash,reward FROM betswirl_bnb.SingleStaking_v2_evt_RewardAddedUNION ALLSELECT 'arbitrum' as blockchain,evt_block_time,token,evt_tx_hash,reward FROM betswirl_arbitrum.SingleStaking_v2_evt_RewardAddedUNION ALLSELECT 'polygon' as blockchain,evt_block_time,token,evt_tx_hash,reward FROM betswirl_polygon.SingleStaking_v2_evt_RewardAddedUNION ALLSELECT 'ethereum' as blockchain,evt_block_time,token,evt_tx_hash,reward FROM betswirl_ethereum."8020Staking_v1_evt_RewardAdded") x LEFT JOIN tokens.erc20 a ON a.blockchain = LOWER(x.blockchain) AND a.contract_address = x.token),getPrices as (SELECT minute,symbol,priceFROM prices.usdWHERE symbol IN ('WETH','WBNB','WAVAX','WMATIC')AND contract_address IN (SELECT DISTINCT token FROM rewardsDuration)AND minute >= TIMESTAMP '2023-06-26')SELECT * FROM (SELECT date_trunc('day',reward_deposited_date) as reward_block_date,