jackguyMarinade native fees 2 copy
Updated 2023-10-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
›
⌄
-- forked from Marinade native fees 2 @ https://flipsidecrypto.xyz/edit/queries/d1d3f54c-26fa-47d9-b809-d77daf5526e2
with tab1 as (
SELECT tx_id
FROM solana.core.fact_events
WHERE signers[0] LIKE 'noMa7dN4cHQLV4ZonXrC29HTKFpxrpFbDLK5Gub8W8t'
and block_timestamp > current_date - 90
)
SELECT
*,
sum(transaction_fees_on_eth) over (ORDER BY day) as total_transaction_fees_usd
FROM (
SELECT
date_trunc('day', block_timestamp) as day,
-- sum((sol_price * fee) / power(10, 9)) as fees_sol,
-- (sum((sol_price * fee) / power(10, 9))) * avg(sol_price) as fees_usd,
count(DISTINCT tx_id) as transactions,
count(DISTINCT tx_id) * avg(eth_tx_fees) as transaction_fees_on_eth
FROM solana.core.fact_transactions
LEFT outer JOIN (
SELECT
date_trunc('day', block_timestamp) as day2,
avg(tx_fee) * avg(price1) as eth_tx_fees
FROM arbitrum.core.fact_transactions
LEFT outer JOIN (
SELECT
date_trunc('day', hour) as day3,
avg(price) as price1
FROM ethereum.core.fact_hourly_token_prices
WHERE symbol LIKE 'WETH'
GROUP BY 1
) on day3 = day2
GROUP BY 1
) on day2 = day
Run a query to Download Data