-- forked from 92e76205-f87a-49aa-b0c1-f17e752436a2 WITH vars AS ( SELECT CAST ('2021-04-23' AS DATE) AS program_start_date, CAST ('2022-03-14' AS DATE) AS new_tiers_start_date, '0x875773784Af8135eA0ef43b5a374AaD105c5D39e'::bytea AS idle_token, '0xc00e94cb662c3520282e6f5717214004a7f26888'::bytea AS comp_token, '0x4215606a720477178AdFCd5A59775C63138711e8'::bytea AS fee_address_0, '0x69a62c24f16d4914a48919613e8ee330641bcb94'::bytea AS fee_address_1, '0xbecc659bfc6edca552fa1a67451cc6b38a0108e4'::bytea AS fee_address_2, '0xfb3bd022d5dacf95ee28a6b07825d4ff9c5b3814'::bytea AS treasury_address, '0xdac17f958d2ee523a2206206994597c13d831ec7'::bytea AS usdt_underlying_token, -- USDT '0xa0b86991c6218b36c1d19d4a2e9eb0ce3606eb48'::bytea AS usdc_underlying_token, -- USDC '0x6b175474e89094c44da98b954eedeac495271d0f'::bytea AS dai_underlying_token, -- DAI '0x57ab1ec28d129707052df4df418d58a2d46d5f51'::bytea AS susd_underlying_token, -- SUSD '0x0000000000085d4780b73119b644ae5ecd22b376'::bytea AS tusd_underlying_token, -- TUSD '0x2260fac5e5542a773aa44fbcfedf7c193bc2c599'::bytea AS wbtc_underlying_token, -- WBTC '0xC02aaA39b223FE8D0A0e5C4F27eAD9083C756Cc2'::bytea AS weth_underlying_token, -- WETH '0x3024656ae91d7bf724f613c314bc56030ba2344c'::bytea AS b2b_vesting_contract_address ), rewards_tiers AS ( SELECT 100000000 as balance,0.2 as multiplier UNION SELECT 250000000 as balance,0.25 as multiplier UNION SELECT 500000000 as balance,0.3 as multiplier UNION SELECT 1000000000 as balance,0.35 as multiplier UNION SELECT 99999999999 as balance,0.5 as multiplier ), rewards_tiers_new AS ( SELECT 5000000 as balance,0.1 as multiplier UNION SELECT 10000000 as balance,0.15 as multiplier UNION SELECT 50000000 as balance,0.2 as multiplier UNION SELECT 100000000 as balance,0.25 as multiplier UNION SELECT 200000000 as balance,0.3 as multiplier UNION SELECT 400000000 as balance,0.35 as multiplier UNION SELECT 700000000 as balance,0.4 as multiplier UNION SELECT 1000000000 as balance,0.45 as multiplier UNION SELECT 99999999999 as balance,0.5 as multiplier ), /* idle_price AS ( SELECT * FROM prices."usd" WHERE contract_address='0x875773784Af8135eA0ef43b5a374AaD105c5D39e' ORDER BY minute DESC ), */ idle_price AS ( SELECT * FROM ethereum.core.fact_hourly_token_prices WHERE token_address = '0x875773784Af8135eA0ef43b5a374AaD105c5D39e' ORDER BY hour DESC ), deposits_redeems_all AS ( SELECT 'idleDAIYield' as token,(SELECT dai_underlying_token FROM vars) as underlying,'DEPOSIT' as action,_amount as "amountOriginal",_amount/1000000000000000000 as amount,_amount/NULLIF("output_mintedTokens",0) as tokenPrice,_referral,call_block_time,call_tx_hash,contract_address FROM idle_v4."IdleDAI_v4_Yield_call_mintIdleToken" WHERE _amount>0 UNION SELECT 'idleDAIYield' as token,(SELECT dai_underlying_token FROM vars) as underlying,'REDEEM' as action,"output_redeemedTokens" as "amountOriginal",-"output_redeemedTokens"/1000000000000000000 as amount,"output_redeemedTokens"/NULLIF(_amount,0) as tokenPrice,'' as _referral,call_block_time,call_tx_hash,contract_address FROM idle_v4."IdleDAI_v4_Yield_call_redeemIdleToken" WHERE "output_redeemedTokens">0 UNION SELECT 'idleUSDCYield' as token,(SELECT usdc_underlying_token FROM vars) as underlying,'DEPOSIT' as action,_amount as "amountOriginal",_amount/1000000 as amount,_amount/NULLIF("output_mintedTokens"/1000000000000,0) as tokenPrice,_referral,call_block_time,call_tx_hash,contract_address FROM idle_v4."IdleUSDC_v4_Yield_call_mintIdleToken" WHERE _amount>0 UNION SELECT 'idleUSDCYield' as token,(SELECT usdc_underlying_token FROM vars) as underlying,'REDEEM' as action,"output_redeemedTokens" as "amountOriginal",-"output_redeemedTokens"/1000000 as amount,"output_redeemedTokens"/NULLIF(_amount/1000000000000,0) as tokenPrice,'' as _referral,call_block_time,call_tx_hash,contract_address FROM idle_v4."IdleUSDC_v4_Yield_call_redeemIdleToken" WHERE "output_redeemedTokens">0 UNION SELECT 'idleUSDTYield' as token,(SELECT usdt_underlying_token FROM vars) as underlying,'DEPOSIT' as action,_amount as "amountOriginal",_amount/1000000 as amount,_amount/NULLIF("output_mintedTokens"/1000000000000,0) as tokenPrice,_referral,call_block_time,call_tx_hash,contract_address FROM idle_v4."IdleUSDT_v4_Yield_call_mintIdleToken" WHERE _amount>0 UNION SELECT 'idleUSDTYield' as token,(SELECT usdt_underlying_token FROM vars) as underlying,'REDEEM' as action,"output_redeemedTokens" as "amountOriginal",-"output_redeemedTokens"/1000000 as amount,"output_redeemedTokens"/NULLIF(_amount/1000000000000,0) as tokenPrice,'' as _referral,call_block_time,call_tx_hash,contract_address FROM idle_v4."IdleUSDT_v4_Yield_call_redeemIdleToken" WHERE "output_redeemedTokens">0 UNION SELECT 'idleSUSDYield' as token,(SELECT susd_underlying_token FROM vars) as underlying,'DEPOSIT' as action,_amount as "amountOriginal",_amount/1000000000000000000 as amount,_amount/NULLIF("output_mintedTokens",0) as tokenPrice,_referral,call_block_time,call_tx_hash,contract_address FROM idle_v4."IdleSUSD_v4_Yield_call_mintIdleToken" WHERE _amount>0 UNION SELECT 'idleSUSDYield' as token,(SELECT susd_underlying_token FROM vars) as underlying,'REDEEM' as action,"output_redeemedTokens" as "amountOriginal",-"output_redeemedTokens"/1000000000000000000 as amount,"output_redeemedTokens"/NULLIF(_amount,0) as tokenPrice,'' as _referral,call_block_time,call_tx_hash,contract_address FROM idle_v4."IdleSUSD_v4_Yield_call_redeemIdleToken" WHERE "output_redeemedTokens">0 UNION SELECT 'idleTUSDYield' as token,(SELECT tusd_underlying_token FROM vars) as underlying,'DEPOSIT' as action,_amount as "amountOriginal",_amount/1000000000000000000 as amount,_amount/NULLIF("output_mintedTokens",0) as tokenPrice,_referral,call_block_time,call_tx_hash,contract_address FROM idle_v4."IdleTUSD_v4_Yield_call_mintIdleToken" WHERE _amount>0 UNION SELECT 'idleTUSDYield' as token,(SELECT tusd_underlying_token FROM vars) as underlying,'REDEEM' as action,"output_redeemedTokens" as "amountOriginal",-"output_redeemedTokens"/1000000000000000000 as amount,"output_redeemedTokens"/NULLIF(_amount,0) as tokenPrice,'' as _referral,call_block_time,call_tx_hash,contract_address FROM idle_v4."IdleTUSD_v4_Yield_call_redeemIdleToken" WHERE "output_redeemedTokens">0 UNION SELECT 'idleWBTCYield' as token,(SELECT wbtc_underlying_token FROM vars) as underlying,'DEPOSIT' as action,_amount as "amountOriginal",_amount/100000000 as amount,_amount/NULLIF("output_mintedTokens"/10000000000,0) as tokenPrice,_referral,call_block_time,call_tx_hash,contract_address FROM idle_v4."IdleWBTC_v4_Yield_call_mintIdleToken" WHERE _amount>0 UNION SELECT 'idleWBTCYield' as token,(SELECT wbtc_underlying_token FROM vars) as underlying,'REDEEM' as action,"output_redeemedTokens" as "amountOriginal",-"output_redeemedTokens"/100000000 as amount,"output_redeemedTokens"/NULLIF(_amount/10000000000,0) as tokenPrice,'' as _referral,call_block_time,call_tx_hash,contract_address FROM idle_v4."IdleWBTC_v4_Yield_call_redeemIdleToken" WHERE "output_redeemedTokens">0 -- UNION SELECT 'idleWETHYield' as token,'DEPOSIT' as action,_amount/1000000000000000000 as amount,_amount/"output_mintedTokens" as tokenPrice,_referral,call_block_time,call_tx_hash,contract_address FROM idle_v4."IdleWETH_v4_Yield_call_mintIdleToken" WHERE _amount>0 -- UNION SELECT 'idleWETHYield' as token,'REDEEM' as action,-"output_redeemedTokens"/1000000000000000000 as amount,"output_redeemedTokens"/_amount as tokenPrice,'' as _referral,call_block_time,call_tx_hash,contract_address FROM idle_v4."IdleWETH_v4_Yield_call_redeemIdleToken" WHERE "output_redeemedTokens">0 ), idleTokens_last_prices AS ( SELECT token,tokenprice,call_block_time FROM deposits_redeems_all t1 WHERE action='DEPOSIT' AND call_block_time=(SELECT call_block_time FROM deposits_redeems_all t2 WHERE t2."action"='DEPOSIT' AND t1."token"=t2."token" ORDER BY call_block_time DESC LIMIT 1) ), deposits_redeems AS ( SELECT * FROM deposits_redeems_all WHERE action<>'DEPOSIT' -- OR _referral='\x0000000000000000000000000000000000000000' OR (_referral<>'' AND _referral<>'\x' AND _referral<>'\x0000000000000000000000000000000000000000') -- OR (account='\x4aac17d48e4ccb4e1c53cafbe4934fee899009c4') -- Shapeshift router ), token_transfers AS ( SELECT 'idleDAIYield' as token,'TRANSFER' as action,-value/1000000000000000000 as amount,0 AS tokenPrice,"evt_block_time" as call_block_time,"evt_tx_hash" as call_tx_hash,contract_address,"from" as account FROM idle_v4."IdleDAI_v4_Yield_evt_Transfer" WHERE idle_v4."IdleDAI_v4_Yield_evt_Transfer"."evt_tx_hash" NOT IN (SELECT "call_tx_hash" FROM idle_v4."IdleDAI_v4_Yield_call_mintIdleToken") AND idle_v4."IdleDAI_v4_Yield_evt_Transfer"."evt_tx_hash" NOT IN (SELECT "call_tx_hash" FROM idle_v4."IdleDAI_v4_Yield_call_redeemIdleToken") /* UNION SELECT 'idleUSDCYield' as token,'TRANSFER' as action,-value/1000000000000000000 as amount,0 AS tokenPrice,"evt_block_time" as call_block_time,"evt_tx_hash" as call_tx_hash,contract_address,"from" as account FROM idle_v4."IdleUSDC_v4_Yield_evt_Transfer" WHERE idle_v4."IdleUSDC_v4_Yield_evt_Transfer"."evt_tx_hash" NOT IN (SELECT "call_tx_hash" FROM idle_v4."IdleUSDC_v4_Yield_call_mintIdleToken") AND idle_v4."IdleUSDC_v4_Yield_evt_Transfer"."evt_tx_hash" NOT IN (SELECT "call_tx_hash" FROM idle_v4."IdleUSDC_v4_Yield_call_redeemIdleToken") UNION SELECT 'idleUSDTYield' as token,'TRANSFER' as action,-value/1000000000000000000 as amount,0 AS tokenPrice,"evt_block_time" as call_block_time,"evt_tx_hash" as call_tx_hash,contract_address,"from" as account FROM idle_v4."IdleUSDT_v4_Yield_evt_Transfer" WHERE idle_v4."IdleUSDT_v4_Yield_evt_Transfer"."evt_tx_hash" NOT IN (SELECT "call_tx_hash" FROM idle_v4."IdleUSDT_v4_Yield_call_mintIdleToken") AND idle_v4."IdleUSDT_v4_Yield_evt_Transfer"."evt_tx_hash" NOT IN (SELECT "call_tx_hash" FROM idle_v4."IdleUSDT_v4_Yield_call_redeemIdleToken") UNION SELECT 'idleSUSDYield' as token,'TRANSFER' as action,-value/1000000000000000000 as amount,0 AS tokenPrice,"evt_block_time" as call_block_time,"evt_tx_hash" as call_tx_hash,contract_address,"from" as account FROM idle_v4."IdleSUSD_v4_Yield_evt_Transfer" WHERE idle_v4."IdleSUSD_v4_Yield_evt_Transfer"."evt_tx_hash" NOT IN (SELECT "call_tx_hash" FROM idle_v4."IdleSUSD_v4_Yield_call_mintIdleToken") AND idle_v4."IdleSUSD_v4_Yield_evt_Transfer"."evt_tx_hash" NOT IN (SELECT "call_tx_hash" FROM idle_v4."IdleSUSD_v4_Yield_call_redeemIdleToken") UNION SELECT 'idleTUSDYield' as token,'TRANSFER' as action,-value/1000000000000000000 as amount,0 AS tokenPrice,"evt_block_time" as call_block_time,"evt_tx_hash" as call_tx_hash,contract_address,"from" as account FROM idle_v4."IdleTUSD_v4_Yield_evt_Transfer" WHERE idle_v4."IdleTUSD_v4_Yield_evt_Transfer"."evt_tx_hash" NOT IN (SELECT "call_tx_hash" FROM idle_v4."IdleTUSD_v4_Yield_call_mintIdleToken") AND idle_v4."IdleTUSD_v4_Yield_evt_Transfer"."evt_tx_hash" NOT IN (SELECT "call_tx_hash" FROM idle_v4."IdleTUSD_v4_Yield_call_redeemIdleToken") UNION SELECT 'idleWBTCYield' as token,'TRANSFER' as action,-value/1000000000000000000 as amount,0 AS tokenPrice,"evt_block_time" as call_block_time,"evt_tx_hash" as call_tx_hash,contract_address,"from" as account FROM idle_v4."IdleWBTC_v4_Yield_evt_Transfer" WHERE idle_v4."IdleWBTC_v4_Yield_evt_Transfer"."evt_tx_hash" NOT IN (SELECT "call_tx_hash" FROM idle_v4."IdleWBTC_v4_Yield_call_mintIdleToken") AND idle_v4."IdleWBTC_v4_Yield_evt_Transfer"."evt_tx_hash" NOT IN (SELECT "call_tx_hash" FROM idle_v4."IdleWBTC_v4_Yield_call_redeemIdleToken") */ ), deposits_accounts AS ( SELECT account,token,underlying,action,amount,tokenprice,_referral,call_block_time,call_tx_hash,contract_address FROM ( SELECT t."from" as "account", dr.*, t."evt_index", ROW_NUMBER() OVER (PARTITION BY call_tx_hash,amount,action,underlying,token ORDER BY t."evt_index" DESC) as row_num FROM deposits_redeems dr LEFT JOIN erc20."ERC20_evt_Transfer" t ON t."evt_tx_hash"=dr."call_tx_hash" AND t."contract_address"=dr."underlying" AND t."value"=ABS(dr."amountOriginal") WHERE dr."action"='DEPOSIT' ) as tab WHERE row_num=1 ), redeems_accounts AS ( SELECT account,token,underlying,action,amount,tokenprice,_referral,call_block_time,call_tx_hash,contract_address FROM ( SELECT t."to" as "account", dr.*, t."evt_index", ROW_NUMBER() OVER (PARTITION BY call_tx_hash,amount,action,underlying,token ORDER BY t."evt_index" ASC) as row_num FROM deposits_redeems dr LEFT JOIN erc20."ERC20_evt_Transfer" t ON t."evt_tx_hash"=dr."call_tx_hash" AND t."contract_address"=dr."underlying" AND t."value"=ABS(dr."amountOriginal") WHERE dr."action"='REDEEM' ) as tab WHERE row_num=1 ), deposit_redeems_accounts AS ( SELECT * FROM deposits_accounts WHERE account<>'\x0000000000000000000000000000000000000000' UNION SELECT * FROM redeems_accounts WHERE account<>'\x0000000000000000000000000000000000000000' -- GROUP BY token, underlying, action, amount, tokenprice, _referral, contract_address, call_tx_hash, call_block_time, account ), deposit_redeems_transfers AS ( SELECT account,token,underlying,action,amount,tokenprice,_referral,call_block_time,call_tx_hash,contract_address FROM deposits_accounts UNION SELECT account,token,underlying,action,amount,tokenprice,_referral,call_block_time,call_tx_hash,contract_address FROM redeems_accounts UNION SELECT account,token,'' as underlying,action,amount,tokenprice,'' as _referral,call_block_time,call_tx_hash,contract_address FROM token_transfers ), transfers_with_balances AS ( SELECT token,action,amount,balance,tokenprice,account,_referral,call_tx_hash,call_block_time FROM ( SELECT *, MAX(CASE WHEN balance_sum < 0 THEN -balance_sum ELSE 0 end) OVER (PARTITION BY account,token ORDER BY call_block_time ASC) + balance_sum AS balance FROM ( SELECT *, SUM(amount) OVER (PARTITION BY account,token ORDER BY call_block_time ASC) as balance_sum FROM deposit_redeems_transfers ) as tab ) as tab ), accounts_with_balances AS ( SELECT *, weighted_balance_num/balance_weight_denom as monthly_weighted_balance, ROW_NUMBER() OVER (PARTITION BY account,token ORDER BY call_block_time DESC) as row_num FROM ( SELECT *, CAST(CONCAT(SUBSTRING(CAST(call_block_time as VARCHAR),0,8),'-01') as DATE) as month, SUM(balance_duration) OVER (PARTITION BY account,token ORDER BY call_block_time ASC) as balance_weight_denom, SUM(balance*balance_duration) OVER (PARTITION BY account,token ORDER BY call_block_time ASC) as weighted_balance_num FROM ( SELECT *, (EXTRACT(SECONDS FROM time_interval) + (EXTRACT(MINUTES from time_interval) * 60) + (EXTRACT(HOURS from time_interval) * 3600) + (EXTRACT(DAYS from time_interval) * 86400)) as balance_duration FROM ( SELECT token,account,action,amount,balance,call_block_time,tokenprice, -- (SELECT SUM(t3.amount*t3.tokenprice)/SUM(t3.amount) FROM transfers_with_balances t3 WHERE t3.action='DEPOSIT' AND t3."token"=t1."token" AND t3."account"=t1."account" AND t3.balance>0 AND t3."call_block_time"<=t1."call_block_time" AND t3."call_block_time">COALESCE((SELECT call_block_time FROM transfers_with_balances t5 WHERE t5."balance"=0 AND t5."token"=t1."token" AND t5."account"=t1."account" AND t5."call_block_time"'DEPOSIT' THEN (SELECT _referral FROM transfers_with_balances t4 WHERE action='DEPOSIT' AND t4."token"=t1."token" AND t4."account"=t1."account" AND t4."call_block_time"<=t1."call_block_time" ORDER BY call_block_time DESC LIMIT 1) ELSE _referral END) as _referral, COALESCE((LEAD(call_block_time,1) OVER (PARTITION BY account,token ORDER BY call_block_time ASC)),NOW())-call_block_time as time_interval, call_tx_hash FROM transfers_with_balances t1 -- WHERE (SELECT balance FROM transfers_with_balances t2 WHERE t2."token"=t1."token" AND t2."account"=t1."account" ORDER BY call_block_time DESC LIMIT 1)>0 ) tab ) tab WHERE balance_duration>0 ) tab --ORDER BY call_block_time ASC ), collected_fees_old AS ( SELECT dr.account, dr.token, dr.underlying, transfers.contract_address, transfers."value", p.price, p.decimals, b.monthly_weighted_balance, (SELECT price FROM prices."usd" WHERE contract_address=(SELECT idle_token FROM vars) AND minute=date_trunc('day', transfers."evt_block_time") LIMIT 1) as idle_real_price, (SELECT SUM(price)/COUNT(0) as price FROM (SELECT price FROM idle_price WHERE minute<=dr.call_block_time ORDER BY minute DESC LIMIT 14400) tab) as idle_avg_price, (SELECT balance FROM rewards_tiers WHERE rewards_tiers.balance>b.monthly_weighted_balance ORDER BY rewards_tiers.balance ASC LIMIT 1) as tier_balance, (SELECT multiplier FROM rewards_tiers WHERE rewards_tiers.balance>b.monthly_weighted_balance ORDER BY rewards_tiers.balance ASC LIMIT 1) as tier_multiplier, (transfers."value"/10^p.decimals)*p.price AS collected_fees_usd, ((transfers."value"/10^p.decimals)*p.price)*(SELECT multiplier FROM rewards_tiers WHERE rewards_tiers.balance>b.monthly_weighted_balance ORDER BY rewards_tiers.balance ASC LIMIT 1) AS collected_fees_shared_usd, ((transfers."value"/10^p.decimals)*p.price)*(SELECT multiplier FROM rewards_tiers WHERE rewards_tiers.balance>b.monthly_weighted_balance ORDER BY rewards_tiers.balance ASC LIMIT 1)/(SELECT SUM(price)/COUNT(0) as price FROM (SELECT price FROM idle_price WHERE minute<=dr.call_block_time ORDER BY minute DESC LIMIT 14400) tab) as collected_rewards, dr.call_tx_hash, dr.call_block_time FROM deposit_redeems_accounts dr INNER JOIN erc20."ERC20_evt_Transfer" transfers ON transfers."evt_tx_hash"=dr."call_tx_hash" INNER JOIN prices."usd" p ON p.contract_address=transfers.contract_address AND p.minute = date_trunc('day', transfers."evt_block_time") INNER JOIN accounts_with_balances b ON b.account=dr.account AND b.token=dr.token AND b.call_block_time=dr.call_block_time WHERE dr.action='REDEEM' AND transfers."evt_block_time">=(SELECT program_start_date FROM vars) AND transfers."evt_block_time"<(SELECT new_tiers_start_date FROM vars) AND (transfers."contract_address"=dr.underlying OR transfers."contract_address"=(SELECT comp_token FROM vars)) AND (transfers."to" IN (SELECT fee_address_0 FROM vars) OR transfers."to" IN (SELECT fee_address_1 FROM vars) OR transfers."to" IN (SELECT fee_address_2 FROM vars)) ), collected_fees_new AS ( SELECT dr.account, dr.token, dr.underlying, transfers.contract_address, transfers."value", p.price, p.decimals, b.monthly_weighted_balance, (SELECT price FROM prices."usd" WHERE contract_address=(SELECT idle_token FROM vars) AND minute=date_trunc('day', transfers."evt_block_time") LIMIT 1) as idle_real_price, (SELECT SUM(price)/COUNT(0) as price FROM (SELECT price FROM idle_price WHERE minute<=dr.call_block_time ORDER BY minute DESC LIMIT 14400) tab) as idle_avg_price, (SELECT balance FROM rewards_tiers_new WHERE rewards_tiers_new.balance>b.monthly_weighted_balance ORDER BY rewards_tiers_new.balance ASC LIMIT 1) as tier_balance, (SELECT multiplier FROM rewards_tiers_new WHERE rewards_tiers_new.balance>b.monthly_weighted_balance ORDER BY rewards_tiers_new.balance ASC LIMIT 1) as tier_multiplier, (transfers."value"/10^p.decimals)*p.price AS collected_fees_usd, ((transfers."value"/10^p.decimals)*p.price)*(SELECT multiplier FROM rewards_tiers_new WHERE rewards_tiers_new.balance>b.monthly_weighted_balance ORDER BY rewards_tiers_new.balance ASC LIMIT 1) AS collected_fees_shared_usd, ((transfers."value"/10^p.decimals)*p.price)*(SELECT multiplier FROM rewards_tiers_new WHERE rewards_tiers_new.balance>b.monthly_weighted_balance ORDER BY rewards_tiers_new.balance ASC LIMIT 1)/(SELECT SUM(price)/COUNT(0) as price FROM (SELECT price FROM idle_price WHERE minute<=dr.call_block_time ORDER BY minute DESC LIMIT 14400) tab) as collected_rewards, dr.call_tx_hash, dr.call_block_time FROM deposit_redeems_accounts dr INNER JOIN erc20."ERC20_evt_Transfer" transfers ON transfers."evt_tx_hash"=dr."call_tx_hash" INNER JOIN prices."usd" p ON p.contract_address=transfers.contract_address AND p.minute = date_trunc('day', transfers."evt_block_time") INNER JOIN accounts_with_balances b ON b.account=dr.account AND b.token=dr.token AND b.call_block_time=dr.call_block_time WHERE dr.action='REDEEM' AND transfers."evt_block_time">=(SELECT program_start_date FROM vars) AND transfers."evt_block_time">=(SELECT new_tiers_start_date FROM vars) AND (transfers."contract_address"=dr.underlying OR transfers."contract_address"=(SELECT comp_token FROM vars)) AND (transfers."to" IN (SELECT fee_address_0 FROM vars) OR transfers."to" IN (SELECT fee_address_1 FROM vars) OR transfers."to" IN (SELECT fee_address_2 FROM vars)) ), collected_fees AS ( SELECT * FROM collected_fees_old UNION SELECT * FROM collected_fees_new ), latest_accounts_with_balances AS ( SELECT * FROM accounts_with_balances WHERE row_num=1 AND (_referral<>'' AND _referral<>'\x' AND _referral<>'\x0000000000000000000000000000000000000000')-- AND balance>0 ), cumulative_collected_fees AS ( SELECT account, token, COALESCE(SUM(collected_fees_usd),0) as collected_fees, COALESCE(SUM(collected_fees_shared_usd),0) as shared_fees, COALESCE(SUM(collected_rewards),0) as idle_rewards FROM collected_fees GROUP BY account,token ), accounts_latest_balances_rewards AS ( SELECT b.*, COALESCE(ccf."collected_fees",0) as collected_fees, COALESCE(ccf."shared_fees",0) as shared_fees, COALESCE(ccf."idle_rewards",0) as idle_rewards FROM latest_accounts_with_balances b LEFT JOIN cumulative_collected_fees ccf ON ccf."account"=b."account" AND ccf."token"=b."token" ), idle_rewards AS ( SELECT token, account, r._referral, balance, --redeemable_balance, monthly_weighted_balance, -- interests, collected_fees, shared_fees, idle_rewards -- sr."rewards" as paid_rewards, --(total_rewards-sr."rewards") as net_rewards FROM accounts_latest_balances_rewards r -- LEFT OUTER JOIN --sent_rewards sr -- ON --r."_referral"=sr."_referral" -- WHERE -- idle_rewards>0 ORDER BY idle_rewards DESC, balance DESC ), proxy_contracts AS ( SELECT ProxyCreated."proxy",createAndCall."initData",createAndCall."call_block_time" FROM idle_dao."MinimalInitializableProxyFactory_evt_ProxyCreated" ProxyCreated INNER JOIN idle_dao."MinimalInitializableProxyFactory_call_createAndCall" createAndCall ON ProxyCreated."implementation"=createAndCall."target" AND ProxyCreated."evt_block_number"=createAndCall."call_block_number" WHERE ProxyCreated."implementation"=(SELECT b2b_vesting_contract_address FROM vars) ), sent_rewards AS ( SELECT b._referral, COALESCE(SUM(t."value")/10^18,0) as rewards FROM (SELECT DISTINCT _referral FROM accounts_latest_balances_rewards) as b INNER JOIN proxy_contracts p ON CAST(p."initData" as VARCHAR) LIKE CONCAT('%',REPLACE(CAST(b._referral as VARCHAR),'\x',''),'%') INNER JOIN erc20."ERC20_evt_Transfer" t ON t."from"=(SELECT treasury_address FROM vars) AND t."to"=p.proxy AND t."contract_address"=(SELECT idle_token FROM vars) GROUP BY b._referral ), idle_cumulative_rewards AS ( SELECT _referral, SUM(balance) as total_balance, SUM(collected_fees) as protocol_fees, SUM(shared_fees) as shared_fees, SUM(idle_rewards) as partner_rewards FROM idle_rewards ir GROUP BY _referral ), idle_cumulative_net_rewards AS ( SELECT icr.*, COALESCE(sr."rewards",0) as paid_rewards, GREATEST((icr."partner_rewards"-COALESCE(sr."rewards",0)),0) as net_rewards FROM idle_cumulative_rewards icr LEFT OUTER JOIN sent_rewards sr ON icr."_referral"=sr."_referral" ) SELECT * FROM idle_cumulative_net_rewards --WHERE _referral <> '\xfb3bd022d5dacf95ee28a6b07825d4ff9c5b3814' WHERE _referral = '\xe140bb5f424a53e0687bfc10f6845a5672d7e242' ORDER BY net_rewards DESC;

Query	Creator	Created
2023-03-08 02:43 PM	MatteoIdleDAO-kTAUno	Mar 8, 2023
Spool BY deposits copy	MatteoIdleDAO-kTAUno	Mar 8, 2023

MatteoIdleDAO-kTAUno