SELECT MIN(BLOCK_TIMESTAMP)::DATE FIRST_ACTIVITY_DATE , TX_SENDER AS ADDRESS

FROM terra.core.fact_transactions

GROUP BY TX_SENDER

HAVING FIRST_ACTIVITY_DATE BETWEEN '2022-12-25' AND '2023-01-06'),

NEW_USERS_TRANSACTIONS AS (

SELECT BLOCK_TIMESTAMP::DATE AS DAYS, COUNT(DISTINCT(TX_ID)) "New users transactions"

FROM terra.core.fact_transactions JOIN NEW_USERS_FIRST_ACTIVITY ON (FIRST_ACTIVITY_DATE = BLOCK_TIMESTAMP::DATE AND ADDRESS = TX_SENDER)

GROUP BY DAYS),

TOTAL_TRANSACTIONS AS (

SELECT BLOCK_TIMESTAMP::DATE AS DAYS, COUNT(DISTINCT(TX_ID)) "Total number of transactions"

FROM terra.core.fact_transactions

WHERE DAYS BETWEEN '2022-12-25' AND '2023-01-06'

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-06' THEN 'Second week of holidays'

END AS TYPE

FROM NEW_USERS_TRANSACTIONS JOIN TOTAL_TRANSACTIONS USING (DAYS)

SELECT * FROM OTHER_USERS_TRANSCATIONS