niloCopy of fin
    Updated 2022-10-19
    WITH

    ACTIVE AS (SELECT DATE_TRUNC('WEEK',BLOCK_TIMESTAMP) AS WEEKLY, COUNT ( DISTINCT BLOCK_TIMESTAMP::DATE) as N_DAY, TX_FROM AS WALLET
    FROM osmosis.core.fact_transactions WHERE BLOCK_TIMESTAMP >= CURRENT_DATE - interval'6 MONTH' GROUP BY WEEKLY,WALLET HAVING N_DAY >= 4)

    SELECT COUNT (DISTINCT TX_ID) AS TXN, COUNT (DISTINCT RECEIVER) AS USERS,
    CASE
    WHEN DAYOFWEEK(DATE_TRUNC('DAY',BLOCK_TIMESTAMP)) = 0 THEN 'Sunday'
    WHEN DAYOFWEEK(DATE_TRUNC('DAY',BLOCK_TIMESTAMP)) = 1 THEN 'Monday'
    WHEN DAYOFWEEK(DATE_TRUNC('DAY',BLOCK_TIMESTAMP)) = 2 THEN 'Tuesday'
    WHEN DAYOFWEEK(DATE_TRUNC('DAY',BLOCK_TIMESTAMP)) = 3 THEN 'Wednesday'
    WHEN DAYOFWEEK(DATE_TRUNC('DAY',BLOCK_TIMESTAMP)) = 4 THEN 'Thursday'
    WHEN DAYOFWEEK(DATE_TRUNC('DAY',BLOCK_TIMESTAMP)) = 5 THEN 'Friday'
    WHEN DAYOFWEEK(DATE_TRUNC('DAY',BLOCK_TIMESTAMP)) = 6 THEN 'Saturday'
    END as TYPE
    FROM osmosis.core.fact_transfers WHERE RECEIVER IN (SELECT WALLET FROM ACTIVE) AND BLOCK_TIMESTAMP >= CURRENT_DATE - interval'6 MONTH' AND BLOCK_TIMESTAMP < CURRENT_DATE
    AND TRANSFER_TYPE = 'IBC_TRANSFER_IN' AND TX_STATUS = 'SUCCEEDED' GROUP BY 3
    Run a query to Download Data