campList of destinations of users after bridging
Updated 2022-08-01Copy 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 bridge_tx AS (
SELECT block_timestamp AS bridge_date, origin_from_address as bridger
FROM ethereum.core.fact_event_logs
WHERE origin_to_address = LOWER ('0xA0c68C638235ee32657e8f720a23ceC1bFc77C77')
AND origin_function_signature IN ('0x4faa8a26' , '0xe3dec8fb')
),
tx_time AS (
SELECT bridger, MIN(block_timestamp) AS blocktime
FROM polygon.core.fact_transactions a JOIN bridge_tx b ON a.from_address = b.bridger
WHERE block_timestamp >= bridge_date
GROUP BY 1
),
tx_data AS (
SELECT from_address, block_timestamp, tx_hash, to_address
FROM polygon.core.fact_transactions
),
first_tx AS(
SELECT bridger, blocktime, tx_hash, to_address
FROM tx_time a JOIN tx_data b ON a.blocktime = b.block_timestamp AND a.bridger=b.from_address
),
final_data AS (
select bridger,
blocktime,
tx_hash,
to_address,
project_name,
label_type
FROM first_tx a
JOIN polygon.core.dim_labels b ON a.to_address = b.address
WHERE NOT address_name LIKE '%canonical%'
),
top10 as(
SELECT project_name as "Project Name", label_type as "Type of Actions", COUNT(bridger) as "Number of bridgers" FROM final_data
GROUP BY 1,2
ORDER BY 3 DESC
Run a query to Download Data