BlockTrackerdata for anova test avalanche or other chains
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
›
⌄
⌄
-- forked from add gas_used to existing query @ https://flipsidecrypto.xyz/edit/queries/5942d116-fdcf-452e-907f-b307d6cda3c2
--token_in -- 0xb97ef9ef8734c71904d8002f8b6bc66dd9c48a6e USDC
--token_out-- 0x152b9d0fdc40c096757f570a51e494bd4b943e50 BTC.b
SELECT
platform,
avg(gas_used) as "Avg.Gas_Used",
avg(tx_fee) as "Avg.tx_fee",
avg(amount_in/amount_out) as "Avg.Exch Rate",
median(amount_in/amount_out) as "Median.Exch Rate",
count(DISTINCT a.tx_hash) as "number_of_swaps",
count (DISTINCT a.origin_from_address) as "Number of Swappers"
FROM {{chain}}.core.ez_dex_swaps a
LEFT JOIN {{chain}}.core.fact_transactions b using(tx_hash)
WHERE token_in LIKE lower('{{token_in}}') AND token_out LIKE lower('{{token_out}}')
AND a.block_timestamp > dateadd('month', -1*{{last_n_months}}, current_date)
AND a.block_timestamp < current_date
AND amount_out >0
AND platform != 'curve' --excluded as it has a problem in WETH/USDC convertion rate
GROUP BY platform
HAVING "number_of_swaps" > 10
ORDER BY "Avg.Gas_Used"
/*
SELECT *
FROM avalanche.core.fact_hourly_token_prices
WHERE symbol ilike '%USDC%'
LIMIT 100
*/
Run a query to Download Data