maytsu2023-09-09 Fees on Wormhole vs Others - Bridging Fees copy
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 lunasg / Terra - 128. Fees on Wormhole vs Others - Bridging Fees @ https://flipsidecrypto.xyz/lunasg/q/LlSzo3m664pM/terra-128-fees-on-wormhole-vs-others-bridging-fees
--IL MANQUE LES VOLUMES DE TRANSACTIONS ASSOCIES! MAYBE DANS terra.msgs ??
WITH BRIDGE_TX AS (
SELECT
DISTINCT tx_id as bridge_tx,
CASE WHEN msg_value:from_address = 'terra1rtn03a9l3qsc0a9verxwj00afs93mlm0yr7chk' THEN 'Harmony-In'
WHEN msg_value:to_address = 'terra1rtn03a9l3qsc0a9verxwj00afs93mlm0yr7chk' THEN 'Harmony'
WHEN msg_value:from_address = 'terra1g6llg3zed35nd3mh9zx6n64tfw3z67w2c48tn2' THEN 'BSC-In'
WHEN msg_value:to_address = 'terra1g6llg3zed35nd3mh9zx6n64tfw3z67w2c48tn2' THEN 'BSC'
WHEN msg_value:from_address = 'terra13yxhrk08qvdf5zdc9ss5mwsg5sf7zva9xrgwgc' THEN 'Ethereum-In'
WHEN msg_value:to_address = 'terra13yxhrk08qvdf5zdc9ss5mwsg5sf7zva9xrgwgc' THEN 'Ethereum' ELSE NULL END AS Bridge_Label
FROM terra.classic.fact_messages
WHERE msg_value:from_address IN ('terra13yxhrk08qvdf5zdc9ss5mwsg5sf7zva9xrgwgc', 'terra1g6llg3zed35nd3mh9zx6n64tfw3z67w2c48tn2', 'terra1rtn03a9l3qsc0a9verxwj00afs93mlm0yr7chk')
OR msg_value:to_address IN ('terra13yxhrk08qvdf5zdc9ss5mwsg5sf7zva9xrgwgc', 'terra1g6llg3zed35nd3mh9zx6n64tfw3z67w2c48tn2', 'terra1rtn03a9l3qsc0a9verxwj00afs93mlm0yr7chk')
),
BRIDGE_FEES AS (
SELECT
date_trunc('hour', block_timestamp) as hour,
tx_id,
Bridge_Label,
F.Value:denom::string as denom,
F.Value:amount as fees --/pow(10,6)
FROM terra.classic.fact_transactions
LEFT JOIN BRIDGE_TX ON tx_id = bridge_tx,
table(flatten(input => fee[0]:amount)) f
WHERE tx_id IN (SELECT bridge_tx FROM BRIDGE_TX)
AND block_timestamp > '2021-10-05' -- Date where WH went life
-- TX_ID = '6A038DF797E8DC9EEBBCF0B1F58D88926CED059E621805327B2DE9CFA7BAC641' -- Receive Fund from Bridge
-- TX_ID = 'E9C873A4F21AE397EED0C0D0BD55B07F66E5A87D3A9C8E80C9D8BCDCDCD4C33F' -- Send Fund from Bridge
)
SELECT
hour,
Bridge_Label,
Run a query to Download Data