SELECT DISTINCT tx_message:txn:apid::number AS app_id

FROM algorand.transactions --

WHERE inner_tx = 'FALSE'

AND tx_message:dt:itx[0]:txn:type::string = 'acfg'

AND tx_message:dt:itx[0]:txn:apar:an::string LIKE '%PACT LP Token'

AND tx_message:dt:itx[0]:txn:apar:au::string = 'https://pact.fi/'

)

select

block_timestamp::date as date,

tx_message:dt:itx[0]:txn:xaid::number AS asset_id,

count(tx_id) as num_tx,

tx_message:txn:apid::number AS app_id,

case when try_BASE64_DECODE_STRING(tx_message:txn:apaa[0]::string) = 'ADDLIQ' then 'mint'

else 'burn' end as mint_or_burn,

sum(tx_message:dt:itx[0]:txn:aamt::number/pow(10,6)) as sum_amount

from algorand.application_call_transaction

where app_id IN (SELECT app_id FROM pact_app_ids)

And try_BASE64_DECODE_STRING(tx_message:txn:apaa[0]::string) = 'ADDLIQ'

OR TRY_BASE64_DECODE_STRING(tx_message:txn:apaa[0]::string) = 'REMLIQ'

group by 1, 2, 4, 5

order by 3 DESC