adriaparcerisasethereum recent 9
Updated 2023-01-20
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
›
⌄
with base as (select origin_from_address,
min(block_timestamp) as first_bridging_tx
from ethereum.core.fact_event_logs
where origin_to_address = '0xa0c68c638235ee32657e8f720a23cec1bfc77c77'
and tx_status = 'SUCCESS' and block_timestamp between '2023-01-01 06:03:23.000' and '2023-01-16 14:38:47.000'
group by 1),
base2 as (select a.origin_from_address,
first_bridging_tx,
min(block_timestamp) as first_tx_after_bridging
from polygon.core.fact_event_logs a
join base b
on a.origin_from_address = b.origin_from_address
where tx_status = 'SUCCESS' and block_timestamp between '2023-01-01 06:03:23.000' and '2023-01-16 14:38:47.000'
group by 1,2
having first_bridging_tx < first_tx_after_bridging)
select
--address_name,
--label_type,
--label_subtype,
project_name,
count(distinct(tx_hash)) as tx_count,
count(distinct(a.origin_from_address)) as wallet_count
from polygon.core.fact_event_logs a
join base2 b
on a.origin_from_address = b.origin_from_address and a.block_timestamp = b.first_tx_after_bridging
join polygon.core.dim_labels c
on a.origin_to_address = c.address
where label_subtype != 'bridge' and block_timestamp between '2023-01-01 06:03:23.000' and '2023-01-16 14:38:47.000'
group by 1 order by 2 desc
Run a query to Download Data