SELECT

date_trunc('hour', block_timestamp) AS hour,

block_timestamp,

tx_id,

e.signers[0] AS market_maker,

e.signers[1] AS trader,

instruction:data AS encoded_data,

utils.udf_base58_to_hex(encoded_data) AS hex_data, -- Decoded Data

livequery.utils.udf_hex_to_int(to_char(reverse(to_binary(substr(hex_data, 3 + 16, 16))))) as sold_amount,

instruction:accounts[6] AS sold_token,

livequery.utils.udf_hex_to_int(to_char(reverse(to_binary(substr(hex_data, 3 + 16 + 16, 16))))) as bought_amount,

instruction:accounts[8] AS bought_token

FROM solana.core.fact_events e

where 1 = 1

AND succeeded

AND block_timestamp >= '2024-12-10' -- Cropping to this date because there were very little transactions prior

AND program_id = '61DFfeTKM7trxYcPQCM78bJ794ddZprZpAwAnLiwTpYH'

),

asset_data AS (

SELECT

hour,

block_timestamp,

tx_id,

market_maker,

trader,

bought_amount,

CASE

WHEN bought_token = 'So11111111111111111111111111111111111111111' THEN 'So11111111111111111111111111111111111111112'

ELSE bought_token

END AS bought_token,

sold_amount,

CASE

WHEN sold_token = 'So11111111111111111111111111111111111111111' THEN 'So11111111111111111111111111111111111111112'

ELSE sold_token