ramishoowUntitled Query
Updated 2023-01-13
99
1
2
3
4
5
6
7
8
9
10
11
›
⌄
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