drone-mostafa2023-06-12 02:01 AM
    SELECT
    DATE_TRUNC ('DAY',t1.BLOCK_TIMESTAMP) AS DATE,
    /*CASE
    when t1.BLOCK_TIMESTAMP >= CURRENT_DATE -7 then 'Last Week'
    when t1.BLOCK_TIMESTAMP >= CURRENT_DATE -14 then '2 weeks ago'
    when t1.BLOCK_TIMESTAMP >= CURRENT_DATE -30 then 'Last Month'
    end as type,*/
    PROJECT_NAME,
    count (DISTINCT t1.TX_HASH) as txn,
    count (DISTINCT t1.TX_SIGNER) as users,
    sum (DEPOSIT) as Volume,

    SUM (txn) OVER (PARTITION BY PROJECT_NAME ORDER BY DATE) AS CUM_TXN,
    SUM (users) OVER (PARTITION BY PROJECT_NAME ORDER BY DATE) AS CUM_users

    FROM near.core.fact_transfers t1
    INNER JOIN near.core.fact_transactions t2 using (TX_HASH)
    LEFT JOIN near.core.dim_address_labels t3 on t1.TX_RECEIVER = ADDRESS
    WHERE t2.TX_RECEIVER in ('usdt.tether-token.near','dac17f958d2ee523a2206206994597c13d831ec7.factory.bridge.near')
    AND t1.BLOCK_TIMESTAMP >= CURRENT_DATE -30
    AND STATUS = 'true'
    AND LABEL_TYPE = 'cex'
    GROUP BY 1,2


    with
    Transfer_T AS ( SELECT
    tx_hash,
    block_timestamp,
    try_parse_json(replace(logs[0]::string, 'EVENT_JSON:', '')) AS EVENTs,
    EVENTs['event']::string AS Method,
    receiver_id AS Token
    FROM near.core.fact_receipts
    WHERE receiver_id = 'usdt.tether-token.near'
    AND object_keys(status_value)[0]::string != 'Failure'
    Run a query to Download Data