Abbas_ra21ETH #1
Updated 2023-01-20Copy Reference Fork
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
29
30
31
32
33
34
35
36
›
⌄
with tb AS (select Date_trunc('Day',BLOCK_TIMESTAMP)::Date AS Day,
avg(LATEST_ANSWER_UNADJ/1e8) AS Price
from ethereum.chainlink.ez_oracle_feeds where FEED_ADDRESS='0x5f4ec3df9cbd43714fe2740f5e3616155c5b8419' and BLOCK_NUMBER between 16310000 and 16420000 group by 1),
tb2 AS (
select date_Trunc('day',Timedate)::Date AS Day,
Count(Address) AS "New Addresses Count" from (
select Min(BLOCK_TIMESTAMP) AS Timedate,
From_Address AS Address
from ethereum.core.fact_transactions where BLOCK_NUMBER between 16310000 and 16420000 and STATUS='SUCCESS' group by 2
) group by 1 ),
tbb AS (
select
DISTINCT From_Address AS Address
from ethereum.core.fact_transactions where BLOCK_TIMESTAMP between dateadd('month',-6,'2023-01-01 06:03:23.000') and '2023-01-01 06:03:23.000'),
tbb2 AS (
select
From_Address,
min(BLOCK_number) AS Block
from ethereum.core.fact_transactions
group by 1 having (Block between 16310000 and 16420000)
),
tbb3 AS (
select
date_trunc('day',BLOCK_TIMESTAMP)::Date AS Day,
count(DISTINCT From_Address ) AS "Returned Addresses"
from ethereum.core.fact_transactions where BLOCK_NUMBER between 16310000 and 16420000
and From_Address not in (select Address from tbb)
and From_Address not in (select From_Address from tbb2)
group by 1),
tb3 AS (
select
Date_trunc('Day',BLOCK_TIMESTAMP)::Date AS Day,
case when STATUS='SUCCESS' then 'SUCCESS' else 'Failed' end AS STATUS,
Count(TX_HASH) AS "Transcations Count",
Count(DISTINCT FROM_ADDRESS) AS "Active Addresses Count",
Avg(Gas_price) AS "Average gas price",
Run a query to Download Data