/* TADA | EVM 10.29.24 OVERVIEW a. Get all transactions for addresses provided b. For each transaction hash, determine how many transfers and of what type of token occurred c. Generate a Tag to describe the transaction behavior and assignment for next step tax calculation d. Format data in structure to be uploaded to TADA TO DO 1. Logs - double check no missing transfers 2. Fees - a. split fee for multi-transfer, b. NFT platform fee from NFT_SWAPS (unless in other CTE) 3. DeFi tables TBD - might need ex_dex_swaps, ex_liquid_staking_deposits, ex_liquid_staking_withdrawals 4. Tags - UNI-V3 as stake, not nft-swap */ -- select * from ethereum.defi.ez_liquid_staking_deposits where tx_hash = '0x5ca126dbf2f3fa28f0a1ba80e4bc04aa7873e36787569c9549aebdca7c8a3cea' /* --erc1155_value <> 1 limit 1000 -- price.ez_asset_metadata, fact_transactions, ez_decoded_traces, ez_token_transfers, ez_native_transfers, ez_nft_transfers, ez_dex_swaps with -- -- RD -- ADDIS as (select * from (values -- (lower('0x49f1Bd3683000Cb18926C07571FB063c340480e4'), 'TEST-1') -- , (lower('0x51cbf3Ab037616eA74314baDc3667b29eBD532bc'), 'TEST-2') -- , (lower('0xfd2395fffE86803275E1cC7CeBd7a000380E3b73'), 'TEST-3') -- , (lower('0xCaE85E07E61B6Ad90aaEAfd22Cb5CE52C277fba5'), 'TEST-4') -- , (lower('0x645977e4fF30e74e84BF2610e0460306329FE489'), 'TEST-5') -- , (lower('0x1E769C95742B757e41de0F60A127aD0954123B1B'), 'TEST-6') -- ) as t(addi, label) -- ) -- GALAXY -- https://etherscan.io/accounts/label/galaxy-digital -- https://platform.arkhamintelligence.com/explorer/entity/galaxy-digital ADDIS as (select * from (values (lower('0xD48573cDA0fed7144f2455c5270FFa16Be389d04'), 'TEST-a') -- new; unlabeled defi find -- , (lower('0x4adf44961a7cbe8ee46b2bf04e7198e879677825'), 'TEST-b') -- new; labeled, Flipside, not in Etherscan list -- (lower('0x5797F722b1FeE36e3D2c3481D938d1372bCD99A7'), 'TEST-1') --37k -- , (lower('0x33566c9D8BE6Cf0B23795E0d380E112Be9d75836'), 'TEST-2') -- OTC --19k -- , (lower('0xF4561C710ba450Aab302Ffc3557EE59Bbce94CA6'), 'TEST-3') --7k (appears only transfers) -- , (lower('0x15abb66bA754F05cBC0165A64A11cDed1543dE48'), 'TEST-4') --3k (appears only transfers) -- , (lower('0xb9C3832e688736ed8c8954d158cB6b00FCa6C8F2'), 'TEST-5') --2k (appears only transfers) -- , (lower('0x46f34C24A7bA7a2Ac6DD76c3F09B32D41C144d08'), 'TEST-6') --1k (appears only transfers) -- , (lower('0x220eaDF10315dCB744Cb4C12d3d8d5F3d1028336'), 'TEST-7') -- HTX Deposit --1k -- , (lower('0x6AE55181F90c954993789546956A8453E63B0015'), 'TEST-8') --100 (appears only transfers) -- , (lower('0xb6949a1A9C335cE1b73D490b1e134086Ec5718F5'), 'TEST-9') -- FTX Deposit --100 -- , (lower('0xcaF66b3C3c343064F19c697E53B126cBb9E7Ad99'), 'TEST-10') --50 -- , (lower('0x65729807485f6f7695af863d97d62140b7d69d83'), 'TEST-c') -- new; tbd, Dune -- Galaxy Digital: Blocktower ) as t(addi, label) ) -- Get Transactions for additional info if it is missing in other CTEs (IE method_id, status, tx_fee) , TX as ( select 'transactions' as source, block_timestamp, block_number, tx_hash as hash_tx , origin_function_signature as method_id_tx, from_address, to_address , 'crypto' as asset_type, 'ETH' as symbol, round(tx_fee, 8) as fee , round(tx_fee * (select avg(price) from ethereum.price.ez_prices_hourly where is_native = 'TRUE' and date_trunc('hour', block_timestamp) = hour), 4) as fee_usd , status, 'false' as flag_price from ethereum.core.fact_transactions where (from_address in (select addi from ADDIS) or to_address in (select addi from ADDIS)) ) -- select * from TX order by block_number, hash_tx /* -- Get Crypto transfers from Native table; only one token per chain, as defined below , NATIVES as ( select * from ( select 'native' as source , block_timestamp, block_number, tx_hash , origin_function_signature, trace_index as index , origin_from_address, origin_to_address , from_address, to_address , 'crypto' as asset_type , (case when 'ethereum' in ('ethereum', 'arbitrum', 'base', 'blast', 'optimism') then 'ETH' -- EVM using ETH when 'ethereum' = 'aptos' then 'APT' when 'ethereum' = 'avalanche' then 'AVAX' when 'ethereum' = 'bsc' then 'BNB' when 'ethereum' = 'gnosis' then 'xDAI' -- trace_index = 1, not 0 when 'ethereum' = 'kaia' then 'KLAY' -- trace_index = 1, not 0 when 'ethereum' = 'polygon' then 'POL' -- changed to POL from MATIC after 09/24 else 'TBD' end ) as symbol , amount, (amount_usd / amount) as token_price, amount_usd , (case when 'ethereum' in ('ethereum', 'arbitrum', 'base', 'blast', 'optimism') then '0xc02aaa39b223fe8d0a0e5c4f27ead9083c756cc2' -- WETH when 'ethereum' = 'aptos' then 'TBD' when 'ethereum' = 'avalanche' then 'TBD' when 'ethereum' = 'bsc' then 'TBD' when 'ethereum' = 'gnosis' then 'TBD' -- trace_index = 1, not 0 when 'ethereum' = 'kaia' then 'TBD' -- trace_index = 1, not 0 when 'ethereum' = 'polygon' then 'TBD' -- changed to POL from MATIC after 09/24 else 'TBD' end ) as contract_address -- set as WETH for ethereum, to update for others , 'true' as has_price , iff(origin_from_address in (select addi from ADDIS) or origin_to_address in (select addi from ADDIS), 'native-origin', 'native-ft') as native_type -- ofa/ota higher priority , rank() over (partition by tx_hash order by index) as call_order -- if not ofa/ota, return first call from ethereum.core.ez_native_transfers where from_address in (select addi from ADDIS) or to_address in (select addi from ADDIS) ) where native_type = 'native-origin' or (native_type = 'native-ft' and call_order = 1) ) -- select * from NATIVES limit 10 /* -- Get Crypto transfers (ERC20) from Tokens table; will have all non-Native Crypto transfers , TOKENS as ( select * exclude flag_origin from ( select 'token' as source , block_timestamp, block_number, tx_hash , origin_function_signature--, event_index as index , origin_from_address, origin_to_address , from_address, to_address , 'crypto' as asset_type, symbol, amount , token_price, amount_usd, contract_address , has_price , (case when origin_from_address not in (select addi from ADDIS) then 'fromto' when from_address in (select addi from ADDIS) or to_address in (select addi from ADDIS) then 'origin' else 'ignore' end ) as flag_origin from ethereum.core.ez_token_transfers where (origin_from_address in (select addi from ADDIS) -- did not find any OTA with RD or from_address in (select addi from ADDIS) or to_address in (select addi from ADDIS)) -- added for few missing transfers; introduced SPAM but removed flag_spam ) where flag_origin <> 'ignore' -- removes additional intra-hash transfers between non-related addresses ) -- select distinct origin_function_signature, count(*) as cnt from TOKENS group by 1 order by 2 desc /* -- Spam identified by many transfers of the same token; if there are more than 19 transfers of the same token in a hash, it is 99.99% SPAM , TOKEN_INS as ( select distinct tx_hash as hash_ins, symbol as symbol_ins, count(*) over (partition by tx_hash, symbol) as count_ins from ethereum.core.ez_token_transfers where tx_hash in ( select distinct tx_hash from ethereum.core.ez_token_transfers where to_address in (select addi from ADDIS) and origin_from_address not in (select addi from ADDIS) -- find only transfers to where not initiated by ) ) -- select * from TOKEN_INS where count_ins is not null order by 3 desc /* -- Flag hashes where spam has been identified, to be joined in TAGS , TOKEN_SPAM as ( select tx_hash as hash_spam, symbol as symbol_spam --, has_price, amount_usd, count_ins , (case when has_price = 'false' and symbol not regexp '^[A-Za-z0-9-]+$' then 'spam-symbol' -- spam tokens usually have special character (except hyphen) / fonts, no pricing when count_ins > 19 and (has_price = 'false' or amount_usd < 10) then 'spam-blast' -- single spam token bulk sent to many addresses (using > 19 as found with 20) and for low / no $ end) as flag_spam from TOKENS left join TOKEN_INS on tx_hash = hash_ins and symbol = symbol_ins ) -- select distinct flag_spam, count(*) as cnt, count(distinct hash_spam) as cnt_hash from TOKEN_SPAM group by 1 /* -- Get NFT transfers (ERC721 & ERC1155) from Mints table; from_address is genesis (0x..0000) as NFTs are newly created, where some have only a tx_fee for Native Crypto, others transfer of additional Crypto , NFT_MINTS as ( select 'nft-mints' as source , block_timestamp, block_number, tx_hash , null as origin_function_signature--, null as index , nft_to_address as origin_from_address, null as origin_to_address , nft_from_address as from_address, nft_to_address as to_address , 'nft' as asset_type, concat(replace(project_name, ' ', ''), '-', tokenid) as symbol, 1 as amount , iff(mint_price_usd > 0, mint_price_usd, mint_price_tokens_usd) as token_price , iff(mint_price_usd > 0, mint_price_usd, mint_price_tokens_usd) as amount_usd , nft_address as contract_address , iff(mint_price_eth > 0 or mint_price_tokens > 0, 'true', 'false') as has_price from ethereum.nft.ez_nft_mints where nft_to_address in (select addi from ADDIS) ) -- select * from NFT_MINTS order by block_number, tx_hash /* -- Get NFT transfers (ERC721 & ERC1155) from Sales table; designated as a Sale when when hash includes trade of NFT for Crypto (from Crypto table(s)) , NFT_SALES as ( select 'nft-sales' as source , block_timestamp, block_number, tx_hash , origin_function_signature--, event_index as index , origin_from_address, origin_to_address , seller_address as from_address, buyer_address as to_address , 'nft' as asset_type, concat(replace(project_name, ' ', ''), '-', tokenid) as symbol, 1 as amount , price as token_price, price_usd as amount_usd --, (total_fees + tx_fee) as fee , platform_address as contract_address , iff(token_price > 0, 'true', 'false') as has_price from ethereum.nft.ez_nft_sales where seller_address in (select addi from ADDIS) or buyer_address in (select addi from ADDIS) ) -- select * from NFT_SALES where erc1155_value is not null order by block_number, tx_hash /* -- Get NFT transfers (ERC721 & ERC1155) that are not Mints or Sales (not a trade or from Genesis) , NFT_TRANS as ( select 'nft-transfers' as source , block_timestamp, block_number, tx_hash , null origin_function_signature--, event_index as index , nft_from_address as origin_from_address, nft_to_address as origin_to_address , nft_from_address as from_address, nft_to_address as to_address , 'nft' as asset_type, concat(replace(project_name, ' ', ''), '-', tokenid) as symbol, 1 as amount , null as token_price, null as amount_usd , nft_address as contract_address , 'false' as has_price from ethereum.nft.ez_nft_transfers where (nft_from_address in (select addi from ADDIS) or nft_to_address in (select addi from ADDIS)) and tx_hash not in (select tx_hash from NFT_MINTS) -- removes Mint transfers and tx_hash not in (select tx_hash from NFT_SALES) -- removes Sale transfers ) -- select * from NFT_TRANS order by block_number, tx_hash limit 100 /* -- Combine all (5) Crypto & NFT CTEs into single table (Native, ERC20, ERC721, ERC1155); get missing method_id from TX , TRANSFERS as ( select * from ( select * from NFT_MINTS union all select * from NFT_SALES union all select * from NFT_TRANS union all select * exclude (index, native_type, call_order) from NATIVES union all --from NATIVES union all -- select * from TOKENS ) left join (select hash_tx, method_id_tx, fee, fee_usd, status from TX) tx on tx_hash = tx.hash_tx -- fill missing info from NFT CTEs ) -- Combine all (5) Crypto & NFT CTEs into single table (Native, ERC20, ERC721, ERC1155); get missing method_id from TX , TRANSFERS_FORMAT as ( select --* source , left(block_timestamp, 19) as timestamp , block_number as block, tx_hash as hash , coalesce(origin_function_signature, method_id_tx) as method_id, asset_type , origin_from_address as ofa, origin_to_address as ota , from_address as addr_from, to_address as addr_to , (case when from_address in (select addi from ADDIS) and to_address in (select addi from ADDIS) then 'SELF' when from_address in (select addi from ADDIS) and to_address not in (select addi from ADDIS) then 'OUT' when from_address not in (select addi from ADDIS) and to_address in (select addi from ADDIS) then 'IN' else 'TBD' end) as direction -- find direction of each token transfer in a hash , symbol as asset, amount as quant, token_price as price, round(amount_usd, 2) as value , iff(origin_from_address in (select addi from ADDIS), fee, null) as fee , iff(origin_from_address in (select addi from ADDIS), fee_usd, null) as fee_usd , contract_address as contract , iff(has_price <> 'true', 'flag-price', null) as flag_price , status as success -- as only hashes with transfers here, should not return any Fail but will monitor , listagg(distinct source, ',') within group (order by source) over (partition by hash) as sources -- get all tables where hash was found from TRANSFERS ) -- select * from TRANSFERS_FORMAT limit 100 /* -- When hashes have both in & out of Crypto (usually Native), return net as one usually immaterial and dual direction impacts Tags & PnL , TRANSFERS_NET as ( select * exclude (quant, ins_multi, outs_multi, quant_rank) , (case when ins_multi > 0 and outs_multi > 0 and quant_rank = 1 then quant - sum(iff(quant_rank > 1, quant, 0)) over (partition by hash) when ins_multi > 0 and outs_multi > 0 and quant_rank > 1 then 0 else quant end) as quant -- replace quant with netted amount, if needed , (case when ins_multi > 0 and outs_multi > 0 and quant_rank = 1 then 'warn-multi-netted' when ins_multi > 0 and outs_multi > 0 and quant_rank > 1 then 'ignore' -- to be removed in next CTE else null end) as flag_multi from ( select * , sum(iff(direction = 'IN' and asset_type = 'crypto', 1, 0)) over (partition by hash, asset) as ins_multi -- only consider Crypto , sum(iff(direction = 'OUT' and asset_type = 'crypto', 1, 0)) over (partition by hash, asset) as outs_multi -- only consider Crypto , rank() over (partition by hash, asset order by quant desc) as quant_rank from TRANSFERS_FORMAT ) ) -- select * from TRANSFERS_NET limit 100 /* -- Get Function, Method, and Contract Names from Traces; to be called below , TRACES as ( select tx_hash, function_name as function, left(input, 10) as method, trace_status , from_address, from_address_name as name_from, to_address, to_address_name as name_to from ethereum.core.ez_decoded_traces where tx_hash in (select hash from TRANSFERS_NET) and round(trace_index, 0) = 0 ) -- select * from TRACES limit 100 /* -- Add Function, missing Method, and Contract Names from Traces , FUN as ( select t.* exclude (method_id, success) , f.function, coalesce(t.method_id, f.method) as method , (case when right(t.addr_from, 6) = '000000' then 'MINT' when t.addr_from in (select addi from ADDIS) then 'TEST' end) as name_from , (case when right(t.addr_to, 6) = '000000' then 'BURN' when t.addr_to in (select addi from ADDIS) then 'TEST' end) as name_to , iff(t.success is null or t.success = 'SUCCESS', 'SUCCESS', 'FAIL') as status from (select * exclude flag_multi from TRANSFERS_NET where flag_multi is null or flag_multi <> 'ignore') t left join (select distinct tx_hash as hash_f, function, method from traces) f on t.hash = f.hash_f -- where t.flag_multi is null or t.flag_multi <> 'ignore' -- ignore netted multi-transfers ) -- select * from FUN limit 100 /* -- select distinct function, method, count(*) as cnt from FUN group by 1,2 order by 3 desc /* -- Analyze hash transfers and assign TADA Tag to designate transaction purpose and tax treatment , TAGS as ( select (case when status = 'FAIL' then 'fail' when directions like 'REV%' then directions when hash in (select distinct hash_spam from TOKEN_SPAM where flag_spam is not null) then 'spam' -- basic spam of a. bulk send, b. fake token when directions in ('out', 'in-out') and origin_events = 0 then 'spam' -- advanced spam where transfers in & out, some showing from_addr as client when directions = 'self' then 'transfer-self' when lower(function) like '%withdraw%' or lower(function) like '%remove%' or lower(function) like '%unstake%' or lower(function) like '%leave%' or lower(function) like '%redeem%' then 'unstake' when lower(function) like '%deposit%' or lower(function) like '%add%' or lower(function) like '%stake%' or lower(function) like '%enter%' or lower(function) like '%supply%' or (lower(function) like '%mint%' and nfts = 0) then 'stake' when directions = 'in' and source = 'nft-mints' then 'nft-mint-fee-only' when directions = 'in' and source <> 'nft-mints' and ( method = '0x' or function is null or lower(function) like '%transfer%' or lower(function) like '%send%') then 'transfer-in' when directions = 'in' and function like '%claim%' and flag_price is null then 'reward' when directions = 'in' and function like '%claim%' and flag_price is not null then 'transfer-in' when directions = 'in' then 'transfer-in' when directions = 'out' and (method = '0x' or function is null or lower(function) like '%transfer%' or lower(function) like '%send%') then 'transfer-out' when directions = 'out' and name_to = 'BURN' then 'burn' when directions = 'out' then 'transfer-out' when directions = 'in-out' and nfts = 0 and mints_nft = 0 and mints_crypto = 0 and burns_nft = 0 and burns_crypto = 0 then 'crypto-swap' when directions = 'in-out' and nfts > 0 and mints_nft = 0 and mints_crypto = 0 and burns_nft = 0 and burns_crypto = 0 then 'nft-swap' when directions = 'in-out' and nfts > 0 and mints_nft > 0 and burns_nft = 0 then 'nft-mint' when directions = 'in-out' then 'REVIEW-IN-OUT' else 'REVIEW' end ) as tag , * from ( select * , (case when selfs > 0 and (ins > 0 or outs > 0) then 'REVIEW-SELF' when selfs > 0 then 'self' when ins > 0 and outs = 0 then 'in' when ins = 0 and outs > 0 then 'out' when ins > 0 and outs > 0 then 'in-out' when ins = 0 and outs = 0 then 'none' else 'REVIEW' end ) as directions from ( select rn as tx_num, * , sum(iff(direction = 'IN', 1, 0)) over (partition by hash) as ins , sum(iff(direction = 'OUT', 1, 0)) over (partition by hash) as outs , sum(iff(direction = 'IN', 1, 0)) over (partition by hash, asset) as ins_asset , sum(iff(direction = 'OUT', 1, 0)) over (partition by hash, asset) as outs_asset , sum(iff(direction = 'SELF', 1, 0)) over (partition by hash) as selfs , sum(iff(asset_type = 'crypto', 1, 0)) over (partition by hash) as cryptos , sum(iff(asset_type = 'nft', 1, 0)) over (partition by hash) as nfts , sum(iff(name_from = 'MINT' and asset_type = 'nft', 1, 0)) over (partition by hash) as mints_nft , sum(iff(name_from = 'MINT' and asset_type = 'crypto', 1, 0)) over (partition by hash) as mints_crypto , sum(iff(name_from = 'BURN' and asset_type = 'nft', 1, 0)) over (partition by hash) as burns_nft , sum(iff(name_from = 'BURN' and asset_type = 'crypto', 1, 0)) over (partition by hash) as burns_crypto , sum(iff(ofa in (select addi from ADDIS) or ota in (select addi from ADDIS), 1, 0)) over (partition by hash) as origin_events , count(*) over (partition by hash) as events , row_number() over (partition by hash order by direction, asset) as event_num , iff(direction = 'OUT', null, sum(iff(direction = 'IN', 1, 0)) over (partition by hash, direction order by asset rows between unbounded preceding and current row)) as in_num , iff(direction = 'IN', null, sum(iff(direction = 'OUT', 1, 0)) over (partition by hash, direction order by asset rows between unbounded preceding and current row)) as out_num from FUN left join (select row_number() over (order by ts, ha) as rn, ha from (select distinct timestamp as ts, hash as ha from FUN)) on hash = ha ) ) ) -- select * from TAGS order by tx_num /* -- Structure into final table format with two sections: 1. body (hash, method, tag, etc.) and 2. transfers (IN & OUT) , INnOUT as ( select t.*, i.*, o.* , iff(i.asset_in is not null and i.price_in is null, 'flag-price', null) as flag_in , iff(o.asset_out is not null and o.price_out is null, 'flag-price', null) as flag_out from ( select distinct sources, tx_num, l.tx_line , (case when name_from = 'TEST' then addr_from when name_to = 'TEST' then addr_to else 'TBD' end ) as wallet_init -- final wallet determined in next query , addr_from, name_from, addr_to, name_to , hash, left(timestamp, 10) as day, timestamp, method, function , max(fee) over (partition by hash) as fee , max(fee_usd) over (partition by hash) as fee_usd -- does not include fee from NFTs , events, tag, directions, status from TAGS left join (select hash as hash_line, in_num as tx_line from TAGS union all select hash as hash_line, out_num as tx_line from TAGS) l on hash = l.hash_line -- used to match ins & outs on single line where directions not in ('none', 'self') and tag <> 'spam' and l.tx_line is not null ) t left join (select distinct in_num as num_in, hash as hash_in, asset_type as type_in, asset as asset_in, quant as quant_in, price as price_in, value as value_in, addr_from as sender_in, name_from as sender_name_in from TAGS where direction = 'IN') i on t.hash = i.hash_in and t.tx_line = i.num_in left join (select distinct out_num as num_out, hash as hash_out, asset_type as type_out, asset as asset_out, quant as quant_out, price as price_out, value as value_out, addr_to as receiver_out, name_to as receiver_name_out from TAGS where direction = 'OUT') o on t.hash = o.hash_out and t.tx_line = o.num_out ) -- select * from INnOUT order by tx_num /* -- Generate single wallet due to some NFT hashes originating from wallet 1 but sending NFT to wallet 2 , WALLET as ( select * exclude (addr_from, name_from, addr_to, name_to, wallet_init) , (case when count(distinct wallet_init) over (partition by hash) > 1 then max(iff(name_from = 'TEST', addr_from, null)) over (partition by hash) else wallet_init end ) as wallet -- single address based on main origination from address from INnOUT ) -- select * from WALLET order by tx_num /* -- When one side of a trade has a price while the other does not, we can use expectation that both are equal values and use the available price to impute the missing price , PRICE_IMPUTE as ( select * exclude (value_in, price_in, value_out, price_out) , iff(flag_in is not null and directions = 'in-out' and events = 2 and value_out is not null and value_out > 0, value_out, value_in) as value_in , iff(flag_in is not null and directions = 'in-out' and events = 2 and value_out is not null and value_out > 0, round(value_out / quant_in, 10), price_in) as price_in , iff(flag_out is not null and directions = 'in-out' and events = 2 and value_in is not null and value_in > 0, value_in, value_out) as value_out , iff(flag_out is not null and directions = 'in-out' and events = 2 and value_in is not null and value_in > 0, round(value_in / quant_out, 10), price_out) as price_out from WALLET ) -- select * from PRICE_IMPUTE order by tx_num /* -- Temporary fix for missing WETH transfers in wrap / unwrap (pending Flipside answer) , WRAPPING as ( select sources, tx_num, tx_line, wallet, hash, day, timestamp, method, function, fee, fee_usd , round((sum(value_in) over (partition by tx_num) + sum(value_out) over (partition by tx_num)) / 2, 2) as value_usd , (case when function = 'deposit' and asset_in is null and asset_out = 'ETH' then 'wrap' when function = 'withdraw' and asset_in = 'ETH'and asset_out is null then 'unwrap' else tag end ) as tag , iff((function = 'deposit' and asset_in is null and asset_out = 'ETH') or (function = 'withdraw' and asset_out is null and asset_in = 'ETH'), 'in-out', directions) as directions , events , num_in, type_in , iff(function = 'deposit' and asset_in is null and asset_out = 'ETH', 'WETH', asset_in) as asset_in , iff(function = 'deposit' and asset_in is null and asset_out = 'ETH', quant_out, quant_in) as quant_in , iff(function = 'deposit' and asset_in is null and asset_out = 'ETH', price_out, price_in) as price_in , iff(function = 'deposit' and asset_in is null and asset_out = 'ETH', value_out, value_in) as value_in , iff(function = 'deposit' and asset_in is null and asset_out = 'ETH', '0xc02aaa39b223fe8d0a0e5c4f27ead9083c756cc2', sender_in) as sender_in , iff(function = 'deposit' and asset_in is null and asset_out = 'ETH', 'Wrapped Ether', sender_name_in) as sender_name_in , num_out, type_out , iff(function = 'withdraw' and asset_out is null and asset_in = 'ETH', 'WETH', asset_out) as asset_out , iff(function = 'withdraw' and asset_out is null and asset_in = 'ETH', quant_in, quant_out) as quant_out , iff(function = 'withdraw' and asset_out is null and asset_in = 'ETH', price_in, price_out) as price_out , iff(function = 'withdraw' and asset_out is null and asset_in = 'ETH', value_in, value_out) as value_out , iff(function = 'withdraw' and asset_out is null and asset_in = 'ETH', '0xc02aaa39b223fe8d0a0e5c4f27ead9083c756cc2', receiver_out) as receiver_out , iff(function = 'withdraw' and asset_out is null and asset_in = 'ETH', 'Wrapped Ether', receiver_name_out) as receiver_name_out , status from PRICE_IMPUTE ) -- select * from WRAPPING where tag like '%wrap' /* -- Test final results for known issues , TESTS as ( select (case when tag like 'REV%' then 'flag-tag' when status <> 'SUCCESS' then 'flag-fail' when count(distinct tx_num) over (partition by hash) > 1 then 'flag-dupe' when wallet = 'TBD' or count(distinct wallet) over (partition by hash) > 1 then 'flag-wallet' when tag = 'spam' and ((directions = 'in' and value_in > 10) or (directions = 'out' and value_out > 10) or (directions = 'in-out' and value_usd > 10)) then 'flag-false-spam' when tag <> 'spam' and directions in ('in-out') and fee_usd is null then 'flag-spam-inout-no-fee' when directions in ('in-out') and tag not like '%stake' and sum(value_in) over (partition by hash) is null and sum(value_out) over (partition by hash) is null then 'flag-no-prices' when directions in ('in-out') and tag not like '%stake' and asset_in is not null and price_in is null then 'warn-price-in' when directions in ('in-out') and tag not like '%stake' and asset_out is not null and price_out is null then 'warn-price-out' when directions in ('in-out') and (sum(value_in) over (partition by hash) / sum(value_out) over (partition by hash)) not between .75 and 1.25 then 'flag-value' -- TO FIX DIVISION 0 ERROR - value of all buys vs. sells within 25% when directions = 'in-out' and (sum(iff(quant_in > 0, 1, 0)) over (partition by hash) = 0 or sum(iff(quant_out > 0, 1, 0)) over (partition by hash) = 0) then 'flag-missing-transfer' when count(distinct directions) over (partition by hash) > 1 then 'flag-directions' else null end) as tests , sources, tx_num, tx_line, wallet, hash, day, timestamp, method, function, value_usd, fee, fee_usd, tag, directions, events , num_in, type_in, asset_IN, quant_in, price_in, value_in, sender_in, sender_name_in , num_out, type_out, asset_out, quant_out, price_out, value_out, receiver_out, receiver_name_out from WRAPPING ) -- select * from TESTS where directions = 'out' and fee_usd is null order by timestamp /* -- select distinct tx_num, wallet, hash, day, timestamp, method, function, fee_usd, tag, asset_in, quant_in, price_in, value_in, asset_out, quant_out, price_out, value_out, tests , FIN as ( select distinct tx_num, tests as flag, 'ethereum' as exchange, wallet as source, hash as id , left(timestamp, 4) as year, timestamp as date, function as instruction, tag , value_usd, fee_usd, iff(fee_usd is null, null, 'USD') as fee_asset , asset_in, type_in, quant_in, price_in, value_in , asset_out, type_out, quant_out, price_out, value_out from TESTS ) -- select distinct flag, tag, directions, count(*) as cnt from FIN where tag not like 'trans%' group by 1,2 order by 3 desc /* select * from FIN order by tx_num -- REVIEW -- a. Functions by tag - confirm the function(s) for each tag are logical -- select distinct tag, function, count(distinct hash) over (partition by tag, function) as hashes, max(hash) over (partition by tag, function) as hash_sample from FIN order by 2 desc /* -- b. Hashes by tag - confirm the function(s) for each tag are logical -- select distinct tag, count(distinct hash) over (partition by tag) as hashes, max(hash) over (partition by tag) as hash_sample from FIN order by 2 desc /* -- c. Other tables not used -- select distinct flag_scope, source, count(distinct hash) over (partition by flag_scope, source) as hashes, max(hash) over (partition by flag_scope, source) as hash_sample -- from (select source, tx_hash as hash, from_, f.from_label, to_, t.to_label, g.tag, iff(g.tag is null, 'flag-tag-missing', 'tagged') as flag_scope -- from ( -- select 'dex_swaps' as source, tx_hash, origin_from_address as from_, origin_to_address as to_ from ethereum.defi.ez_dex_swaps where origin_from_address in (select addi from ADDIS) or origin_to_address in (select addi from ADDIS) union -- select 'ls_deposits' as source, tx_hash, origin_from_address as from_, origin_to_address as to_ from ethereum.defi.ez_liquid_staking_deposits where origin_from_address in (select addi from ADDIS) or origin_to_address in (select addi from ADDIS) union -- select 'ls_withdrawals' as source, tx_hash, origin_from_address as from_, origin_to_address as to_ from ethereum.defi.ez_liquid_staking_withdrawals where origin_from_address in (select addi from ADDIS) or origin_to_address in (select addi from ADDIS)) -- left join (select addi, label as from_label from ADDIS) f on f.addi = from_ -- left join (select addi, label as to_label from ADDIS) t on t.addi = to_ -- left join (select distinct hash, tag from FIN) g on tx_hash = g.hash) -- order by 3 desc, 1 /* ---------------------------------------------- SCRATCHPAD ---------------------------------------------- /* Known Issues - ERC404 missing transfers - not included in Flipside data. Found <2% of 'execute' hashes missing transfer(s) - 0xffc8767a118d06f313d0b759be59039d66aad09293c5621f9931119968faba9b Filters - block_timestamp between current_date - interval '1100 days' and current_date - interval '900 days' - block > 20000000 - left(block_timestamp, 10)::date > {-{date_min}) - left(block_timestamp, 10)::date > '2024-10-01' - where block_timestamp > current_date - interval '30 days' Unique hashes -- 4/1-IN/OUT > hash = '0x225417293bfb33384a9b511f4e5d18e546dda01c6edb395752e610cd3e6127a2' -- SPAM > 20-0xb34933084364eb066e711e3ce04136473059f0c4e2663ef81cabe6bc4c62adfd, 200-0xde42f0875fd94abcacffb09daf6e3eb48b0b0dc1c696504d76e321077c8adda4 -- DUAL ADDRESS (originated from 1, NFT sent to 2) - 0x30e43224ad0ca98480d0565b42a4794c896fcde4d5158d08c6ca1f939eabe146, 0x603289624d2c9afdf0edc0b46d4ddbcaf61aae25966f172e361e78ea727345b0 -- SPAM-FISHING - fake tokens, generally different font or spelling > - ETH-0x7b0ea1212152ccefe6936d782f42c4696b72754263c70934c2e7fbc1e717bc32 - 𝐔𝐒𝐃𝐂-0xf5a7506b5078855861245eb38d5d5f9c26b1bdd284562c42de257561d6497853 - FTX Token-0xfa7f85380eb9f27cc40459eb1068f54606fef5389d8ad3493e772236436bdcf7 - 💎USDT-0xf85ada2229e7b5c0a03800c05816079e52f56f2bf182b8eb58388e604efaf2ab -- Add Function, missing Method, and Contract Names from Traces , FUN as ( select * exclude (flag_multi, success) , function, coalesce(method_id, method) as method , (case when right(addr_from, 6) = '000000' then 'MINT' when addr_from in (select addi from ADDIS) then 'TEST' -- else nf.name_from end -- expensive / not needed end) as name_from , (case when right(addr_to, 6) = '000000' then 'BURN' when addr_to in (select addi from ADDIS) then 'TEST' -- else nt.name_to end -- expensive / not needed end) as name_to , iff(success is null or success = 'SUCCESS', 'SUCCESS', 'FAIL') as status -- and (f.trace_status is null or f.trace_status = 'SUCCESS'), 'SUCCESS', 'FAIL') as status from TRANSFERS_NET left join (select distinct tx_hash as hash_t, function, method from traces) on hash = hash_t -- left join TRACES f on f.tx_hash = t.hash -- left join TRACES nf on nf.tx_hash = t.hash and nf.from_address = t.addr_from -- left join TRACES nt on nt.tx_hash = t.hash and nt.to_address = t.addr_to where flag_multi is null or flag_multi <> 'ignore' -- ignore netted multi-transfers ) */

Query	Creator	Created
mammoth-white uniswap kyberswap hashflow maverick dodo	pepe69420	Oct 30, 2024
Vitalik \| Fun uniswap kyberswap hashflow maverick dodo	pepe69420	Oct 19, 2024

pepe69420