SELECT

block_timestamp::date as fecha,

tx_group_id,

CASE tx_message:txn:apid

WHEN 354073834 then 'ASA'

WHEN 354073718 then 'ALGO'

END AS ESCROW

FROM

algorand.transactions

WHERE

ESCROW IN ('ASA', 'ALGO')

AND fecha = '2022-02-10'

),

ORDER_TYPE AS (

SELECT

BLOCK_TIMESTAMP,

TX_GROUP_ID,

TX_MESSAGE:txn:amt/pow(10,6) as ALGO_amount,

CASE tx_message:txn:apid

WHEN 354073834 then 'ASA'

WHEN 354073718 then 'ALGO'

END AS ESCROW,

--INTRA,

ASSET_ID,

--TX_TYPE_NAME,

--TX_MESSAGE,

REGEXP_SUBSTR( TRY_BASE64_DECODE_STRING(TX_MESSAGE:txn:note), '.* ":(.*)', 1, 1, 'e', 1) as substr_note,

PARSE_JSON(SUBSTRING( substr_note, 0, LEN(substr_note) -1 )) as json_note,

json_note:escrowAddr as escrow_wallet,

json_note:escrowOrderType as OrderType

FROM

algorand.transactions

WHERE

tx_group_id IN (SELECT tx_group_id FROM TXGROUP)