SELECT

tx_id,

block_timestamp,

DATE_TRUNC('day', block_timestamp) AS day,

'Ordwallet' AS marketplace,

tx1_in AS buyer,

tx2_in AS seller,

flatten_Outputs.value:value / 0.027 as amount,

flatten_Outputs.value:value / 0.027 * price as amount_usd

FROM

(

SELECT

tx_id,

MAX(block_timestamp) as block_timestamp,

MAX(CASE WHEN input.index = 0 THEN input.PUBKEY_SCRIPT_ADDRESS END) as tx1_in,

MAX(CASE WHEN input.index = 0 THEN input.VALUE END) as val1_in,

MAX(CASE WHEN input.index = 1 THEN input.PUBKEY_SCRIPT_ADDRESS END) as tx2_in,

MAX(CASE WHEN input.index = 1 THEN input.VALUE END) as val2_in,

MAX(CASE WHEN input.index = 2 THEN input.PUBKEY_SCRIPT_ADDRESS END) as tx3_in,

MAX(CASE WHEN input.index = 2 THEN input.VALUE END) as val3_in,

MAX(CASE WHEN input.index = 3 THEN input.PUBKEY_SCRIPT_ADDRESS END) as tx4_in,

MAX(CASE WHEN input.index = 3 THEN input.VALUE END) as val4_in,

MAX(CASE WHEN output.index = 0 THEN output.PUBKEY_SCRIPT_ADDRESS END) as tx1_out,

MAX(CASE WHEN output.index = 0 THEN output.VALUE END) as val1_out,

MAX(CASE WHEN output.index = 1 THEN output.PUBKEY_SCRIPT_ADDRESS END) as tx2_out,

MAX(CASE WHEN output.index = 1 THEN output.VALUE END) as val2_out,

MAX(CASE WHEN output.index = 2 THEN output.PUBKEY_SCRIPT_ADDRESS END) as tx3_out,

MAX(CASE WHEN output.index = 2 THEN output.VALUE END) as val3_out,

MAX(CASE WHEN output.index = 3 THEN output.PUBKEY_SCRIPT_ADDRESS END) as tx4_out,

MAX(CASE WHEN output.index = 3 THEN output.VALUE END) as val4_out,

transactions.OUTPUTS as OUTPUTS

FROM bitcoin.core.fact_inputs input join bitcoin.core.fact_outputs output using(block_timestamp, tx_id)

join bitcoin.core.fact_transactions transactions using(block_timestamp, tx_id)

where block_timestamp between '2023-02-01' and '2024-06-06'