charliemarketplaceflashbots-protect-weekly
Updated 2024-09-19
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
›
⌄
-- users can submit multiple protect tx at once
-- with slightly different parameters, get the max tx id
-- to filter accordingly
-- get the max tx_id
with timed_protect_tx AS (
select block_timestamp,
created_at_block_number, tx_hash, from_address,
hints_selected, num_of_builders_shared, refund_percent
from external.flashbots.fact_protect_transactions inner join ethereum.core.fact_transactions USING (tx_hash)
where block_timestamp > '2022-02-01' -- after flashbots launched
),
dex_protected AS (
select p.block_timestamp, p.tx_hash,
COALESCE(amount_in_usd, COALESCE(amount_out_usd, 0)) as protected_usd, 'DEX' as type
from timed_protect_tx p inner join ethereum.defi.ez_dex_swaps USING (block_timestamp, tx_hash)
),
lend_protected AS (
select p.block_timestamp, p.tx_hash,
COALESCE(amount_usd, 0) as protected_usd, 'LEND' as type
from timed_protect_tx p inner join ethereum.defi.ez_lending_borrows USING (block_timestamp, tx_hash)
),
bridge_protected AS (
select p.block_timestamp, p.tx_hash,
COALESCE(amount_usd, 0) as protected_usd, 'BRIDGE' as type
from timed_protect_tx p inner join ethereum.defi.ez_bridge_activity USING (block_timestamp, tx_hash)
),
nft_sale_protected AS (
select p.block_timestamp, p.tx_hash,
COALESCE(price_usd, 0) as protected_usd, 'NFT Sale' as type
from timed_protect_tx p inner join ethereum.nft.ez_nft_sales USING (block_timestamp, tx_hash)
QueryRunArchived: QueryRun has been archived