select

a.asset_name,

tx_message:dt:itx[0]:txn:xaid as a_id,

tx_message:txn:apid as app_id,

CASE

WHEN tx_message:txn:amt is not null THEN tx_message:txn:amt/pow(10,6) -- providing repaying ALGO

WHEN tx_message:txn:aamt is not null THEN tx_message:txn:aamt/pow(10,6) -- providing repaying Non-ALGO

WHEN tx_message:dt:itx[0]:txn:amt is not null THEN tx_message:dt:itx[0]:txn:amt/pow(10,6) -- withdrawing borrowing ALGO

WHEN tx_message:dt:itx[0]:txn:aamt is not null THEN tx_message:dt:itx[0]:txn:aamt/pow(10,6) -- withdrawing borrowing Non-ALGO

END as amount,

TRY_BASE64_DECODE_STRING(tx_message :txn :apaa [0] :: STRING) as message,

CASE

WHEN message = 'm' THEN 'Deposit'

WHEN message = 'r' THEN 'Redeem'

WHEN message = 'l' THEN 'Liquidation'

END as status,

t.*

from algorand.transactions t join algorand.asset a on tx_message:dt:itx[0]:txn:xaid = a.asset_id

where message in ('m','r', 'l')

and block_timestamp >= '2022-03-01'

--and a_id in ('0','31566704','312769','386192725','386195940','694432641','694683000','701364134')

select

block_timestamp::date as date,

status,

status || ' : ' || asset_name as name,

count(distinct tx_group_id) as n_txns,

count(distinct sender) as n_wallets,

sum(amount) as asset_amount,

avg(amount) as avg_asset_amount,

sum(n_txns) over (partition by name order by date ) as growth_n_txns,

sum(n_wallets) over (partition by name order by date ) as growth_n_wallets,

sum(asset_amount) over (partition by name order by date ) as growth_asset_amount,

sum(avg_asset_amount) over (partition by name order by date ) as growth_avg_asset_amount,