iboo-jbj2MVgoBTC Liquidity
    Updated 2022-02-18
    WITH
    Temporary_liquidity AS (
    SELECT
    block_timestamp,
    tx_group_id
    FROM algorand.application_call_transaction
    WHERE
    app_id = 465814149
    AND block_timestamp >= '2021-12-17'
    AND block_timestamp < '2022-02-01'
    AND try_base64_decode_string(tx_message:txn:note::string) = 'Market: mt'
    GROUP BY block_timestamp, tx_group_id
    ),
    Temporary_liquidity_amount AS (
    SELECT
    CAST(date_trunc('day',algorand.asset_transfer_transaction.block_timestamp) AS DATE) AS which_day,
    sum(asset_amount) AS liquidity_amount,
    COUNT(DISTINCT sender) AS sender_count
    FROM Temporary_liquidity
    JOIN algorand.asset_transfer_transaction
    ON Temporary_liquidity.tx_group_id = algorand.asset_transfer_transaction.tx_group_id
    GROUP BY which_day
    ),
    Temporary_remove_liquidity_tx_group_ids AS (
    SELECT
    CAST(date_trunc('day',block_timestamp) AS DATE) AS which_day,
    sum(tx_message:dt:itx[0]:txn:aamt/1e6) AS withdraw_liquidity_amount,
    COUNT(DISTINCT sender) AS number_of_withdraw_wallets
    FROM algorand.application_call_transaction
    WHERE
    app_id = 465814149
    AND block_timestamp >= '2021-12-17'
    AND block_timestamp < '2022-02-01'
    AND try_base64_decode_string(tx_message:txn:note::string) = 'Market: rcu'
    AND tx_message:dt:itx[0]:txn:aamt IS NOT NULL
    GROUP BY which_day
    Run a query to Download Data