transactions as (

select

transactions.block_timestamp,

transactions.tx_id,

transactions.outputs[1]:scriptPubKey:address::string AS buyer,

input3.pubkey_script_address AS seller,

transactions.outputs[2]:value AS amount,

amount * prices.price as amount_usd,

0 as platform_fee,

0 as platform_fee_usd

from

bitcoin.core.fact_transactions transactions

join bitcoin.price.ez_hourly_token_prices prices on date_trunc('hour', transactions.block_timestamp) = prices.hour

join bitcoin.core.fact_inputs input1 on transactions.tx_id = input1.tx_id

join bitcoin.core.fact_inputs input2 on transactions.tx_id = input2.tx_id

join bitcoin.core.fact_inputs input3 on transactions.tx_id = input3.tx_id

join bitcoin.core.fact_inputs input4 on transactions.tx_id = input4.tx_id

where

transactions.block_number > 767429

and input1.index = 0

and input2.index = 1

and input3.index = 2

and input4.index = 3

and input1.value = 0.000006

and input2.value = 0.000006

and outputs[0]:value = 0.000012

and input1.pubkey_script_address = transactions.outputs[0]:scriptPubKey:address

and input2.pubkey_script_address = transactions.outputs[0]:scriptPubKey:address

and input4.pubkey_script_address = transactions.outputs[0]:scriptPubKey:address

and input3.pubkey_script_address != transactions.outputs[0]:scriptPubKey:address

and (

transactions.outputs[3]:scriptPubKey:address = 'bc1qyylrgsxjrmaearjqqradhy8ferh4u0ydw4yuze'

or (

input3.pubkey_script_address = transactions.outputs[2]:scriptPubKey:address

and transactions.outputs[3]:scriptPubKey:address is null