Query editor
WITH-- Base CTE to process transactions oncetransactions_processed AS (SELECTid,block_time,ELEMENT_AT(input, 1).script_pub_key.address AS tx1_in,ELEMENT_AT(input, 1).value AS val1_in,ELEMENT_AT(input, 2).script_pub_key.address AS tx2_in,ELEMENT_AT(input, 2).value AS val2_in,ELEMENT_AT(input, 3).script_pub_key.address AS tx3_in,ELEMENT_AT(input, 3).value AS val3_in,ELEMENT_AT(input, 4).script_pub_key.address AS tx4_in,ELEMENT_AT(input, 4).value AS val4_in,ELEMENT_AT(input, 5).script_pub_key.address AS tx5_in,ELEMENT_AT(input, 5).value AS val5_in,ELEMENT_AT(input, 6).script_pub_key.address AS tx6_in,ELEMENT_AT(input, 6).value AS val6_in,ELEMENT_AT(input, 7).script_pub_key.address AS tx7_in,ELEMENT_AT(input, 7).value AS val7_in,ELEMENT_AT(input, 8).script_pub_key.address AS tx8_in,ELEMENT_AT(input, 8).value AS val8_in,ELEMENT_AT(input, 9).script_pub_key.address AS tx9_in,ELEMENT_AT(input, 9).value AS val9_in,ELEMENT_AT(input, 10).script_pub_key.address AS tx10_in,ELEMENT_AT(input, 10).value AS val10_in,ELEMENT_AT(output, 1).script_pub_key.address AS tx1_out,ELEMENT_AT(output, 1).value AS val1_out,ELEMENT_AT(output, 2).script_pub_key.address AS tx2_out,ELEMENT_AT(output, 2).value AS val2_out,ELEMENT_AT(output, 3).script_pub_key.address AS tx3_out,ELEMENT_AT(output, 3).value AS val3_out,ELEMENT_AT(output, 4).script_pub_key.address AS tx4_out,ELEMENT_AT(output, 4).value AS val4_out,ELEMENT_AT(output, 5).script_pub_key.address AS tx5_out,ELEMENT_AT(output, 5).value AS val5_out,ELEMENT_AT(output, 6).script_pub_key.address AS tx6_out,ELEMENT_AT(output, 6).value AS val6_out,ELEMENT_AT(output, 7).script_pub_key.address AS tx7_out,ELEMENT_AT(output, 7).value AS val7_out,outputFROMbitcoin.transactionsWHEREblock_time > CAST('2023-02-01' AS TIMESTAMP)),-- Marketplace-specific CTEs using the base CTEordinalsmarket_btc AS (SELECTid,block_time,DATE_TRUNC('day', block_time) AS day,'ordinals market btc' AS marketplace,tx4_in AS buyer,tx3_out AS seller,val3_out AS amountFROMtransactions_processed