0xHaM-dThorchain
Updated 2023-05-04
999
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
›
⌄
-- SQL Credit https://flipsidecrypto.xyz/0xHaM-d/squid-competitive-analysis-2qD7di
with priceTb as (
SELECT
date_trunc('day', hour) as p_date,
symbol,
avg(price) as price
FROM ethereum.core.fact_hourly_token_prices
WHERE symbol in ('USDT', 'USDC', 'MATIC', 'WETH', 'SNX', 'HOP', 'DAI', 'WBNB', 'WAVAX')
GROUP by 1,2
)
, squidTb as (
SELECT
date_trunc('day', s.block_timestamp) as date,
count(DISTINCT s.tx_hash) as "Tx Count",
count(DISTINCT sender) as "User Count",
sum(TX_FEE * price) as "Fees Volume",
avg(TX_FEE * price) as "Avg Fees Volume",
"Fees Volume" / "Tx Count" as "Avg Fee per Tx",
"Fees Volume" / "User Count" as "Avg Fee per User"
FROM axelar.core.ez_squid s
JOIN ethereum.core.fact_transactions t using(tx_hash, block_timestamp)
JOIN priceTb p on (t.block_timestamp::date = p.p_date)
WHERE symbol = 'WETH'
AND block_timestamp > '2023-02-15'
GROUP by 1
UNION
SELECT
date_trunc('day', s.block_timestamp) as date,
count(DISTINCT s.tx_hash) as "Tx Count",
count(DISTINCT sender) as "User Count",
sum(TX_FEE * price) as "Fees Volume",
avg(TX_FEE * price) as "Avg Fees Volume",
"Fees Volume" / "Tx Count" as "Avg Fee per Tx",
Run a query to Download Data