select
-- (base64_decode_string (tx_message:txn:apaa[0]::String))
date_trunc('day',block_timestamp) as block_day,
app_id,
count (tx_message) as outlfow_transactions,
sum (tx_message:dt:itx[0]:txn:aamt::number / 1e6) as outflow_volume
from flipside_prod_db.algorand.application_call_transaction
where
base64_decode_string(tx_message:txn:apaa[0]::String) = 'withdraw'
AND
app_id = 770102986 -- USDC,USDT
group by block_day,app_id
order by block_day
-- Query tips
-- select distinct base64_decode_string(tx_message:txn:apaa[0]::String) from algorand.application_call_transaction where app_id = 770103640