feyikemiAvalanche Bridge Totals 1
Updated 2024-06-18
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_activity AS (
SELECT
date_trunc('day', BLOCK_TIMESTAMP) AS day,
COUNT(DISTINCT TX_HASH) AS total_bridges,
COUNT(DISTINCT ORIGIN_FROM_ADDRESS) AS total_bridgers,
SUM(CASE WHEN AMOUNT_USD < 1e9 THEN AMOUNT_USD ELSE 0 END) AS total_amount_bridged
FROM
avalanche.defi.ez_bridge_activity
WHERE
BLOCK_TIMESTAMP::DATE >= '2024-01-01'
GROUP BY 1
),
total_metrics AS (
SELECT
SUM(total_bridges) AS total_bridges,
SUM(total_bridgers) AS total_bridgers,
SUM(total_amount_bridged) AS total_volume_bridged
FROM
bridge_activity
),
weekly_bridge_metrics AS (
SELECT
SUM(total_bridges) AS weekly_bridges,
SUM(total_bridgers) AS weekly_bridgers,
SUM(total_amount_bridged) AS weekly_volume_bridged
FROM
bridge_activity
WHERE
day >= current_date - INTERVAL '7 days'
)
SELECT
tm.total_bridges AS "Total Avalanche Bridges",
wm.weekly_bridges AS "Total Avalanche Bridges Past 7 Days",
QueryRunArchived: QueryRun has been archived