SELECT * FROM bitcoin.core.fact_transactions WHERE TX_ID='fcd5e53300cafa578c79c79a4718642fde25c8da9cdf88eb6af0dff4df2c5801' SELECT * FROM ethereum.nft.ez_nft_sales where TX_HASH='0xf5a597b3175be75abfcd2b9f1bd44a12648fa98b77ccc46ac125e9724985fee5' SELECT BUYER_ADDRESS ,TX_HASH ,PRICE_USD FROM ethereum.nft.ez_nft_sales where NFT_ADDRESS=lower('0x495f947276749Ce646f68AC8c248420045cb7b5e') and BLOCK_TIMESTAMP <= '2021-09-14 05:14:00.000' and BLOCK_TIMESTAMP >= '2021-09-14 04:00:00.000' and TX_HASH in ( SELECT DISTINCT TX_HASH FROM ethereum.core.fact_transactions where BLOCK_TIMESTAMP <= '2021-09-14 05:14:00.000' and BLOCK_TIMESTAMP >= '2021-09-14 04:00:00.000' and NONCE <= 3 ) AND BUYER_ADDRESS in ( SELECT DISTINCT SELLER_ADDRESS FROM ethereum.nft.ez_nft_sales where NFT_ADDRESS=lower('0x495f947276749Ce646f68AC8c248420045cb7b5e') and BLOCK_TIMESTAMP >= '2021-09-14 05:14:00.000' and BLOCK_TIMESTAMP <= '2021-09-14 06:00:00.000' ) with eth_receiver as( SELECT DISTINCT TO_ADDRESS FROM ethereum.core.fact_traces where FROM_ADDRESS=lower('0xa3a4548b39DA96Eb065FF91811cA30da40431C0D') AND date(BLOCK_TIMESTAMP) <= date('2021-09-14') AND VALUE > 0 ) SELECT count(DISTINCT BUYER_ADDRESS) as buy_addr_cnt ,count(DISTINCT NFT_ADDRESS) as nft_addr_cnt ,sum(PRICE_USD) as PRICE_USD FROM ( SELECT BUYER_ADDRESS ,NFT_ADDRESS ,PRICE_USD FROM ethereum.nft.ez_nft_sales WHERE date(BLOCK_TIMESTAMP) <= date('2021-09-14') AND BUYER_ADDRESS in (SELECT TO_ADDRESS FROM eth_receiver) ) with eth_receiver as( SELECT DISTINCT TO_ADDRESS FROM ethereum.core.fact_traces where FROM_ADDRESS=lower('0xa3a4548b39DA96Eb065FF91811cA30da40431C0D') AND date(BLOCK_TIMESTAMP) <= date('2021-09-14') AND VALUE > 0 ) SELECT count(DISTINCT SELLER_ADDRESS) as sell_addr_cnt ,count(DISTINCT NFT_ADDRESS) as nft_addr_cnt ,sum(PRICE_USD) as PRICE_USD FROM ( SELECT SELLER_ADDRESS ,NFT_ADDRESS ,PRICE_USD FROM ethereum.nft.ez_nft_sales WHERE date(BLOCK_TIMESTAMP) <= date('2021-09-14') AND SELLER_ADDRESS in (SELECT TO_ADDRESS FROM eth_receiver) ) with tgt_hash as( SELECT DISTINCT TX_HASH ,FROM_ADDRESS FROM ethereum.core.fact_transactions where date(BLOCK_TIMESTAMP) <= date('2023-12-31') and date(BLOCK_TIMESTAMP) >= date('2023-12-01') and INPUT_DATA != '0x' ), tgt_address as( SELECT DISTINCT FROM_ADDRESS as address FROM ethereum.core.fact_transactions where date(BLOCK_TIMESTAMP) <= date('2023-12-31') and date(BLOCK_TIMESTAMP) >= date('2023-12-01') AND NONCE=0 ), trade_out as ( SELECT FROM_ADDRESS as address ,-sum(VALUE) as amount ,-count(DISTINCT TX_HASH) as hash_cnt FROM ( SELECT * FROM ( SELECT a.FROM_ADDRESS ,a.VALUE ,a.TX_HASH ,b.FROM_ADDRESS as OR_FROM_ADDRESS FROM ( SELECT FROM_ADDRESS ,VALUE ,TX_HASH FROM ethereum.core.fact_traces where date(BLOCK_TIMESTAMP) <= date('2023-12-31') and date(BLOCK_TIMESTAMP) >= date('2023-12-01') and VALUE > 0 and TX_STATUS='SUCCESS' )a inner join tgt_hash b on a.TX_HASH=b.TX_HASH inner join tgt_address c on a.FROM_ADDRESS=c.address )where OR_FROM_ADDRESS=FROM_ADDRESS )group by FROM_ADDRESS ), trade_in as ( SELECT TO_ADDRESS as address ,sum(VALUE) as amount ,count(DISTINCT TX_HASH) as hash_cnt FROM ( SELECT * FROM ( SELECT a.TO_ADDRESS ,a.VALUE ,a.TX_HASH ,b.FROM_ADDRESS as OR_FROM_ADDRESS FROM ( SELECT TO_ADDRESS ,VALUE ,TX_HASH FROM ethereum.core.fact_traces where date(BLOCK_TIMESTAMP) <= date('2023-12-31') and date(BLOCK_TIMESTAMP) >= date('2023-12-01') and VALUE > 0 and TX_STATUS='SUCCESS' )a inner join tgt_hash b on a.TX_HASH=b.TX_HASH inner join tgt_address c on a.TO_ADDRESS=c.address )WHERE TO_ADDRESS=OR_FROM_ADDRESS )group by TO_ADDRESS ) SELECT * FROM ( SELECT address ,sum(amount) as amount ,sum(case when amount > 0 then amount else 0 end) as in_amount ,-sum(case when amount < 0 then amount else 0 end) as out_amount ,sum(case when hash_cnt > 0 then hash_cnt else 0 end) as in_hash_cnt ,-sum(case when hash_cnt < 0 then hash_cnt else 0 end) as out_hash_cnt FROM ( SELECT * FROM trade_in UNION ALL SELECT * FROM trade_out )group by address ) where in_hash_cnt >= 1 and out_hash_cnt >= 1 order by amount DESC SELECT * FROM ethereum.core.fact_transactions WHERE substr(FROM_ADDRESS,1,31)='0xca1214fa5236e23b6d20ab8563aca' with tgt_hash as( SELECT DISTINCT TX_HASH ,FROM_ADDRESS FROM ethereum.core.fact_transactions WHERE FROM_ADDRESS='0x2fb45d3ae4589ed70e761629f2d34ccf23af7afd' -- where date(BLOCK_TIMESTAMP) <= date('2023-12-31') -- and date(BLOCK_TIMESTAMP) >= date('2023-12-01') and INPUT_DATA != '0x' ), tgt_address as( SELECT DISTINCT FROM_ADDRESS as address FROM ethereum.core.fact_transactions WHERE FROM_ADDRESS='0x2fb45d3ae4589ed70e761629f2d34ccf23af7afd' -- where date(BLOCK_TIMESTAMP) <= date('2023-12-31') -- and date(BLOCK_TIMESTAMP) >= date('2023-12-01') -- AND NONCE=0 ), trade_out as ( SELECT FROM_ADDRESS as address ,-sum(VALUE) as amount ,-count(DISTINCT TX_HASH) as hash_cnt FROM ( SELECT * FROM ( SELECT a.FROM_ADDRESS ,a.VALUE ,a.TX_HASH ,b.FROM_ADDRESS as OR_FROM_ADDRESS FROM ( SELECT FROM_ADDRESS ,VALUE ,TX_HASH FROM ethereum.core.fact_traces -- where date(BLOCK_TIMESTAMP) <= date('2023-12-31') -- and date(BLOCK_TIMESTAMP) >= date('2023-12-01') WHERE VALUE > 0 and TX_STATUS='SUCCESS' )a inner join tgt_hash b on a.TX_HASH=b.TX_HASH inner join tgt_address c on a.FROM_ADDRESS=c.address )where OR_FROM_ADDRESS=FROM_ADDRESS )group by FROM_ADDRESS ), trade_in as ( SELECT TO_ADDRESS as address ,sum(VALUE) as amount ,count(DISTINCT TX_HASH) as hash_cnt FROM ( SELECT * FROM ( SELECT a.TO_ADDRESS ,a.VALUE ,a.TX_HASH ,b.FROM_ADDRESS as OR_FROM_ADDRESS FROM ( SELECT TO_ADDRESS ,VALUE ,TX_HASH FROM ethereum.core.fact_traces -- where date(BLOCK_TIMESTAMP) <= date('2023-12-31') -- and date(BLOCK_TIMESTAMP) >= date('2023-12-01') where VALUE > 0 and TX_STATUS='SUCCESS' )a inner join tgt_hash b on a.TX_HASH=b.TX_HASH inner join tgt_address c on a.TO_ADDRESS=c.address )WHERE TO_ADDRESS=OR_FROM_ADDRESS )group by TO_ADDRESS ) SELECT * FROM ( SELECT address ,sum(amount) as amount ,sum(case when amount > 0 then amount else 0 end) as in_amount ,-sum(case when amount < 0 then amount else 0 end) as out_amount ,sum(case when hash_cnt > 0 then hash_cnt else 0 end) as in_hash_cnt ,-sum(case when hash_cnt < 0 then hash_cnt else 0 end) as out_hash_cnt FROM ( SELECT * FROM trade_in UNION ALL SELECT * FROM trade_out )group by address ) where in_hash_cnt >= 1 and out_hash_cnt >= 1 order by amount DESC with token_hour_price as( SELECT TOKEN_ADDRESS ,substr(HOUR,1,13) as date ,price FROM ethereum.price.ez_hourly_token_prices WHERE price > 0 ), tgt_hash as( SELECT DISTINCT TX_HASH ,FROM_ADDRESS FROM ethereum.core.fact_transactions WHERE FROM_ADDRESS='0x2fb45d3ae4589ed70e761629f2d34ccf23af7afd' and INPUT_DATA != '0x' ), tgt_address as( SELECT DISTINCT FROM_ADDRESS as address FROM ethereum.core.fact_transactions WHERE FROM_ADDRESS='0x2fb45d3ae4589ed70e761629f2d34ccf23af7afd' ), trade_out as ( SELECT FROM_ADDRESS as address ,-sum(VALUE) as amount ,-count(DISTINCT TX_HASH) as hash_cnt ,-sum(amount_usd) as amount_usd FROM ( SELECT * FROM ( SELECT a.FROM_ADDRESS ,a.VALUE ,a.TX_HASH ,b.FROM_ADDRESS as OR_FROM_ADDRESS ,a.date ,a.VALUE * d.price as amount_usd FROM ( SELECT FROM_ADDRESS ,VALUE ,TX_HASH ,substr(BLOCK_TIMESTAMP,1,13) as date ,'0xc02aaa39b223fe8d0a0e5c4f27ead9083c756cc2' as TOKEN_ADDRESS FROM ethereum.core.fact_traces WHERE VALUE > 0 and TX_STATUS='SUCCESS' )a inner join tgt_hash b on a.TX_HASH=b.TX_HASH inner join tgt_address c on a.FROM_ADDRESS=c.address left join token_hour_price d on a.date=d.date )where OR_FROM_ADDRESS=FROM_ADDRESS )group by FROM_ADDRESS ), trade_in as ( SELECT TO_ADDRESS as address ,sum(VALUE) as amount ,count(DISTINCT TX_HASH) as hash_cnt ,sum(amount_usd) as amount_usd FROM ( SELECT * FROM ( SELECT a.TO_ADDRESS ,a.VALUE ,a.TX_HASH ,b.FROM_ADDRESS as OR_FROM_ADDRESS ,a.date ,a.VALUE * d.price as amount_usd FROM ( SELECT TO_ADDRESS ,VALUE ,TX_HASH ,substr(BLOCK_TIMESTAMP,1,13) as date ,'0xc02aaa39b223fe8d0a0e5c4f27ead9083c756cc2' as TOKEN_ADDRESS FROM ethereum.core.fact_traces where VALUE > 0 and TX_STATUS='SUCCESS' )a inner join tgt_hash b on a.TX_HASH=b.TX_HASH inner join tgt_address c on a.TO_ADDRESS=c.address left join token_hour_price d on a.date=d.date )WHERE TO_ADDRESS=OR_FROM_ADDRESS )group by TO_ADDRESS ) SELECT * FROM ( SELECT address ,sum(amount) as amount -- ,sum(amount_usd) as amount_usd ,sum(case when amount > 0 then amount else 0 end) as in_amount ,-sum(case when amount < 0 then amount else 0 end) as out_amount ,sum(case when hash_cnt > 0 then hash_cnt else 0 end) as in_hash_cnt ,-sum(case when hash_cnt < 0 then hash_cnt else 0 end) as out_hash_cnt FROM ( SELECT * FROM trade_in UNION ALL SELECT * FROM trade_out )group by address ) where in_hash_cnt >= 1 and out_hash_cnt >= 1 order by amount DESC

Query	Creator	Created
btc_test bitcoin transactions	70a2e949-aef6-4c96-af6a-45f9498f3a07	Jan 6, 2024
sad-bronze bitcoin transfers	70a2e949-aef6-4c96-af6a-45f9498f3a07	Jul 25, 2024

70a2e949-aef6-4c96-af6a-45f9498f3a07