Rodolfo-LimaPolygon Daily TXs & Unique Address
Updated 2022-07-26
99
1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
28
›
⌄
⌄
/*
Q1. Count and plot the number of daily transactions and unique addresses on Polygon, beginning July 1, 2022.
In your estimation, discuss whether Polygon is still growing, has reached a plateau, etc.
*/
WITH DAILY_TX AS (
SELECT
DATE_TRUNC('day',BLOCK_TIMESTAMP) AS DATE,
COUNT(DISTINCT(TX_HASH)) AS TXs,
COUNT(DISTINCT(FROM_ADDRESS)) AS UNIQUE_ADDRESS
FROM
polygon.core.fact_transactions
WHERE DATE >= '2022-07-01'
AND TX_HASH IS NOT NULL
--AND FROM_ADDRES NOT IN ((SELECT DISTINCT TO_ADDRESS FROM polygon.core.fact_transactions WHERE DATE_TRUNC('day',BLOCK_TIMESTAMP) => CURRENT_DATE - INTERVAL '15days'))
GROUP BY 1
ORDER BY 1 ASC)
SELECT
DATE,
TXs,
UNIQUE_ADDRESS,
AVG(TXs) OVER(ORDER BY DATE ASC ROWS BETWEEN 1 PRECEDING AND CURRENT ROW) AS MA_2_TX ,-- 2 DAYS MOVING AVERAGE FOR DAILY TX
AVG(UNIQUE_ADDRESS) OVER(ORDER BY DATE ASC ROWS BETWEEN 1 PRECEDING AND CURRENT ROW) AS MA_2_ADDRESS -- 2 DAYS MOVING AVERAGE FOR UNIQUE ADDRESS
FROM
DAILY_TX
ORDER BY 1
Run a query to Download Data