ALMX_Tx_1 AS (

SELECT block_timestamp, caller, asset_swapped, fee_taken/POW(10,decimals) as ALMX_fee, ALMX_fee*price_usd as ALMX_fee_USD, amount_USDC, num_groups,

group_tx0, group_tx1, group_tx2, group_tx3, group_tx4, group_tx5, group_tx6,

tx_id, block_id

FROM (

(

SELECT block_timestamp, sender as caller, asset_transferred as asset_swapped, asset_amount as fee_taken, try_BASE64_decode_string(tx_message:txn:note) as decoded, try_parse_json(decoded):volumeUSDC/1e6 as amount_USDC,

(len(try_parse_json(decoded):groupIds)-1)/47 as num_groups, try_parse_json(decoded):groupIds[0] as group_tx0, try_parse_json(decoded):groupIds[1] as group_tx1,

try_parse_json(decoded):groupIds[2] as group_tx2, try_parse_json(decoded):groupIds[3] as group_tx3, try_parse_json(decoded):groupIds[4] as group_tx4, try_parse_json(decoded):groupIds[5] as group_tx5,

try_parse_json(decoded):groupIds[6] as group_tx6,

tx_id, block_id

FROM flipside_prod_db.algorand.asset_transfer_transaction

WHERE asset_receiver = 'H5T5FHYHVC2GFEZS6YQP27QVNCSMSNXCVMALDONJXFCL3L5NN4BKDMISGA'

) UNION

(

SELECT block_timestamp, sender as caller, asset_id as asset_swapped, amount*1e6 as fee_taken, try_BASE64_decode_string(tx_message:txn:note) as decoded, try_parse_json(decoded):volumeUSDC/1e6 as amount_USDC,

(len(try_parse_json(decoded):groupIds)-1)/47 as num_groups, try_parse_json(decoded):groupIds[0] as group_tx0, try_parse_json(decoded):groupIds[1] as group_tx1,

try_parse_json(decoded):groupIds[2] as group_tx2, try_parse_json(decoded):groupIds[3] as group_tx3, try_parse_json(decoded):groupIds[4] as group_tx4, try_parse_json(decoded):groupIds[5] as group_tx5,

try_parse_json(decoded):groupIds[6] as group_tx6,

tx_id, block_id

FROM flipside_prod_db.algorand.payment_transaction

WHERE receiver = 'H5T5FHYHVC2GFEZS6YQP27QVNCSMSNXCVMALDONJXFCL3L5NN4BKDMISGA'

)

) T LEFT JOIN flipside_prod_db.algorand.prices_swap P ON (date_trunc('hour',T.block_timestamp) = P.block_hour AND T.asset_swapped = P.asset_id)

LEFT JOIN flipside_prod_db.algorand.asset A ON T.asset_swapped = A.asset_id

),

ALMX_Tx_2 AS ( -- Fill ones where groups are present, but usdc is missing !! Most of the Group tx_ids in the notes can not be found! (only 12 out of 12...)

SELECT block_timestamp, caller, asset_swapped, ALMX_fee, ALMX_fee_USD, asset_transacted/POW(10,decimals)*price_usd as amount_USDC, num_groups,

tx_id

FROM (SELECT * FROM (

(

SELECT A.tx_id, sum(asset_amount) as asset_transacted

FROM ALMX_Tx_1 A LEFT JOIN flipside_prod_db.algorand.asset_transfer_transaction T ON (

T.tx_group_id IN (A.group_tx0, A.group_tx1, A.group_tx2, A.group_tx3, A.group_tx4, A.group_tx5, A.group_tx6)