lagandispenserWeekly Liquidity STBL
    Updated 2022-05-23
    with algofi_groupids as (
    select
    tx_group_id,
    try_base64_decode_string(tx_message:txn:note::string) as message,
    tx_message:txn:apid as app_id,
    CASE
    WHEN app_id = '465814065' THEN 'ALGO'
    WHEN app_id = '465814103' THEN 'USDC'
    WHEN app_id = '465814149' THEN 'goBTC'
    WHEN app_id = '465814222' THEN 'goETH'
    WHEN app_id = '465814278' THEN 'STBL'
    END as asset_name,
    CASE
    WHEN message = 'Market: mt' THEN 'Providing'
    WHEN message = 'Market: rcu' THEN 'Withdrawing'
    WHEN message = 'Market: b' THEN 'Borrowing'
    WHEN message = 'Market: rb' THEN 'Repaying'
    END as liquidity_type
    from algorand.transactions
    where app_id in ('465814065','465814103','465814149','465814222','465814278')
    and message in ('Market: mt', 'Market: rcu', 'Market: b', 'Market: rb')
    and block_timestamp::date between '2021-12-17' and '2022-02-01'
    ),
    liquidity_txns as (
    select
    g.asset_name,
    g.liquidity_type,
    t.block_timestamp,
    t.sender,
    CASE
    WHEN t.tx_message:txn:amt is not null THEN t.tx_message:txn:amt/pow(10,6) -- providing repaying ALGO
    WHEN t.tx_message:txn:aamt is not null THEN t.tx_message:txn:aamt/pow(10,6) -- providing repaying Non-ALGO
    WHEN t.tx_message:dt:itx[0]:txn:amt is not null THEN t.tx_message:dt:itx[0]:txn:amt/pow(10,6) -- withdrawing borrowing ALGO
    WHEN t.tx_message:dt:itx[0]:txn:aamt is not null THEN t.tx_message:dt:itx[0]:txn:aamt/pow(10,6) -- withdrawing borrowing Non-ALGO
    END as amount
    -- t.*
    Run a query to Download Data