ramishoowUntitled Query
    Updated 2023-01-13
    WITH NEW_USERS_FIRST_ACTIVITY AS ( SELECT MIN(BLOCK_TIMESTAMP)::DATE FIRST_ACTIVITY_DATE , TX_SIGNER AS ADDRESS FROM near.core.fact_transactions GROUP BY TX_SIGNER
    HAVING FIRST_ACTIVITY_DATE BETWEEN '2022-12-25' AND '2023-01-13'), NEW_USERS_TRANSACTIONS AS (
    SELECT BLOCK_TIMESTAMP::DATE AS DAYS, COUNT(DISTINCT(TX_HASH)) "New users transactions"
    FROM near.core.fact_transactions JOIN NEW_USERS_FIRST_ACTIVITY ON (FIRST_ACTIVITY_DATE = BLOCK_TIMESTAMP::DATE AND ADDRESS = TX_SIGNER)
    --FROM near.core.fact_transactions JOIN NEW_USERS_FIRST_ACTIVITY ON (FIRST_ACTIVITY_DATE = BLOCK_TIMESTAMP::DATE AND ADDRESS = TX_SIGNER)
    GROUP BY DAYS), TOTAL_TRANSACTIONS AS ( SELECT BLOCK_TIMESTAMP::DATE AS DAYS, COUNT(DISTINCT(TX_HASH)) "Total number of transactions"
    FROM near.core.fact_transactions WHERE DAYS BETWEEN '2022-12-25' AND '2023-01-13' GROUP BY DAYS) OTHER_USERS_TRANSCATIONS AS (
    SELECT NEW_USERS_TRANSACTIONS.DAYS, "New users transactions", "Total number of transactions", "Total number of transactions" - "New users transactions" AS "Other users transactions",
    ("New users transactions" / "Other users transactions") * 100 AS "Share of new users transactions from total transactions", CASE
    WHEN DAYS BETWEEN '2022-12-25' AND '2022-12-31' THEN 'First week of holidays' WHEN DAYS BETWEEN '2023-01-01' AND '2023-01-13' THEN 'Second week of holidays'
    END AS TYPE FROM NEW_USERS_TRANSACTIONS JOIN TOTAL_TRANSACTIONS USING (DAYS) ) SELECT * FROM OTHER_USERS_TRANSCATIONS
    Run a query to Download Data