barbodUntitled Query
    Updated 2022-05-25
    With pact_app_ids as(
    SELECT
    DISTINCT tx_message :txn :apid :: NUMBER AS app_id
    FROM
    flipside_prod_db.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/'
    ),
    pactfi as (
    select
    block_timestamp::date as date ,
    case when asset_name ='ALGO/goETH PACT LP Token' then 'goETH'
    when asset_name ilike '%Algo/goBTC%' then 'goBTC'
    end as asset,
    case when TRY_BASE64_DECODE_STRING(tx_message :txn :apaa [0] :: STRING) = 'ADDLIQ' then 'Mint'
    when TRY_BASE64_DECODE_STRING(tx_message :txn :apaa [0] :: STRING) = 'REMLIQ' then 'Burn'
    end as type ,
    count (DISTINCT sender ) as sender ,
    count (tx_group_id) as num_transactions,
    sum(asset_amount/1e6) as amount
    from flipside_prod_db.algorand.application_call_transaction pact_app_ids join flipside_prod_db.algorand.asset_transfer_transaction using (tx_group_id)
    join flipside_prod_db.algorand.asset using (asset_id)
    where app_id in (select * from pact_app_ids)
    group by 1,2,3 having asset is not null and type is not NULL
    ),
    algofi as (
    select block_timestamp::date as date ,
    case when app_id =635864509 then 'goETH'
    when app_id =635860537 then 'goBTC'
    end as asset ,
    case when try_base64_decode_string(tx_message:txn:note::string) ='Market: mt' then 'Mint'
    when try_base64_decode_string(tx_message:txn:note::string) ='Market: rcu' then 'Burn'
    end as type ,
    Run a query to Download Data