-- SELECT DECODED_LOG:order:giveChainId as chainIdSour, -- DECODED_LOG:orderId as orderIdSour, -- FULL_DECODED_LOG:address as address1, -- DECODED_LOG:referralCode as referral, * FROM polygon.core.fact_decoded_event_logs -- WHERE BLOCK_TIMESTAMP > current_date -interval '1 DAYS' -- and referral='4844' SELECT DECODED_LOG:order:giveChainId as chainIdSour, DECODED_LOG:orderId as orderIdSour, FULL_DECODED_LOG:address as address1, DECODED_LOG:referralCode as referral, * FROM avalanche.core.fact_decoded_event_logs WHERE BLOCK_TIMESTAMP > current_date -interval '31 DAYS' and BLOCK_TIMESTAMP < current_date -interval '15 DAYS' and referral='4844' -- SELECT * from bsc.core.ez_decoded_event_logs -- where BLOCK_NUMBER='36026691' -- and tx_hash='0x1e11efe8bb43afc1e68339f562c44267a004e218ffb6b759ced981991318ecc7' -- and origin_to_address=lower('0xc31fc94F3Fd088eE53ac915D6e8a14fF25a23C47') -- ////source polygon and distination bsc -- SELECT DECODED_LOG:orderId as orderId_dist ,* FROM bsc.core.ez_decoded_event_logs -- where tx_hash='0x1e11efe8bb43afc1e68339f562c44267a004e218ffb6b759ced981991318ecc7' -- and event_name='FulfilledOrder' -- ////source poly dis bsc -- select t1.chainIdSour,t1.orderIdSour ,t2.orderId_dist, t1.referral from -- (SELECT -- DECODED_LOG:order:giveChainId as chainIdSour, -- DECODED_LOG:orderId as orderIdSour, -- FULL_DECODED_LOG:address as address1, -- DECODED_LOG:referralCode as referral, * FROM polygon.core.fact_decoded_event_logs -- WHERE BLOCK_TIMESTAMP > current_date - interval '28 DAYS' -- and referral='4844' -- AND event_name='CreatedOrder') as t1 -- join (SELECT DECODED_LOG:orderId as orderId_dist , -- * FROM bsc.core.ez_decoded_event_logs -- -- where BLOCK_TIMESTAMP > current_date -- WHERE BLOCK_TIMESTAMP > current_date - interval '28 DAYS' -- and event_name='FulfilledOrder') as t2 -- ON t1.orderIdSour=t2.orderId_dist --///// join two result -- SELECT T1.col_a, T1.col_b, T2.col_c -- FROM (SELECT col_a, col_b, ...etc...) AS T1 -- JOIN (SELECT col_a, col_c, ...etc...) AS T2 -- ON T1.col_a = T2.col_a -- ////polygon-SOURCE -- SELECT DECODED_LOG:order:giveChainId as chainId,DECODED_LOG:orderId as orderId, -- FULL_DECODED_LOG:address as address1,DECODED_LOG:referralCode as referral, * FROM polygon.core.fact_decoded_event_logs -- -- where BLOCK_TIMESTAMP > current_date - interval '28 DAYS' -- where BLOCK_TIMESTAMP>=current_date -- and event_name='CreatedOrder' -- and contract_address='0xef4fb24ad0916217251f553c0596f8edc630eb66' -- and DECODED_LOG:referralCode='4844' -- ( ///cross-chain on poligon -- SELECT * FROM polygon.defi.ez_bridge_activity -- WHERE -- DESTINATION_CHAIN_ID='56' -- AND ORIGIN_TO_ADDRESS='0x663dc15d3c1ac63ff12e45ab68fea3f0a883c251' -- AND TX_HASH='0xc73bc8af96f0850f8b644e77736447aeae0c7bcf46ae54db4f6db907e48e9a10' -- SELECT * FROM bsc.defi.ez_bridge_activity -- WHERE ORIGIN_TO_ADDRESS ='0xc31fc94f3fd088ee53ac915d6e8a14ff25a23c47' -- AND DESTINATION_CHAIN_ID ='137' -- AND PLATFORM='dln_debridge' -- WHERE ORIGIN_TO_ADDRESS='0xc31fc94F3Fd088eE53ac915D6e8a14fF25a23C47' -- TX_HASH='0x1e11efe8bb43afc1e68339f562c44267a004e218ffb6b759ced981991318ecc7' -- SELECT * FROM polygon.defi.ez_bridge_activity -- where block_number='36026691' -- WHERE DESTINATION_CHAIN_ID IN('1','137','56','10','42161','43114','324') -- where BLOCK_TIMESTAMP > current_date - interval '20 DAYS' -- and tx_hash='0x1e11efe8bb43afc1e68339f562c44267a004e218ffb6b759ced981991318ecc7' -- AND ORIGIN_TO_ADDRESS = '0xe7351fd770a37282b91d153ee690b63579d6dd7f' -- AND BRIDGE_ADDRESS ='0xc31fc94f3fd088ee53ac915d6e8a14ff25a23c47' -- //////////////////////////// -- SELECT * FROM polygon.defi.ez_bridge_activity -- WHERE DESTINATION_CHAIN_ID IN('1','56','10','42161','43114','324') -- AND BLOCK_TIMESTAMP > current_date - interval '30 DAYS' -- AND ORIGIN_TO_ADDRESS = '0x663dc15d3c1ac63ff12e45ab68fea3f0a883c251' -- AND BRIDGE_ADDRESS ='0xef4fb24ad0916217251f553c0596f8edc630eb66' -- ///////////////////////// -- SELECT * FROM ethereum.defi.ez_bridge_activity -- WHERE DESTINATION_CHAIN_ID IN('137','56','10','42161','43114','324') -- AND BLOCK_TIMESTAMP > current_date - interval '30 DAYS' -- AND ORIGIN_TO_ADDRESS = '0x663dc15d3c1ac63ff12e45ab68fea3f0a883c251' -- AND BRIDGE_ADDRESS ='0xef4fb24ad0916217251f553c0596f8edc630eb66' -- ///////////////////////////// -- SELECT * FROM arbitrum.defi.ez_bridge_activity -- WHERE DESTINATION_CHAIN_ID IN('137','56','10','1','43114','324') -- AND BLOCK_TIMESTAMP > current_date - interval '30 DAYS' -- AND ORIGIN_TO_ADDRESS = '0x663dc15d3c1ac63ff12e45ab68fea3f0a883c251' -- AND BRIDGE_ADDRESS ='0xef4fb24ad0916217251f553c0596f8edc630eb66' -- SELECT * FROM avalanche.defi.ez_bridge_activity -- WHERE DESTINATION_CHAIN_ID IN('137','56','10','1','42161','324') -- AND BLOCK_TIMESTAMP > current_date - interval '30 DAYS' -- AND ORIGIN_TO_ADDRESS = '0x663dc15d3c1ac63ff12e45ab68fea3f0a883c251' -- AND BRIDGE_ADDRESS ='0xef4fb24ad0916217251f553c0596f8edc630eb66' -- Polygon: 137 -- BSC: 56 -- Ethereum: 1 -- Arbitrum: 42161 -- Optimisim: 10 -- Avalanche: 43114 -- zkSync: 324 -- sample -- """pip install flipside""" -- from flipside import Flipside -- """Initialize Flipside with your API Key / API Url""" -- flipside = Flipside("", "https://api-v2.flipsidecrypto.xyz") -- sql = """with price as ( -- select hour::date as price_date, -- token_address, -- symbol, -- decimals, -- avg(price) as price -- from polygon.price.ez_hourly_token_prices -- group by 1,2,3,4 -- ) -- , MATIC_price as ( -- select hour::date as price_date, -- token_address, -- symbol, -- decimals, -- avg(price) as MATIC_price -- from polygon.price.ez_hourly_token_prices -- where symbol = 'MATIC' -- group by 1,2,3,4 -- ) -- , base_from_polygon as ( -- select distinct tx_hash -- from polygon.core.fact_token_transfers t -- where 1 = 1 -- and block_timestamp >= '2023-04-01' and block_timestamp < CURRENT_DATE -- and to_address = '0xef4fb24ad0916217251f553c0596f8edc630eb66' -- ) -- , cancelled_tx as ( -- select tx_hash -- FROM polygon.core.fact_event_logs -- where 1 = 1 -- and block_timestamp > '2023-04-01' -- and topics[0] = '0x7d7d1c5b3eadbe275ceb358e65cd57410b35997187258dbaaae42ab6e1405fd8' -- ) -- , from_polygon as ( -- select block_timestamp::date as date, -- 'From' as route_type, -- 'polygon' as chain, -- 'Creation' as tx_type, -- p.symbol, -- raw_amount/pow(10,p.decimals) as amount, -- amount*price as volume_usd, -- origin_from_address as user_address, -- tx_hash -- from polygon.core.fact_token_transfers t -- join price p ON (contract_address = token_address and block_timestamp::date = p.price_date) -- where 1 = 1 -- and block_timestamp >= '2023-04-01' and block_timestamp < CURRENT_DATE -- and tx_hash in (select tx_hash from base_from_polygon) -- qualify row_number() over (partition by tx_hash order by event_index) = 1 -- ) -- , from_polygon_MATIC as ( -- select block_timestamp::date as date, -- 'From' as route_type, -- 'polygon' as chain, -- 'Creation' as tx_type, -- p.symbol, -- amount - 0.5 as real_amount, -- real_amount*MATIC_price as volume_usd, -- origin_from_address as user_address, -- tx_hash -- from polygon.core.ez_MATIC_transfers t -- join MATIC_price p ON (block_timestamp::date = p.price_date) -- where 1 = 1 -- and block_timestamp >= '2023-04-01' and block_timestamp < CURRENT_DATE -- -- tx_hash = '0x0595daadd276a7fa014ca8ac0c83ef07999fecc34559bb35c25bd79a9b779b8d' -- and MATIC_to_address = '0xef4fb24ad0916217251f553c0596f8edc630eb66' -- and real_amount != 0 -- ) -- , to_polygon as ( -- select block_timestamp::date as date, -- 'To' as route_type, -- 'polygon' as chain, -- 'Creation' as tx_type, -- symbol, -- raw_amount/pow(10,decimals) as amount, -- amount*price as volume_usd, -- to_address as user_address, -- tx_hash -- from polygon.core.fact_token_transfers t -- join price p ON (contract_address = token_address and block_timestamp::date = price_date) -- where 1 = 1 -- and block_timestamp >= '2023-04-01' and block_timestamp < CURRENT_DATE -- -- tx_hash = '0x0595daadd276a7fa014ca8ac0c83ef07999fecc34559bb35c25bd79a9b779b8d' -- and ( -- from_address in ('0x555ce236c0220695b68341bc48c68d52210cc35b', '0x55f6265a266f7202835b5661c1da045f39f2e587') -- or origin_to_address = '0xe7351fd770a37282b91d153ee690b63579d6dd7f' -- ) -- and to_address != '0xc31fc94f3fd088ee53ac915d6e8a14ff25a23c47' -- ) -- , to_polygon_MATIC as ( -- select block_timestamp::date as date, -- 'To' as route_type, -- 'polygon' as chain, -- 'Creation' as tx_type, -- 'MATIC' as symbol, -- amount, -- amount_usd as volume_usd, -- MATIC_to_address as user_address, -- tx_hash -- from polygon.core.ez_MATIC_transfers t -- where 1 = 1 -- and block_timestamp >= '2023-04-01' and block_timestamp < CURRENT_DATE -- -- tx_hash = '0x0595daadd276a7fa014ca8ac0c83ef07999fecc34559bb35c25bd79a9b779b8d' -- and MATIC_from_address = '0xe7351fd770a37282b91d153ee690b63579d6dd7f' -- and MATIC_to_address != '0x43de2d77bf8027e25dbd179b491e8d64f38398aa' -- ) -- , to_polygon_others as ( -- select block_timestamp::date as date, -- 'To' as route_type, -- 'polygon' as chain, -- 'Creation' as tx_type, -- symbol, -- raw_amount/pow(10,decimals) as amount, -- amount*price as volume_usd, -- to_address as user_address, -- tx_hash -- from polygon.core.fact_token_transfers t -- join price p ON (contract_address = token_address and block_timestamp::date = price_date) -- where 1 = 1 -- and block_timestamp >= '2023-04-01' and block_timestamp < CURRENT_DATE -- and origin_to_address = '0xc31fc94f3fd088ee53ac915d6e8a14ff25a23c47' -- and from_address = '0xc31fc94f3fd088ee53ac915d6e8a14ff25a23c47' -- and tx_hash not in (select tx_hash from to_polygon) -- and tx_hash not in (select tx_hash from to_polygon_MATIC) -- qualify row_number() over (partition by tx_hash order by event_index desc) = 1 -- ) -- , cancel_polygon as ( -- select block_timestamp::date as date, -- 'From' as route_type, -- 'polygon' as chain, -- 'Cancel' as tx_type, -- symbol, -- raw_amount/pow(10,decimals) as amount, -- amount*price as volume_usd, -- to_address as user_address, -- tx_hash -- from polygon.core.fact_token_transfers -- join price p ON (contract_address = token_address and block_timestamp::date = price_date) -- where block_timestamp > '2023-04-01' -- and tx_hash in (select tx_hash from cancelled_tx) -- and from_address = '0xef4fb24ad0916217251f553c0596f8edc630eb66' -- ) -- , cancel_polygon_MATIC as ( -- select block_timestamp::date as date, -- 'From' as route_type, -- 'polygon' as chain, -- 'Cancel' as tx_type, -- p.symbol, -- amount, -- amount*MATIC_price as volume_usd, -- MATIC_to_address as user_address, -- tx_hash -- from polygon.core.ez_MATIC_transfers -- join MATIC_price p ON (block_timestamp::date = p.price_date) -- where block_timestamp > '2023-04-01' -- and tx_hash in (select tx_hash from cancelled_tx) -- and MATIC_from_address = '0xef4fb24ad0916217251f553c0596f8edc630eb66' -- and amount != 0.5 -- ) -- , base_polygon as ( -- select * from to_polygon -- UNION -- select * from to_polygon_MATIC -- UNION -- select * from to_polygon_others -- UNION -- select * from from_polygon -- UNION -- select * from from_polygon_MATIC -- UNION -- select * from cancel_polygon -- UNION -- select * from cancel_polygon_MATIC -- ) -- , final_polygon as ( -- select * from base_polygon -- order by date desc -- -- date, -- -- route_type || ' ' || chain as type, -- -- zeroifnull(sum(case -- -- when (route_type = 'From' and tx_type != 'Cancel') then 0.5 * MATIC_price -- -- when (route_type = 'From' and tx_type = 'Cancel') then -0.5 * MATIC_price end)) as fee_usd, -- -- sum(case when tx_type != 'Cancel' then volume_usd else -volume_usd end) as amount_usd, -- -- case when fee_usd != 0 then amount_usd *0.04 * 0.01 else 0 end as variable_fee, -- -- fee_usd + variable_fee as total_fee, -- -- count(distinct user_address) as user_count, -- -- count(distinct case when tx_type = 'Cancel' then tx_hash end) as cancel_txs, -- -- count(distinct tx_hash) as all_txs, -- -- all_txs - cancel_txs*2 as tx_count -- deducted creation txs as well -- -- from base_polygon -- -- join MATIC_price on (date = price_date) -- -- where route_type = 'From' -- -- group by 1 -- ) -- select * -- from final_polygon -- """ -- """Run the query against Flipside's query engine and await the results""" -- query_result_set = flipside.query(sql) -- print(query_result_set) -- SELECT DECODED_LOG:order:giveChainId as chainIdSour, -- DECODED_LOG:orderId as orderIdSour, -- FULL_DECODED_LOG:address as address1, -- DECODED_LOG:referralCode as referral, * FROM polygon.core.fact_decoded_event_logs -- WHERE BLOCK_TIMESTAMP > current_date -interval '1 DAYS' -- and referral='4844' -- SELECT DECODED_LOG:order:giveChainId as chainIdSour, -- DECODED_LOG:orderId as orderIdSour, -- FULL_DECODED_LOG:address as address1, -- DECODED_LOG:referralCode as referral, * FROM optimism.core.fact_decoded_event_logs -- WHERE BLOCK_TIMESTAMP > current_date -interval '21 DAYS' -- and BLOCK_TIMESTAMP < current_date -interval '5 DAYS' -- and referral='4844' -- SELECT * from bsc.core.ez_decoded_event_logs -- where BLOCK_NUMBER='36026691' -- and tx_hash='0x1e11efe8bb43afc1e68339f562c44267a004e218ffb6b759ced981991318ecc7' -- and origin_to_address=lower('0xc31fc94F3Fd088eE53ac915D6e8a14fF25a23C47') -- ////source polygon and distination bsc -- SELECT DECODED_LOG:orderId as orderId_dist ,* FROM bsc.core.ez_decoded_event_logs -- where tx_hash='0x1e11efe8bb43afc1e68339f562c44267a004e218ffb6b759ced981991318ecc7' -- and event_name='FulfilledOrder' -- ////source poly dis bsc -- select t1.chainIdSour,t1.orderIdSour ,t2.orderId_dist, t1.referral from -- (SELECT -- DECODED_LOG:order:giveChainId as chainIdSour, -- DECODED_LOG:orderId as orderIdSour, -- FULL_DECODED_LOG:address as address1, -- DECODED_LOG:referralCode as referral, * FROM polygon.core.fact_decoded_event_logs -- WHERE BLOCK_TIMESTAMP > current_date - interval '28 DAYS' -- and referral='4844' -- AND event_name='CreatedOrder') as t1 -- join (SELECT DECODED_LOG:orderId as orderId_dist , -- * FROM bsc.core.ez_decoded_event_logs -- -- where BLOCK_TIMESTAMP > current_date -- WHERE BLOCK_TIMESTAMP > current_date - interval '28 DAYS' -- and event_name='FulfilledOrder') as t2 -- ON t1.orderIdSour=t2.orderId_dist --///// join two result -- SELECT T1.col_a, T1.col_b, T2.col_c -- FROM (SELECT col_a, col_b, ...etc...) AS T1 -- JOIN (SELECT col_a, col_c, ...etc...) AS T2 -- ON T1.col_a = T2.col_a -- ////polygon-SOURCE -- SELECT DECODED_LOG:order:giveChainId as chainId,DECODED_LOG:orderId as orderId, -- FULL_DECODED_LOG:address as address1,DECODED_LOG:referralCode as referral, * FROM polygon.core.fact_decoded_event_logs -- -- where BLOCK_TIMESTAMP > current_date - interval '28 DAYS' -- where BLOCK_TIMESTAMP>=current_date -- and event_name='CreatedOrder' -- and contract_address='0xef4fb24ad0916217251f553c0596f8edc630eb66' -- and DECODED_LOG:referralCode='4844' -- ( ///cross-chain on poligon -- SELECT * FROM polygon.defi.ez_bridge_activity -- WHERE -- DESTINATION_CHAIN_ID='56' -- AND ORIGIN_TO_ADDRESS='0x663dc15d3c1ac63ff12e45ab68fea3f0a883c251' -- AND TX_HASH='0xc73bc8af96f0850f8b644e77736447aeae0c7bcf46ae54db4f6db907e48e9a10' -- SELECT * FROM bsc.defi.ez_bridge_activity -- WHERE ORIGIN_TO_ADDRESS ='0xc31fc94f3fd088ee53ac915d6e8a14ff25a23c47' -- AND DESTINATION_CHAIN_ID ='137' -- AND PLATFORM='dln_debridge' -- WHERE ORIGIN_TO_ADDRESS='0xc31fc94F3Fd088eE53ac915D6e8a14fF25a23C47' -- TX_HASH='0x1e11efe8bb43afc1e68339f562c44267a004e218ffb6b759ced981991318ecc7' -- SELECT * FROM polygon.defi.ez_bridge_activity -- where block_number='36026691' -- WHERE DESTINATION_CHAIN_ID IN('1','137','56','10','42161','43114','324') -- where BLOCK_TIMESTAMP > current_date - interval '20 DAYS' -- and tx_hash='0x1e11efe8bb43afc1e68339f562c44267a004e218ffb6b759ced981991318ecc7' -- AND ORIGIN_TO_ADDRESS = '0xe7351fd770a37282b91d153ee690b63579d6dd7f' -- AND BRIDGE_ADDRESS ='0xc31fc94f3fd088ee53ac915d6e8a14ff25a23c47' -- //////////////////////////// -- SELECT * FROM polygon.defi.ez_bridge_activity -- WHERE DESTINATION_CHAIN_ID IN('1','56','10','42161','43114','324') -- AND BLOCK_TIMESTAMP > current_date - interval '30 DAYS' -- AND ORIGIN_TO_ADDRESS = '0x663dc15d3c1ac63ff12e45ab68fea3f0a883c251' -- AND BRIDGE_ADDRESS ='0xef4fb24ad0916217251f553c0596f8edc630eb66' -- ///////////////////////// -- SELECT * FROM ethereum.defi.ez_bridge_activity -- WHERE DESTINATION_CHAIN_ID IN('137','56','10','42161','43114','324') -- AND BLOCK_TIMESTAMP > current_date - interval '30 DAYS' -- AND ORIGIN_TO_ADDRESS = '0x663dc15d3c1ac63ff12e45ab68fea3f0a883c251' -- AND BRIDGE_ADDRESS ='0xef4fb24ad0916217251f553c0596f8edc630eb66' -- ///////////////////////////// -- SELECT * FROM arbitrum.defi.ez_bridge_activity -- WHERE DESTINATION_CHAIN_ID IN('137','56','10','1','43114','324') -- AND BLOCK_TIMESTAMP > current_date - interval '30 DAYS' -- AND ORIGIN_TO_ADDRESS = '0x663dc15d3c1ac63ff12e45ab68fea3f0a883c251' -- AND BRIDGE_ADDRESS ='0xef4fb24ad0916217251f553c0596f8edc630eb66' -- SELECT * FROM avalanche.defi.ez_bridge_activity -- WHERE DESTINATION_CHAIN_ID IN('137','56','10','1','42161','324') -- AND BLOCK_TIMESTAMP > current_date - interval '30 DAYS' -- AND ORIGIN_TO_ADDRESS = '0x663dc15d3c1ac63ff12e45ab68fea3f0a883c251' -- AND BRIDGE_ADDRESS ='0xef4fb24ad0916217251f553c0596f8edc630eb66' -- Polygon: 137 -- BSC: 56 -- Ethereum: 1 -- Arbitrum: 42161 -- Optimisim: 10 -- Avalanche: 43114 -- zkSync: 324 -- sample -- """pip install flipside""" -- from flipside import Flipside -- """Initialize Flipside with your API Key / API Url""" -- flipside = Flipside("", "https://api-v2.flipsidecrypto.xyz") -- sql = """with price as ( -- select hour::date as price_date, -- token_address, -- symbol, -- decimals, -- avg(price) as price -- from polygon.price.ez_hourly_token_prices -- group by 1,2,3,4 -- ) -- , MATIC_price as ( -- select hour::date as price_date, -- token_address, -- symbol, -- decimals, -- avg(price) as MATIC_price -- from polygon.price.ez_hourly_token_prices -- where symbol = 'MATIC' -- group by 1,2,3,4 -- ) -- , base_from_polygon as ( -- select distinct tx_hash -- from polygon.core.fact_token_transfers t -- where 1 = 1 -- and block_timestamp >= '2023-04-01' and block_timestamp < CURRENT_DATE -- and to_address = '0xef4fb24ad0916217251f553c0596f8edc630eb66' -- ) -- , cancelled_tx as ( -- select tx_hash -- FROM polygon.core.fact_event_logs -- where 1 = 1 -- and block_timestamp > '2023-04-01' -- and topics[0] = '0x7d7d1c5b3eadbe275ceb358e65cd57410b35997187258dbaaae42ab6e1405fd8' -- ) -- , from_polygon as ( -- select block_timestamp::date as date, -- 'From' as route_type, -- 'polygon' as chain, -- 'Creation' as tx_type, -- p.symbol, -- raw_amount/pow(10,p.decimals) as amount, -- amount*price as volume_usd, -- origin_from_address as user_address, -- tx_hash -- from polygon.core.fact_token_transfers t -- join price p ON (contract_address = token_address and block_timestamp::date = p.price_date) -- where 1 = 1 -- and block_timestamp >= '2023-04-01' and block_timestamp < CURRENT_DATE -- and tx_hash in (select tx_hash from base_from_polygon) -- qualify row_number() over (partition by tx_hash order by event_index) = 1 -- ) -- , from_polygon_MATIC as ( -- select block_timestamp::date as date, -- 'From' as route_type, -- 'polygon' as chain, -- 'Creation' as tx_type, -- p.symbol, -- amount - 0.5 as real_amount, -- real_amount*MATIC_price as volume_usd, -- origin_from_address as user_address, -- tx_hash -- from polygon.core.ez_MATIC_transfers t -- join MATIC_price p ON (block_timestamp::date = p.price_date) -- where 1 = 1 -- and block_timestamp >= '2023-04-01' and block_timestamp < CURRENT_DATE -- -- tx_hash = '0x0595daadd276a7fa014ca8ac0c83ef07999fecc34559bb35c25bd79a9b779b8d' -- and MATIC_to_address = '0xef4fb24ad0916217251f553c0596f8edc630eb66' -- and real_amount != 0 -- ) -- , to_polygon as ( -- select block_timestamp::date as date, -- 'To' as route_type, -- 'polygon' as chain, -- 'Creation' as tx_type, -- symbol, -- raw_amount/pow(10,decimals) as amount, -- amount*price as volume_usd, -- to_address as user_address, -- tx_hash -- from polygon.core.fact_token_transfers t -- join price p ON (contract_address = token_address and block_timestamp::date = price_date) -- where 1 = 1 -- and block_timestamp >= '2023-04-01' and block_timestamp < CURRENT_DATE -- -- tx_hash = '0x0595daadd276a7fa014ca8ac0c83ef07999fecc34559bb35c25bd79a9b779b8d' -- and ( -- from_address in ('0x555ce236c0220695b68341bc48c68d52210cc35b', '0x55f6265a266f7202835b5661c1da045f39f2e587') -- or origin_to_address = '0xe7351fd770a37282b91d153ee690b63579d6dd7f' -- ) -- and to_address != '0xc31fc94f3fd088ee53ac915d6e8a14ff25a23c47' -- ) -- , to_polygon_MATIC as ( -- select block_timestamp::date as date, -- 'To' as route_type, -- 'polygon' as chain, -- 'Creation' as tx_type, -- 'MATIC' as symbol, -- amount, -- amount_usd as volume_usd, -- MATIC_to_address as user_address, -- tx_hash -- from polygon.core.ez_MATIC_transfers t -- where 1 = 1 -- and block_timestamp >= '2023-04-01' and block_timestamp < CURRENT_DATE -- -- tx_hash = '0x0595daadd276a7fa014ca8ac0c83ef07999fecc34559bb35c25bd79a9b779b8d' -- and MATIC_from_address = '0xe7351fd770a37282b91d153ee690b63579d6dd7f' -- and MATIC_to_address != '0x43de2d77bf8027e25dbd179b491e8d64f38398aa' -- ) -- , to_polygon_others as ( -- select block_timestamp::date as date, -- 'To' as route_type, -- 'polygon' as chain, -- 'Creation' as tx_type, -- symbol, -- raw_amount/pow(10,decimals) as amount, -- amount*price as volume_usd, -- to_address as user_address, -- tx_hash -- from polygon.core.fact_token_transfers t -- join price p ON (contract_address = token_address and block_timestamp::date = price_date) -- where 1 = 1 -- and block_timestamp >= '2023-04-01' and block_timestamp < CURRENT_DATE -- and origin_to_address = '0xc31fc94f3fd088ee53ac915d6e8a14ff25a23c47' -- and from_address = '0xc31fc94f3fd088ee53ac915d6e8a14ff25a23c47' -- and tx_hash not in (select tx_hash from to_polygon) -- and tx_hash not in (select tx_hash from to_polygon_MATIC) -- qualify row_number() over (partition by tx_hash order by event_index desc) = 1 -- ) -- , cancel_polygon as ( -- select block_timestamp::date as date, -- 'From' as route_type, -- 'polygon' as chain, -- 'Cancel' as tx_type, -- symbol, -- raw_amount/pow(10,decimals) as amount, -- amount*price as volume_usd, -- to_address as user_address, -- tx_hash -- from polygon.core.fact_token_transfers -- join price p ON (contract_address = token_address and block_timestamp::date = price_date) -- where block_timestamp > '2023-04-01' -- and tx_hash in (select tx_hash from cancelled_tx) -- and from_address = '0xef4fb24ad0916217251f553c0596f8edc630eb66' -- ) -- , cancel_polygon_MATIC as ( -- select block_timestamp::date as date, -- 'From' as route_type, -- 'polygon' as chain, -- 'Cancel' as tx_type, -- p.symbol, -- amount, -- amount*MATIC_price as volume_usd, -- MATIC_to_address as user_address, -- tx_hash -- from polygon.core.ez_MATIC_transfers -- join MATIC_price p ON (block_timestamp::date = p.price_date) -- where block_timestamp > '2023-04-01' -- and tx_hash in (select tx_hash from cancelled_tx) -- and MATIC_from_address = '0xef4fb24ad0916217251f553c0596f8edc630eb66' -- and amount != 0.5 -- ) -- , base_polygon as ( -- select * from to_polygon -- UNION -- select * from to_polygon_MATIC -- UNION -- select * from to_polygon_others -- UNION -- select * from from_polygon -- UNION -- select * from from_polygon_MATIC -- UNION -- select * from cancel_polygon -- UNION -- select * from cancel_polygon_MATIC -- ) -- , final_polygon as ( -- select * from base_polygon -- order by date desc -- -- date, -- -- route_type || ' ' || chain as type, -- -- zeroifnull(sum(case -- -- when (route_type = 'From' and tx_type != 'Cancel') then 0.5 * MATIC_price -- -- when (route_type = 'From' and tx_type = 'Cancel') then -0.5 * MATIC_price end)) as fee_usd, -- -- sum(case when tx_type != 'Cancel' then volume_usd else -volume_usd end) as amount_usd, -- -- case when fee_usd != 0 then amount_usd *0.04 * 0.01 else 0 end as variable_fee, -- -- fee_usd + variable_fee as total_fee, -- -- count(distinct user_address) as user_count, -- -- count(distinct case when tx_type = 'Cancel' then tx_hash end) as cancel_txs, -- -- count(distinct tx_hash) as all_txs, -- -- all_txs - cancel_txs*2 as tx_count -- deducted creation txs as well -- -- from base_polygon -- -- join MATIC_price on (date = price_date) -- -- where route_type = 'From' -- -- group by 1 -- ) -- select * -- from final_polygon -- """ -- """Run the query against Flipside's query engine and await the results""" -- query_result_set = flipside.query(sql) -- print(query_result_set)

GhasemHassani