ErsvanCopy of Popularity stablecoin
    Updated 2022-06-03
    WITH w_transactions (MONTH_DATE, ASSET_NAME, SENDER, TX_ID, ASSET_ID) AS
    (
    SELECT
    DATE_TRUNC('month', block_timestamp) AS MONTH_DATE,
    ASSET_NAME,
    SENDER AS SENDER,
    TX_ID,
    asset.asset_id AS asset_id
    FROM
    algorand.transactions
    LEFT JOIN algorand.asset ON algorand.transactions.asset_id = algorand.asset.asset_id
    WHERE
    (
    asset.ASSET_ID = 31566704 --USDC
    OR asset.ASSET_ID = 312769 --Tether USDt
    OR asset.ASSET_ID = 2757561 --realioUSD
    OR asset.ASSET_ID = 465865291 --STBL
    OR asset.ASSET_ID = 684649988 --GARD
    OR asset.ASSET_ID = 506189856 --NCoin USD
    )
    AND TX_TYPE_NAME = 'asset transfer'
    AND TX_MESSAGE:txn:aamt IS NOT NULL
    )

    SELECT
    w_transactions.MONTH_DATE
    ,USDT ,USDC
    FROM
    w_transactions
    LEFT JOIN (SELECT COUNT(SENDER) AS USDT FROM w_transactions WHERE ASSET_ID = 312769 GROUP BY sender) AS tUSDT ON w_transactions.MONTH_DATE = tUSDT.MONTH_DATE
    LEFT JOIN (SELECT COUNT(SENDER) AS USDC FROM w_transactions WHERE ASSET_ID = 31566704 GROUP BY sender) AS tUSDC ON w_transactions.MONTH_DATE = tUSDC.MONTH_DATE
    GROUP BY w_transactions.MONTH_DATE, USDC, USDT
    ORDER BY 1


    Run a query to Download Data