kentoBOTH
Updated 2025-02-23
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
›
⌄
WITH kaia_feb_2025 AS (
SELECT
DATE(BLOCK_TIMESTAMP) AS day, -- Extract the day
SUM(VALUE) AS total_transaction_volume,
SUM(TX_FEE) AS total_transaction_fees,
AVG(GAS_PRICE) AS avg_gas_price,
SUM(GAS_USED) AS total_gas_used,
COUNT(*) AS total_transactions,
AVG(VALUE) AS avg_transaction_value,
AVG(TX_FEE) AS avg_transaction_fee,
SUM(CASE WHEN TX_SUCCEEDED THEN 1 ELSE 0 END) * 1.0 / COUNT(*) AS success_rate
FROM kaia.core.fact_transactions
WHERE BLOCK_TIMESTAMP >= '2025-02-01' AND BLOCK_TIMESTAMP < '2025-03-01'
GROUP BY DATE(BLOCK_TIMESTAMP) -- Grouping by day
),
avalanche_feb_2025 AS (
SELECT
DATE(BLOCK_TIMESTAMP) AS day, -- Extract the day
SUM(VALUE) AS total_transaction_volume,
SUM(TX_FEE) AS total_transaction_fees,
AVG(GAS_PRICE) AS avg_gas_price,
SUM(GAS_USED) AS total_gas_used,
COUNT(*) AS total_transactions,
AVG(VALUE) AS avg_transaction_value,
AVG(TX_FEE) AS avg_transaction_fee,
SUM(CASE WHEN TX_SUCCEEDED THEN 1 ELSE 0 END) * 1.0 / COUNT(*) AS success_rate
FROM avalanche.core.fact_transactions
WHERE BLOCK_TIMESTAMP >= '2025-02-01' AND BLOCK_TIMESTAMP < '2025-03-01'
GROUP BY DATE(BLOCK_TIMESTAMP) -- Grouping by day
)
SELECT
COALESCE(k.day, a.day) AS day, -- Ensure we have a day value even if one side is missing
k.total_transaction_volume AS kaia_total_transaction_volume,
a.total_transaction_volume AS avalanche_total_transaction_volume,
k.total_transaction_fees AS kaia_total_transaction_fees,
a.total_transaction_fees AS avalanche_total_transaction_fees,
QueryRunArchived: QueryRun has been archived