ValiMohammadiSum All transaction of Last month
Updated 2022-07-13Copy Reference Fork
99
1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
›
⌄
SELECT SUM(Table1.CountPerSecond) AS SumLastMonthPerSecond ,SUM(Table2.CountRestOfYearPerSecond) AS sum_CountRestOfYearPerSecond FROM
(SELECT COUNT(*) AS CountPerDay,COUNT(*)/86400 as CountPerSecond,CAST(SUBSTRING(BLOCK_TIMESTAMP::DATE,9,2) as int )as Days from
solana.core.fact_transactions
WHERE BLOCK_TIMESTAMP >= current_date-30 and BLOCK_TIMESTAMP < current_date
GROUP BY Days
ORDER BY Days ) as Table1
INNER JOIN (
SELECT COUNT(*)/(6*86400) as CountRestOfYearPerSecond, count(*) as CountRestOfYearPerDay,CAST( substring(BLOCK_TIMESTAMP::DATE,9,2) as int ) as Days
FROM
solana.core.fact_transactions
WHERE BLOCK_TIMESTAMP > '2022-01-01' AND BLOCK_TIMESTAMP <=current_date-30
GROUP BY Days
ORDER BY Days DESC
) AS Table2
ON Table1.Days=Table2.Days
Run a query to Download Data