quenineblast bridging 2 copy
Updated 2024-05-10
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
›
⌄
-- forked from pentalimited / blast bridging 2 @ https://flipsidecrypto.xyz/pentalimited/q/6RT5rpaA9utS/blast-bridging-2
WITH weekly_bridge_stats AS (
SELECT
date_trunc('week', x.BLOCK_TIMESTAMP) AS week,
event_name,
COUNT(DISTINCT x.TX_HASH) AS num_bridge_transactions,
COUNT(DISTINCT decoded_log:user) AS num_bridge_users,
SUM(decoded_log:amount/pow(10,18)) AS total_amount_bridged,
sum(case when event_name='BridgedDeposit' then decoded_log:amount/pow(10,18) else (decoded_log:amount/pow(10,18))*(-1) end) as net_amount_bridged
FROM
blast.core.ez_decoded_event_logs x
WHERE
--x.BLOCK_TIMESTAMP >= '2024-02-29 10:00'
x.BLOCK_TIMESTAMP < current_date
and event_name in('BridgedDeposit','BridgedWithdrawal')
GROUP BY 1,2
),
prices as (
SELECT
trunc(hour,'week') as weeks,
avg(price) as price_usd
from blast.price.ez_prices_hourly
where symbol='ETH'
group by 1
)
SELECT
week,
event_name,
num_bridge_transactions,
sum(num_bridge_transactions) over (partition by event_name order by week) as total_bridges,
num_bridge_users,
case when event_name='BridgedDeposit' then total_amount_bridged*price_usd else total_amount_bridged*price_usd*(-1) end as total_amount_bridged_usd,
sum(total_amount_bridged_usd) over (partition by event_name order by week) as total_volume_usd
FROM
QueryRunArchived: QueryRun has been archived