misaghlbFlow vs Other L1s Pt (II) - ethereum average time between swaps and nfts
Updated 2022-07-15
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 swaps as (
SELECT block_timestamp as time_1, ORIGIN_FROM_ADDRESS as wallet,
lead(block_timestamp,1) over (partition by wallet order by block_timestamp ASC) as time_2
FROM ethereum.core.ez_dex_swaps
WHERE date(block_timestamp) >= '2022-06-01'
),
nfts as (
SELECT block_timestamp as time_1, BUYER_ADDRESS as wallet,
lead(block_timestamp,1) over (partition by wallet order by block_timestamp ASC) as time_2
FROM ethereum.core.ez_nft_sales
WHERE date(block_timestamp) >= '2022-06-01'
),
swaps_bot_removal as (
SELECT date_trunc('hour', time_1) as hourly, wallet, COUNT(*) as tx_count from swaps
GROUP by hourly, wallet having tx_count > 300
),
nfts_bot_removal as (
SELECT date_trunc('hour', time_1) as hourly, wallet, COUNT(*) as tx_count from nfts
GROUP by hourly, wallet having tx_count > 300
),
swaps_retention as (
SELECT
avg(DATEDIFF(hour, time_1, time_2)) as difference, 'Swaps' as type
FROM swaps
where wallet not in (SELECT wallet from swaps_bot_removal)
),
nfts_retention as (
SELECT
avg(DATEDIFF(hour, time_1, time_2)) as difference, 'NFTs' as type
FROM nfts
where wallet not in (SELECT wallet from nfts_bot_removal)
)
SELECT * from swaps_retention
UNION
SELECT * from nfts_retention
Run a query to Download Data