Zanyar_98Number of transactions of AUs and number of transactions of regular users - Daily chart
    Updated 2023-04-13
    WITH DATE_GENERATOR AS (
    SELECT '2022-05-27'::date+x AS DAYS
    FROM (
    SELECT row_number() OVER(ORDER BY 0) x FROM TABLE(GENERATOR(ROWCOUNT => 320)))
    ),

    FIRST_ACTIVITY AS (
    SELECT TX_SENDER AS "Address", MIN(BLOCK_TIMESTAMP::DATE) "First activity", 1 AS TEMP
    FROM terra.core.fact_transactions
    GROUP BY "Address"
    ),

    DAILY_TRANSACTIONS AS (
    SELECT BLOCK_TIMESTAMP::DATE AS DAYS, TX_SENDER AS "Address", 1 AS "Temp"
    FROM terra.core.fact_transactions
    GROUP BY DAYS, "Address"
    ORDER BY DAYS
    ),
    ACCOUNTS_AGE AS (
    SELECT DATE_GENERATOR.DAYS, "Address", "First activity", DATEDIFF(DAY, "First activity", DAYS) +1 AS "Address current age"
    FROM DATE_GENERATOR LEFT JOIN First_Activity ON ("First activity" <= DAYS)
    ),

    ACCOUNTS_AND_TRANSACTIONS AS (
    SELECT ACCOUNTS_AGE.DAYS, ACCOUNTS_AGE."Address", "First activity", "Address current age", DAILY_TRANSACTIONS.DAYS AS "Transaction day", "Temp"
    FROM ACCOUNTS_AGE LEFT JOIN DAILY_TRANSACTIONS ON
    (ACCOUNTS_AGE.DAYS = DAILY_TRANSACTIONS.DAYS AND ACCOUNTS_AGE."Address" = DAILY_TRANSACTIONS."Address" )
    ),

    ACCOUNTS_AND_TRANSACTIONS_ACTIVE_DAYS AS (
    SELECT DAYS, "Address", "First activity", "Address current age", "Transaction day",
    SUM("Temp") OVER (Partition By "Address" ORDER by DAYS ASC ROWS BETWEEN unbounded preceding AND CURRENT ROW) "Current Active Days",
    "Current Active Days" / "Address current age"
    FROM ACCOUNTS_AND_TRANSACTIONS),

    Run a query to Download Data