---- TADA 3.0 ---- -- 11.8.24 -- OVERVIEW -- a. Calculate Capital gain / loss and reward income -- TO DO: -- 1. Dual & aggregated basis rationalization - 11.8 -- 2. Income considerations? -- 3. DeFi logic for lending rewards / borrow expense -- 4. Fees - a. split fee for multi-transfer, b. NFT platform fee from NFT_SWAPS (unless in other CTE) -- 5. Tags - UNI-V3 as stake, not nft-swap with 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) ) ---- [1] TRANSFERS ---- ------------------------------------------------------------------------------------------------------------------------------------------ -- a. Get all Native, Token (ERC20), NFT (ERC721/ERC1155) transfers for addresses in ADDIS , UNO as ( with -- 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 , null as event_name --11.4-added again , 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 , null as flag_spam from ethereum.core.ez_native_transfers where from_address in (select addi from ADDIS) or to_address in (select addi from ADDIS) ) where amount_usd > 0 and (native_type = 'native-origin' or (native_type = 'native-ft' and call_order = 1)) ) --select distinct * from NATIVES where tx_hash = '0xdd0299b3562838b2ba16c5d5cb00fe7049c1456a219ce7e60c4ddd7aae8270f3' /* -- 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 , null as event_name --11.4-added again , origin_function_signature , 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 * from TOKENS where tx_hash = '0xdd0299b3562838b2ba16c5d5cb00fe7049c1456a219ce7e60c4ddd7aae8270f3' /* -- 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 hash_ins = '0x92f2708462150fd94eb0ce90e975050a7025d0c98603657861c236409c7e1c92' 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-]+$' or symbol like '%.%') 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 $ when count(distinct contract_address) over (partition by tx_hash, symbol) > 1 then 'spam-contracts' -- spam catch where 0 real asset and X fake asset transferred 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 , event_type as event_name --11.4-added again , null as origin_function_signature , 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 , iff(has_price = 'false' and (symbol not regexp '^[A-Za-z0-9-]+$' or symbol like '%.%'), 'spam-symbol-nft', null) as flag_spam -- spam tokens usually have special character (except hyphen) / fonts, no pricing from ethereum.nft.ez_nft_mints where nft_to_address in (select addi from ADDIS) ) --select * from NFT_MINTS where tx_hash = '0x92f2708462150fd94eb0ce90e975050a7025d0c98603657861c236409c7e1c92' 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 , event_type as event_name --11.4-added again , origin_function_signature , 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 , iff(has_price = 'false' and (symbol not regexp '^[A-Za-z0-9-]+$' or symbol like '%.%' or symbol like '%:%'), 'spam-symbol-nft', null) as flag_spam -- spam tokens usually have special character (except hyphen) / fonts, no pricing 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 --as hash_nft , event_type as event_name --11.4-added again , null origin_function_signature , 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 , (case when has_price = 'false' and (symbol not regexp '^[A-Za-z0-9-]+$' or symbol like '%.%') then 'spam-symbol-nft' when c.trans_cnt > 25 then concat('spam-blast-nfts') end) as flag_spam -- spam tokens usually have special character (except hyphen) / fonts, no pricing from ( select * 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 ) left join (select tx_hash as hash_cnt, count(*) as trans_cnt from ethereum.nft.ez_nft_transfers group by 1) c on tx_hash = c.hash_cnt ) --select distinct tx_hash, trans_cnt, flag_spam from NFT_TRANS where flag_spam like 'spam-blast-nfts%' order by 2 desc /* -- 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 (select t.*, s.flag_spam from TOKENS t left join token_spam s on t.tx_hash = s.hash_spam and t.symbol = s.symbol_spam) ) 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 ) --select * from TRANSFERS where tx_hash = '0xdd0299b3562838b2ba16c5d5cb00fe7049c1456a219ce7e60c4ddd7aae8270f3' /* -- 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, event_name , 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 , flag_spam , 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) , lower(f.function) as 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 ) --select distinct * from FUN where hash = '0xdd0299b3562838b2ba16c5d5cb00fe7049c1456a219ce7e60c4ddd7aae8270f3' /* , DEFI as ( select * from ( (select 'bridge' as source, tx_hash, iff(origin_from_address in (select addi from ADDIS), origin_from_address, origin_to_address) as wallet, token_symbol as asset, event_name, platform from ethereum.defi.ez_bridge_activity where origin_from_address in (select addi from ADDIS) or origin_to_address in (select addi from ADDIS) ) union all (select 'swap' as source, tx_hash, iff(origin_from_address in (select addi from ADDIS), origin_from_address, origin_to_address) as wallet, concat(token_in, token_out) as asset, event_name, pool_name as platform 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 all (select 'lend-borrow' as source, tx_hash, iff(origin_from_address in (select addi from ADDIS), origin_from_address, origin_to_address) as wallet, token_symbol as asset, event_name, platform --Borrow,Compound V2 (also:borrower) from ethereum.defi.ez_lending_borrows where origin_from_address in (select addi from ADDIS) or origin_to_address in (select addi from ADDIS) ) union all (select 'lend-deposit' as source, tx_hash, iff(origin_from_address in (select addi from ADDIS), origin_from_address, origin_to_address) as wallet, token_symbol as asset, event_name, platform --Supply,Aave V3 from ethereum.defi.ez_lending_deposits where origin_from_address in (select addi from ADDIS) or origin_to_address in (select addi from ADDIS) ) union all (select 'lend-flashloan' as source, tx_hash, iff(origin_from_address in (select addi from ADDIS), origin_from_address, origin_to_address) as wallet, flashloan_token_symbol as asset, event_name, platform from ethereum.defi.ez_lending_flashloans where origin_from_address in (select addi from ADDIS) or origin_to_address in (select addi from ADDIS) ) union all (select 'lend-liquidation' as source, tx_hash, iff(origin_from_address in (select addi from ADDIS), origin_from_address, origin_to_address) as wallet, concat(collateral_token_symbol, debt_token_symbol) as asset, event_name, platform from ethereum.defi.ez_lending_liquidations where origin_from_address in (select addi from ADDIS) or origin_to_address in (select addi from ADDIS) ) union all (select 'lend-repay' as source, tx_hash, iff(origin_from_address in (select addi from ADDIS), origin_from_address, origin_to_address) as wallet, token_symbol as asset, event_name, platform --Supply,Compound V3 from ethereum.defi.ez_lending_repayments where origin_from_address in (select addi from ADDIS) or origin_to_address in (select addi from ADDIS) ) union all (select 'lend-withdraw' as source, tx_hash, iff(origin_from_address in (select addi from ADDIS), origin_from_address, origin_to_address) as wallet, token_symbol as asset, event_name, platform --WithdrawCollateral,Compound V3 from ethereum.defi.ez_lending_withdraws where origin_from_address in (select addi from ADDIS) or origin_to_address in (select addi from ADDIS) ) union all (select 'stake-deposit' as source, tx_hash, iff(origin_from_address in (select addi from ADDIS), origin_from_address, origin_to_address) as wallet, token_symbol as asset, event_name, platform 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 all (select 'stake-withdraw' as source, tx_hash, iff(origin_from_address in (select addi from ADDIS), origin_from_address, origin_to_address) as wallet, token_symbol as asset, event_name, platform 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)) ) ) , LOGS as ( select tx_hash as hash_log , origin_from_address as ofa_log , origin_to_address as contract_log , lower(listagg(distinct event_name, ',') within group (order by event_name) over (partition by tx_hash)) as logs -- get all event_name where hash was found from ethereum.core.ez_decoded_event_logs where tx_hash in (select distinct hash from FUN) and origin_from_address in (select addi from ADDIS) and tx_status = 'SUCCESS' and event_removed = 'false' ) --select * from LOGS /* , FIN as ( select distinct f.sources as sources_standard , max(d.sources_defi) over (partition by f.hash) as sources_defi , f.source, d.source_defi , f.flag_price, f.flag_spam , count(distinct d.source_defi) over (partition by f.hash) as defis , f.timestamp, f.block, f.hash, f.function, f.method , coalesce(f.event_name, d.event_defi) as event_name , f.ofa, f.ota, f.addr_from, f.addr_to , coalesce(f.name_from, d.platform) as name_from , coalesce(f.name_to, d.platform) as name_to , f.direction, f.asset, f.asset_type, f.quant , f.price, f.value, f.fee, f.fee_usd , f.contract, f.status , l.logs from FUN f left join ( select distinct tx_hash as hash_defi, asset as asset_defi, event_name as event_defi, source as source_defi, platform , listagg(distinct source, ',') within group (order by source) over (partition by tx_hash) as sources_defi -- get all tables where hash was found from DEFI ) d on f.hash = d.hash_defi and f.asset = d.asset_defi left join LOGS l on l.hash_log = f.hash and f.ofa = l.ofa_log ) select distinct iff(sources_defi is not null, concat(sources_standard, ',', sources_defi), sources_standard) as sources, * exclude (sources_standard, sources_defi) from FIN order by timestamp, hash, asset ) --select * from UNO limit 10 ---- [2] TRANSFER ANALYZE & TAG ---- ------------------------------------------------------------------------------------------------------------------------------------------ -- a. For each transaction hash, determine how many transfers and of what type of token occurred -- b. Generate a Tag to describe the transaction behavior and assignment for next step tax calculation -- TO DO: -- 1. Aggregated basis approach - to confirm if should move to tx_hash level - 11.8 , DOS as ( with -- Reference tokenlist which will only have tokens if they are not spam TOKEN_VERIFY as ( select g.*, iff(t.symbol is not null, 'verified', 'flag-not-tokenlisted') as token_verify from (select distinct * from UNO) g left join external.tokenlists.ez_verified_tokens t on t.token_address = g.contract and g.asset_type = 'crypto' ) --select distinct * from TOKEN_VERIFY where hash = '0xa8798661c7c7dc52f3820f565594c25aa2d7d0c7881ed7058c4333efcb5f1879' /* -- 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 --- SPAM --- when flag_spam is not null then 'spam' --basic spam of a. bulk send, b. fake token when sum(iff(flag_spam is not null, 1, 0)) over (partition by hash) > 0 then 'spam-2' --any hash spam transfers when directions <> 'in-out' and function like '%swap%' and fee is null then 'spam-3' -- no fee or in-out when directions in ('in-out') and origin_events = 0 and (value is null or value = 0) then 'spam-4' --advanced spam where transfers in & out, some showing from_addr as client when fee is null and (asset is null or source = 'nft-mints') then 'spam-5' --NFT mint should have fee when directions in ('out') and origin_events = 0 and (value is null or value = 0) then 'spam-REVIEW-1' -- advanced spam where transfers in & out, some showing from_addr as client when directions in ('in', 'out') and value is null and token_verify is null then 'spam-REVIEW-2' -- no price, not verified by Tokenlist when directions = 'in-out' and value is null and token_verify is null then 'spam-REVIEW-3' -- no price, not verified by Tokenlist --- DEFI --- when sources like '%bridge%' or function like '%bridge%' or logs like '%bridge%' then 'defi-bridge' when sources like '%stake-withdraw%' then 'defi-unstake-liquid' when sources like '%stake-deposit%' then 'defi-stake-liquid' when sources like '%lend-borrow%' or function like '%borrow%' then 'defi-borrow' -- ensure checked first due to collateral when sources like '%lend-repay%' or function like '%repay%' then 'defi-repay' -- ensure checked first due to collateral when sources like '%lend-withdraw%' or (function like '%withdraw%' or function like '%remove%' or function like '%unstake%' or function like '%leave%' or function like '%redeem%') then 'defi-unstake' when sources = 'native' and directions = 'in' and asset_types = 'crypto' and logs like '%collateral%' then 'defi-unstake-2' when sources like '%lend-deposit%' or (function like '%deposit%' or function like '%add%' or function like '%stake%' or function like '%enter%' or function like '%supply%' or (function like '%mint%' and asset_types not like '%nft%')) then 'defi-stake' when sources = 'native' and directions = 'out' and asset_types = 'crypto' and logs like '%collateral%' then 'defi-stake-2' when directions = 'out' and name_to = 'burn' then 'burn' --- REWARDS --- when directions = 'in' and function like '%claim%' and logs not like '%collateral%' and flag_price is null then 'reward' --- TRANSFERS --- when directions = 'in' and function like '%claim%' and flag_price is not null then 'transfer-in' when directions = 'in' and source <> 'nft-mints' and (method = '0x' or function is null or function like '%transfer%' or function like '%send%') then 'transfer-in' when directions = 'in' then 'transfer-in' when directions = 'self' then 'transfer-self' when directions = 'out' and (method = '0x' or function is null or function like '%transfer%' or function like '%send%') then 'transfer-out' when directions = 'out' then 'transfer-out' --- SWAPS --- 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 function in ('sellGem') then 'crypto-swap' --sellGem=sky/DAI --- NFTS --- when directions = 'in-out' and nfts > 0 and mints_nft > 0 and burns_nft = 0 then 'nft-mint' when directions = 'in' and source = 'nft-mints' then 'nft-mint-fee-only' 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' --- REVIEW --- when directions = 'in-out' then 'REVIEW-IN-OUT' else 'REVIEW' end ) as tag , (case when flag_spam is not null then flag_spam -- basic spam of a. bulk send (spam-blast), b. fake token (spam-symbol), c. fake token + real token (spam-contract) when sum(iff(flag_spam is not null, 1, 0)) over (partition by hash) > 0 then max(flag_spam) over (partition by hash) -- any hash spam transfers when directions in ('in-out') and origin_events = 0 and (value is null or value = 0) then 'spam-inout-no-origin' -- advanced spam where transfers in & out, some showing from_addr as client when directions in ('out') and origin_events = 0 and (value is null or value = 0) then 'spam-out-no-origin' -- advanced spam where transfers out, some showing from_addr as client when fee is null and (asset is null or source = 'nft-mints') then 'spam-nft-mint-no-fee' -- NFT mint should have fee when directions <> 'in-out' and function like '%swap%' and fee is null then 'spam-invalid-swap' -- no fee or in-out else null end ) as flag_spam , * exclude flag_spam from ( select * , (case 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 , (case when cryptos > 0 and nfts > 0 then 'crypto-nft' when cryptos > 0 and nfts = 0 then 'crypto' when cryptos = 0 and nfts > 0 then 'nft' end) as asset_types from ( select distinct r.rn as tx_num, * exclude (rn, ha) , 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 (select distinct * from TOKEN_VERIFY) left join (select row_number() over (order by ts, ha) as rn, ha from (select distinct timestamp as ts, hash as ha from UNO)) r on hash = r.ha ) ) ) --select distinct * 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) , INOUT as ( select distinct (case when t.flag_spam is not null then t.flag_spam when count(distinct t.wallet_init) over (partition by t.hash) > 1 and ( (t.directions = 'in' and (i.value_in is null or i.value_in = 0)) or (t.directions = 'out' and (o.value_out is null or o.value_out = 0)) ) then 'spam-multi-wallet-no-value' -- final spam check for consolidated hash transfers end) as flag_spam , (case when t.tag like 'spam%' then 'spam' when count(distinct t.wallet_init) over (partition by t.hash) > 1 and ( (t.directions = 'in' and (i.value_in is null or i.value_in = 0)) or (t.directions = 'out' and (o.value_out is null or o.value_out = 0)) ) then 'spam' -- final spam check for consolidated hash transfers else t.tag end) as tag , t.* exclude (flag_spam, tag) , i.* exclude hash_in , o.* exclude hash_out , 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 t.sources, t.tx_num, l.tx_line , (case when t.name_from = 'test' then t.addr_from when t.name_to = 'test' then t.addr_to else 'TBD' end ) as wallet_init -- final wallet determined in next query , t.asset_types , t.addr_from, t.name_from, t.addr_to, t.name_to , t.hash, left(t.timestamp, 10) as day, t.timestamp, t.method, t.function , max(t.fee) over (partition by t.hash) as fee , max(t.fee_usd) over (partition by t.hash) as fee_usd -- does not include fee from NFTs , t.events, t.tag, t.directions, t.status, t.logs, t.defis , max(t.flag_spam) over (partition by t.hash) as flag_spam -- if any spam, use for hash from TAGS t left join ( select hash, addr_from, addr_to, in_num as tx_line from TAGS where in_num is not null union select hash, addr_from, addr_to, out_num as tx_line from TAGS where out_num is not null ) l on t.hash = l.hash -- used to match ins & outs on single line and t.addr_from = l.addr_from and t.addr_to = l.addr_to ) 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, event_name as event_in , addr_from as sender_in, name_from as sender_name_in, contract as contract_in, ota as interacted_in, token_verify as token_verify_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, event_name as event_out , addr_to as receiver_out, name_to as receiver_name_out, contract as contract_out, ota as interacted_out, token_verify as token_verify_out from TAGS where direction = 'out' ) o on t.hash = o.hash_out and t.tx_line = o.num_out ) --select distinct * from INOUT order by tx_num, tx_line /* -- Fix multi-line by generating single wallet / line due to some NFT hashes originating from wallet 1 but sending NFT to wallet 2; also fix multiple method where some not decoded , 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 INOUT ) --select distinct * 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 *, iff(tag <> 'spam' and directions = 'in-out', round(least(value_in, value_out) / greatest(value_in, value_out), 2), null) as value_ratio from ( select distinct * 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 /* -- Create event for wrapped token transfer as no event emitted , WRAPPING as ( select distinct sources, tx_num, tx_line, wallet, hash, day, timestamp, method, function, asset_types, fee, fee_usd, value_ratio , (case when directions = 'in' then value_in when directions = 'out' then value_out when directions = 'in-out' then round((sum(value_in) over (partition by tx_num) + sum(value_out) over (partition by tx_num)) / 2, 2) end) as value_usd , (case when tag = 'REVIEW-IN-OUT' and value_ratio between .95 and 1.05 then 'crypto-swap-2' 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' -- DAI contracts for the below: ('0xe8955c9248d830b1a2d5f7afb70860ae22edfded', '0x74ac14c85b2c338e6b9b5386138bd7a6f2f428c8', '0x590fba885b8b83b335e1f803fbb213688e1f50f5') when directions = 'in-out' and value_ratio < .90 and value_in > 100 and asset_in = 'DAI' then 'defi-borrow-2' --and (logs like '%deposit%' or logs like '%newcdp%' when directions = 'in' and value_in > 100 and asset_in = 'DAI' then 'defi-borrow-3' --and (logs like '%deposit%' or logs like '%newcdp%' when directions = 'in-out' and value_ratio < .90 and value_out > 100 and asset_out = 'DAI' then 'defi-repay-2' --and logs like '%withdraw%' when directions = 'out' and value_out > 100 and asset_out = 'DAI' then 'defi-repay-3' --and logs like '%withdraw%' 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 , coalesce(interacted_in, interacted_out) as contract , events, logs , 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 , iff(function = 'deposit' and asset_in is null and asset_out = 'ETH', contract_out, contract_in) as token_addr_in , iff(function = 'deposit' and asset_in is null and asset_out = 'ETH', 'verified', token_verify_in) as token_verify_in , iff(function = 'deposit' and asset_in is null and asset_out = 'ETH', function, event_in) as event_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 , iff(function = 'withdraw' and asset_out is null and asset_in = 'ETH', contract_in, contract_out) as token_addr_out , iff(function = 'withdraw' and asset_out is null and asset_in = 'ETH', 'verified', token_verify_out) as token_verify_out , iff(function = 'withdraw' and asset_out is null and asset_in = 'ETH', function, event_out) as event_out , status, flag_spam, defis from PRICE_IMPUTE ) --select * from WRAPPING order by tx_num, tx_line /* -- Test final results for known issues , TESTS as ( select * exclude tag, iff(tests = 'flag-no-value', 'spam', tag) as tag from ( select distinct (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-tx_num' when count(distinct directions) over (partition by hash) > 1 then 'flag-directions' when tag not in ('spam') and directions <> 'self' and asset_in is null and asset_out is null then 'flag-asset' when tag not in ('spam') and ( (directions = 'in-out' and (count(*) over (partition by hash, asset_in, quant_in) > 1 or count(*) over (partition by hash, asset_out, quant_out) > 1)) or (directions = 'in' and (count(*) over (partition by hash, asset_in, quant_in) > 1)) or (directions = 'out' and count(*) over (partition by hash, asset_out, quant_out) > 1) ) then 'flag-dupe-transfer' when wallet = 'TBD' or count(distinct wallet) over (partition by hash) > 1 then 'flag-wallet' when tag = 'spam' and fee is not null 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-out' and fee_usd is null then 'flag-spam-inout-no-fee' when tag not in ('spam', 'transfer-self') and ((directions = 'in' and value_in = 0 and token_verify_in <> 'verified') or (directions = 'out' and value_out = 0 and token_verify_in <> 'verified') or (directions = 'in-out' and value_in = 0 and value_out = 0 and token_verify_in <> 'verified' and token_verify_out <> 'verified')) then 'ignore-no-value' when tag not in ('spam', 'transfer-self') and tag not like 'defi%' and ((directions = 'in' and value_in is null and asset_types <> 'nft' and token_verify_in <> 'verified') or (directions = 'out' and value_out is null and token_verify_out <> 'verified') or (directions = 'in-out' and value_in is null and value_out is null and token_verify_in <> 'verfied' and token_verify_out <> 'verfied')) then 'flag-no-value' when tag not in ('spam', 'transfer-self') and tag like 'defi%' and ((directions = 'in' and value_in is null and type_in <> 'nft') or (directions = 'out' and value_out is null and type_out <> 'nft') or (directions = 'in-out' and value_in is null and value_out is null and asset_types <> 'nft')) then 'warn-defi-no-value' when tag not in ('spam') and directions in ('in-out') and tag not like 'defi%' 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 tag not in ('spam') and directions in ('in-out') and tag not like 'defi%' and asset_in is not null and price_in is null then 'warn-price-in' when tag not in ('spam') and directions in ('in-out') and tag not like 'defi%' and asset_out is not null and price_out is null then 'warn-price-out' when tag not in ('spam') and directions in ('in-out') and tag not like 'defi%' and value_in > 0 and value_out > 0 and (sum(value_in) over (partition by hash) / sum(value_out) over (partition by hash)) not between .75 and 1.25 then 'flag-value-ratio' -- value of all buys vs. sells within 25% when tag not in ('spam') and 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 tag not in ('spam') and ( (directions = 'in' and sum(iff(tag = 'spam', value_in, null)) over (partition by asset_in) > 100) or (directions = 'out' and sum(iff(tag = 'spam', value_out, null)) over (partition by asset_out) > 100) or (directions = 'in-out' and (sum(iff(tag = 'spam', value_in, null)) over (partition by asset_in) + sum(iff(tag = 'spam', value_out, null)) over (partition by asset_out)) > 100)) then 'flag-spam-high-value' -- ensure value of spam flagged tokens is immaterial else null end ) as tests , sources, tx_num, tx_line, wallet, hash, day, timestamp , method, function, logs, asset_types, value_usd, fee, fee_usd , tag, directions, defis, events, contract , num_in, event_in, type_in, asset_IN, quant_in, price_in, value_in, sender_in, sender_name_in, token_addr_in, token_verify_in , num_out, event_out, type_out, asset_out, quant_out, price_out, value_out, receiver_out, receiver_name_out, token_addr_out, token_verify_out , iff(fee is not null, 'true', 'false') as fee_paid, flag_spam, value_ratio from WRAPPING ) ) --select distinct * from TESTS where hash = '0x2fbf057924fae05f708a5562884782d01935a716e72fe027493ede2fa3024e7f' order by tx_num, tx_line /* -- 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, tx_line , (case when tag = 'spam' then null when contract in ('0xe8955c9248d830b1a2d5f7afb70860ae22edfded', '0x74ac14c85b2c338e6b9b5386138bd7a6f2f428c8', '0x590fba885b8b83b335e1f803fbb213688e1f50f5') then 'warn-aave_v3-tbd' when tag not like 'defi%' and tag not in ('spam') and (sources like '%lend%' or sources like '%stake%' or sources like '%bridge%') then 'flag-defi-main' when tag not like 'defi%' and tag not in ('spam', 'wrap', 'unwrap') and directions = 'in-out' and right(asset_in, 3) = right(asset_out, 3) then 'flag-defi-wrap' when tag <> 'defi-stake' and (function like '%deposit%' or function like '%add%' or (function like '%stake%' and function not like '%unstake%') or function like '%enter%' or function like '%supply%' or (logs like '%stake%' and logs not like '%unstake%') or (function like '%mint%' and asset_types not like '%nft%')) then 'flag-defi-stake' when tag <> 'defi-unstake' and (function like '%withdraw%' or function like '%remove%' or function like '%unstake%' or function like '%leave%' or function like '%redeem%' or logs like '%unstake%') then 'flag-defi-unstake' when tag <> 'defi-borrow' and (function like '%borrow%' or logs like '%borrow%') then 'flag-defi-borrow' when tag <> 'defi-repay' and (function like '%repay%' or logs like '%repay%') then 'flag-defi-repay' when tag not like '%bridge%' and logs like '%bridge%' then 'flag-defi-bridge' when tag not like 'defi%' and value_ratio not between .95 and 1.05 then 'flag-defi-value-ratio' when tag not like 'defi%' and logs like '%collateral%' and logs like '%supply%'then 'flag-defi-collateral-supply' when tag not like 'defi%' and (logs like '%supply%' or logs like '%deposit%') then 'flag-defi-collateral-supply' when tag not like 'defi%' and logs like '%collateral%' and logs like '%withdraw%'then 'flag-defi-collateral-withdraw-1' when tag not like 'defi%' and logs not like '%collateral%' and logs like '%withdraw%'then 'flag-defi-collateral-withdraw-2' when tag not like 'defi%' and logs like '%collateral%' then 'warn-defi-collateral' when tag = 'reward' and value_in > 100000 then 'flag-reward-high' when tag = 'reward' and value_in is null then 'flag-reward-null' when tests = 'flag-spam-value' and tag not in ('spam') and least(sum(value_in) over (partition by hash), sum(value_out) over (partition by hash)) / greatest(sum(value_in) over (partition by hash), sum(value_out) over (partition by hash)) > .10 then 'flag-defi-spam-value' else tests end ) as flag_defi , iff(tag = 'spam' and tests = 'flag-no-value', null, tests) as flag , 'ethereum' as exchange, wallet, hash , left(timestamp, 4) as year, timestamp, function as instruction, tag, sources, contract, directions, asset_types , value_usd, fee_usd, iff(fee_usd is null, null, 'USD') as fee_asset , event_in, asset_in, type_in, quant_in, price_in, value_in, token_addr_in, token_verify_in , event_out, asset_out, type_out, quant_out, price_out, value_out, token_addr_out, token_verify_out , value_ratio, logs, flag_spam from TESTS ) , TADA as ( select distinct r.rn as tx_num , wallet, hash, timestamp, directions, asset_types, value_usd, fee_usd , (case when tag = 'transfer-in' then 'crypto-buy' when tag like '%-2' or tag like '%-3' then substring(tag, 1, length(tag)-2) else tag end ) as tag , (case when tag = 'crypto-swap' then 'capital-gain' when tag = 'reward' then 'income' else null end ) as taxable , asset_in, quant_in as quantity_in, price_in, value_in , iff(tag = 'transfer-in', 'USD', asset_out) as asset_out , iff(tag = 'transfer-in', quant_in, quant_out) as quantity_out , iff(tag = 'transfer-in', 1, price_out) as price_out , iff(tag = 'transfer-in', value_in, value_out) as value_out , instruction, flag, flag_defi, logs, value_ratio, event_in, event_out, token_verify_in, token_verify_out from FIN left join ( select row_number() over (order by tx) as rn, ha from ( select distinct tx_num as tx, hash as ha from FIN ) ) r on hash = r.ha ) select * from TADA order by tx_num ) --select * from DOS limit 10 /* ---- [3] BASIS ---- ------------------------------------------------------------------------------------------------------------------------------------------ -- a. For each hash where capital gain calculation is required, generate a buy table to include required basis info for next step FIFO calc -- TO DO: -- 1. Aggregated basis approach - to confirm if should move to tx_hash level - 11.8 , TRES as ( with CAP as ( select * from DOS where directions like 'in%' ) , QUANT_SUMS as ( select distinct row_number() over (partition by asset order by buy_date desc) as buy_num , concat(buy_date, '.', asset) as buy_id , * , round(sum(buy_quant) over (partition by asset order by buy_date desc), 2) as buy_pool_end --to spread LIFO by inventory , lag(buy_date, 1, buy_date) over (partition by asset order by buy_date) as buy_date_prev , lag(buy_price, 1, buy_price) over (partition by asset order by buy_date) as buy_price_prev , lag(buy_value, 1, buy_value) over (partition by asset order by buy_date) as value_prev , datediff(day, lag(buy_date, 1, buy_date) over (partition by asset order by buy_date), buy_date) as buy_gap , abs(datediff(day, lead(buy_date, 1, buy_date) over (partition by asset order by buy_date), buy_date)) as next_basis_gap , round(max(buy_price) over (partition by asset) / min(buy_price) over (partition by asset), 2) as buy_ratio_all , round(max(buy_price) over (partition by asset, buy_year) / min(buy_price) over (partition by asset, buy_year), 2) as buy_ratio_yr from ( select distinct buy_year, buy_date, asset, asset_types, buy_price , round(sum(value_usd) over (partition by asset, buy_date), 2) as buy_value , round(sum(quantity_in) over (partition by asset, buy_date), 4) as buy_quant from ( select distinct left(timestamp, 4) as buy_year , try_to_date(timestamp) as buy_date , value_usd , asset_in as asset , asset_out , 'Ethereum' as source , asset_types , quantity_in , (case when value_in > 1 then round(value_in, 2) else value_in end) as value_in , price_in as buy_price , value_ratio from CAP order by buy_date, asset ) order by buy_date, asset ) order by buy_date, asset ) --select * from QUANT_SUMS /* , PRICES_IQR as ( select distinct * , round(iff(buy_price > buy_price_prev, buy_price / buy_price_prev, buy_price_prev / buy_price), 2) as buy_ratio_prev , round(iff((q1 - (1.5*(q3-q1))) < 0, q1/5, (q1 - (1.5*(q3-q1)))), 10) as low , round((q3 + (1.5*(q3-q1))), 10) as high from ( select distinct * , (percentile_cont(0.25) within group (order by buy_price) over (partition by asset))::decimal(18, 10) as q1 , (percentile_cont(0.75) within group (order by buy_price) over (partition by asset))::decimal(18, 10) as q3 from QUANT_SUMS) ) , PRICES_IQR_YR as ( select distinct * , round(iff((q1 - (1.5*(q3-q1))) < 0, q1/5, (q1 - (1.5*(q3-q1)))), 10) as low_yr , round((q3 + (1.5*(q3-q1))), 10) as high_yr from ( select distinct * , (percentile_cont(0.25) within group (order by buy_price) over (partition by asset, buy_year))::decimal(18, 10) as q1 , (percentile_cont(0.75) within group (order by buy_price) over (partition by asset, buy_year))::decimal(18, 10) as q3 from QUANT_SUMS) ) , PRICES_FLAG as ( select * , round(iff(buy_price > buy_avg_before, buy_price / buy_avg_before, buy_avg_before / buy_price), 2) as buy_ratio_before , round(iff(buy_price > buy_avg_after, buy_price / buy_avg_after, buy_avg_after / buy_price), 2) as buy_ratio_after from ( select distinct a.* , round((case when a.buy_price > a.high then (a.buy_price / a.high) when a.buy_price < a.low then -(a.low / a.buy_price) else null end), 4) as range_iqr , iff(abs(round((case when a.buy_price > a.high then (a.buy_price / a.high) when a.buy_price < a.low then -(a.low / a.buy_price) else null end), 4)) > 100, 'flag > 100', null) as flag_iqr , y.low_yr, y.high_yr , iff(abs(round((case when y.buy_price > y.high_yr then (y.buy_price / y.high_yr) when y.buy_price < y.low_yr then -(y.low_yr / y.buy_price) else null end), 4)) > 100, 'flag > 100', null) as flag_iqr_yr , iff(a.buy_ratio_prev > 10, 'Flag > 10', null) as flag_prev_price , max(a.buy_ratio_prev) over (partition by a.asset, a.buy_year) as max_yr_pbr , round(sum(a.buy_value) over (partition by a.asset, a.buy_year), 2) as value_yr , count(a.buy_price) over (partition by a.asset) as buy_cnt , count(a.buy_price) over (partition by a.asset order by a.buy_date rows between unbounded preceding and 1 preceding) as buy_cnt_before , round(avg(a.buy_price) over (partition by a.asset order by a.buy_date rows between unbounded preceding and 1 preceding), 4) as buy_avg_before , count(a.buy_price) over (partition by a.asset order by a.buy_date rows between 1 following and unbounded following) as buy_cnt_after , round(avg(a.buy_price) over (partition by a.asset order by a.buy_date rows between 1 following and unbounded following), 4) as buy_avg_after from PRICES_IQR_YR y left join PRICES_IQR a on y.asset = a.asset and y.buy_num = a.buy_num order by a.asset, a.buy_date ) ) , FLAG_ANALYSIS as ( select distinct (case when value_yr > 10000 and buy_ratio_prev > 5 and buy_gap > 730 and buy_gap <= 10 then '[1] 2+ Gap - Low < 10' --either do not allow this join or remove the earlier ones if too big of a variance when value_yr > 10000 and buy_ratio_prev > 5 and buy_gap > 730 and buy_gap > 10 then '[2] 2+ Gap - High > 10' --either do not allow this join or remove the earlier ones if too big of a variance when value_yr > 10000 and buy_ratio_prev > 5 and buy_price / buy_avg_before > 5 and buy_price / buy_avg_after > 5 then '[3] Variable - Low' when value_yr > 10000 and buy_ratio_prev > 5 and buy_avg_before / buy_price > 5 and buy_avg_after / buy_price > 5 then '[4] Variable - High' when value_yr > 10000 and buy_ratio_prev > 5 and buy_avg_before > buy_price and buy_avg_after < buy_price then '[5] Review - Down' when value_yr > 10000 and buy_ratio_prev > 5 and buy_avg_before < buy_price and buy_avg_after > buy_price then '[6] Review - Up' else null end) as buy_flag , buy_id, buy_num, buy_year , buy_date , asset, asset_types, buy_value, buy_price, buy_quant, buy_pool_end , buy_price_prev, value_prev, buy_ratio_prev, buy_gap, buy_date_prev , next_basis_gap , buy_cnt, buy_cnt_before, buy_cnt_after , buy_ratio_before, buy_ratio_after, buy_ratio_all , buy_avg_before, buy_avg_after , buy_ratio_yr , low, high, range_iqr, low_yr, high_yr , max(buy_ratio_prev) over (partition by asset, buy_year) as max_yr_pbr , max(abs(range_iqr)) over (partition by asset) as max_iqr , max(buy_gap) over (partition by asset) as max_gap , sum(iff(buy_ratio_prev > 5, 1, 0)) over (partition by asset order by buy_date rows between 1 following and unbounded following) as price_issues_after -- count number of data issues following , value_yr from PRICES_FLAG order by asset ) , PRICES as ( select distinct buy_flag, buy_num, buy_id, buy_date, asset_types, asset, buy_value, buy_price, buy_quant , buy_price_prev as buy_price_prev , buy_date_prev , buy_gap as days_held_prev , (case when buy_num = 1 then 0 else sum(buy_pool_end) over (partition by asset order by buy_date desc rows between 1 preceding and 1 preceding) end) as buy_pool_start , buy_pool_end , range_iqr , buy_gap , buy_ratio_prev , buy_ratio_all , buy_ratio_yr from FLAG_ANALYSIS order by buy_date, asset ) -- select * from PRICES where asset = 'BTC' order by buy_date desc /* , PRICES_FORMAT as ( select buy_flag, buy_num, buy_id , asset_types, asset , buy_date, buy_value, buy_price, buy_quant , round(buy_pool_start, 4) as buy_pool_start , round(buy_pool_end, 4) as buy_pool_end , buy_gap , buy_price_prev, buy_date_prev, buy_ratio_prev, buy_ratio_all, buy_ratio_yr , range_iqr from PRICES order by buy_num ) select * from PRICES_FORMAT order by asset, buy_num ) --select * from TRES limit 10 /* ---- [4] INVENTORY & PNL ---- ------------------------------------------------------------------------------------------------------------------------------------------ -- a. Calculate inventory, assign lifo cost basis, calculate PnL on aggregated daily trades -- TO DO: -- 1. Dual basis rationalization - 11.8 -- 2. Income considerations? -- 3. DeFi logic for lending rewards / borrow expense , QUATRO as ( with TRADES as ( select tx_num, hash, tag , left(timestamp, 4) as tx_year , try_to_date(timestamp) as tx_date , value_usd, value_ratio , round(value_in, 2) as value_in , round(value_out, 2) as value_out , asset_types as type_in, asset_in, price_in, quantity_in as quant_in , asset_types as type_out, asset_out, price_out, quantity_out as quant_out , round(sum(quant_in) over (partition by asset_in order by tx_date, asset_in rows between unbounded preceding and current row), 4) as cum_in , round(sum(quant_out) over (partition by asset_out order by tx_date, asset_in rows between unbounded preceding and current row), 4) as cum_out , (case when type_out = 'stable' or asset_out = 'USD' then null else round(sum(quant_out) over (partition by asset_out order by tx_num desc rows between unbounded preceding and current row), 4) end) as cum_out_lifo from DOS where tag = 'crypto-swap' and value_ratio between .95 and 1 order by tx_num ) --select * from TRADES order by tx_num /* , BASIS_MATCH as ( select * , (case when type_out = 'stable' or asset_out = 'USD' then null else (select max(buy_date) from TRES where asset_out = asset and tx_date >= buy_date) end) as basis_match , (case when type_out = 'stable' or asset_out = 'USD' then null else (select max(buy_date) from TRES where asset_out = asset and tx_date >= buy_date and cum_out_lifo >= buy_pool_start and cum_out_lifo < buy_pool_end) end) as basis_match_lifo from TRADES order by tx_date desc ) --select * from BASIS_MATCH /* , BASIS as ( select m.* , coalesce(l.buy_num, b.buy_num) as basis_num -- DOUG TO CONFIRM IF COALESCE OF 2 DIFF APPROACHES IS CORRECT , coalesce(l.buy_date, b.buy_date) as basis_date , datediff(day, coalesce(l.buy_date, b.buy_date), m.tx_date) as days_held , coalesce(l.buy_price, b.buy_price) as basis_price , round(coalesce(l.buy_price, b.buy_price) * m.quant_out, 2) as basis , coalesce(l.buy_gap, b.buy_gap) as prev_basis_gap from BASIS_MATCH m left join TRES b on m.asset_out = b.asset and m.basis_match = b.buy_date --basis_match will find last available; fixes issue on days of no inflow left join TRES l on m.asset_out = l.asset and m.basis_match_lifo = l.buy_date order by tx_num desc ) --select * from BASIS /* , REVIEW as ( select * , min(basis_date) over (partition by asset_out order by tx_num desc rows between unbounded preceding and 1 preceding) as basis_min , round((case when first_value(basis_price) over (partition by asset_out, tx_year, quarter(tx_date) order by tx_num) > first_value(price_out) over (partition by asset_out, tx_year, quarter(tx_date) order by tx_num) then first_value(basis_price) over (partition by asset_out, tx_year, quarter(tx_date) order by tx_num) / first_value(price_out) over (partition by asset_out, tx_year, quarter(tx_date) order by tx_num) else first_value(price_out) over (partition by asset_out, tx_year, quarter(tx_date) order by tx_num) / first_value(basis_price) over (partition by asset_out, tx_year, quarter(tx_date) order by tx_num) end), 2) as main_ratio , (case when basis is null and type_out <> 'stable' and type_out <> 'fiat' then 'no-basis' when basis_date > min(basis_date) over (partition by asset_out order by tx_num desc rows between unbounded preceding and 1 preceding) then 'earlier basis for newer tx' else null end) as basis_flag , (case when price_out is null then 'no-price-out' when price_in is null then 'no-price-in' end) as pricing_flag , (case when basis_num is not null then 'lifo' when type_out = 'stable' or asset_out = 'USD' then 'USDx' else 'None' end) as basis_type from BASIS order by tx_num desc ) -- select * from REVIEW order by tx_num /* , INSOUTS as ( select tx_num, asset , quant_in, quant_out , round(sum(quant_in) over (partition by asset order by tx_num), 4) as cum_in_out , round(sum(quant) over (partition by asset order by tx_num), 4) as inv_out from ( (select tx_num, tx_date, asset_in as asset, quant_in as quant, quant_in as quant_in, 0 as quant_out from REVIEW where main_ratio < 100) union (select tx_num, tx_date, asset_out as asset, quant_out as quant, 0 as quant_in, quant_out as quant_out from REVIEW where main_ratio < 100) order by tx_num ) order by tx_num ) --select * from INSOUTS /* , INV as ( select t.* , io.cum_in_out , io.inv_out , iff(t.cum_out <> 0 and io.cum_in_out <> 0, round(t.cum_out / io.cum_in_out, 2), 0) as inv_ratio , (case when t.type_out = 'fiat' or t.type_out = 'stable' then null when io.inv_out < 0 and io.cum_in_out is null then 'invNeg-noBuys' when io.inv_out < 0 and t.cum_out = 0 then 'flag-cum-out-0' when io.inv_out < 0 and io.cum_in_out = 0 then 'flag-cum-in-out-0' when io.inv_out < 0 and t.cum_out / io.cum_in_out < 3 then 'invNeg-under3' when io.inv_out < 0 and t.cum_out / io.cum_in_out >= 3 then 'invNeg-over3' else null end) as inv_flag from REVIEW t left join INSOUTS io on t.tx_num = io.tx_num and t.asset_out = io.asset order by t.tx_num ) -- select * from INV order by tx_num /* , PNL as ( select distinct tx_num , coalesce(pricing_flag, basis_flag) as anyFlag --ignore inv_flag as known issues and not a show stopper , basis_flag, pricing_flag, inv_flag , hash, tx_year, tx_date, tag , value_usd, basis , (case when asset_in = 'UST' then 'stable' else type_in end) as type_in , asset_in, quant_in, price_in, value_in , (case when asset_out = 'UST' then 'stable' else type_out end) as type_out , asset_out, quant_out, price_out, value_out , basis_type, basis_date, basis_price, basis, days_held , iff(days_held > 365, 'LTCG', 'STCG') as term , iff(basis is not null, round(value_out - basis, 4), 0) as pnl , main_ratio, inv_ratio, inv_out from INV order by tx_num ) select distinct * from PNL order by tx_num ) select * from QUATRO order by tx_num -- limit 10 -- */

Query	Creator	Created
TADA 1-4 uniswap kyberswap hashflow maverick dodo	tadaaaa	Nov 9, 2024
TADA.1 uniswap kyberswap hashflow maverick dodo	tadaaaa	Nov 11, 2024

tadaaaa