lagandispenserThe number of depositors, borrow and redeemers over time
    Updated 2022-07-02
    select
    block_timestamp::date as date ,
    case when APP_ID = 686498781 then 'ALGO'
    when APP_ID = 686500029 then 'USDC'
    when APP_ID = 686500844 then 'USDt'
    when APP_ID = 686501760 then 'goBTC'
    when APP_ID = 694405065 then 'goETH'
    when APP_ID = 694464549 then 'gALGO3'
    when APP_ID = 743679535 then 'ALGO/gALGO3 TMP1.1'
    when APP_ID = 743685742 then 'ALGO/gALGO3 PLP'
    when APP_ID = 747239433 then 'ALGO/USDC PLP'
    when APP_ID = 747237154 then 'ALGO/USDC TMP1.1'
    end as pool,
    case when TRY_BASE64_DECODE_STRING(tx_message :txn :apaa [0] :: STRING) = 'd' then 'deposit'
    when TRY_BASE64_DECODE_STRING(tx_message :txn :apaa [0] :: STRING) = 'r' then 'redeem'
    when TRY_BASE64_DECODE_STRING(tx_message :txn :apaa [0] :: STRING) = 'b' then 'borrow'
    end as type ,
    count( DISTINCT tx_group_id) as num_transactions,
    count(DISTINCT sender ) as senders ,
    sum (ASSET_AMOUNT) as amount,
    sum (amount) over (partition by pool,type order by date ) as cum_amount
    from flipside_prod_db.algorand.application_call_transaction join flipside_prod_db.algorand.asset_transfer_transaction using (tx_group_id)
    where APP_ID in (686498781,686500029,686500844,686501760,694405065,694464549,743679535,743685742,747237154,747239433) and ASSET_AMOUNT >0
    group by 1,2,3 having pool is not null and type is not null


    Run a query to Download Data