messariloose-blue
Updated 2024-08-27
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 ranked_prices AS (
SELECT
DATE_TRUNC('day', hour) AS block_date,
price,
ROW_NUMBER() OVER (PARTITION BY DATE_TRUNC('day', hour) ORDER BY hour DESC) AS rank
FROM kaia.price.ez_prices_hourly
WHERE is_native = true
),
kaia_daily_prices AS (
SELECT block_date, price
FROM ranked_prices
WHERE rank = 1
),
kaia_daily_fees AS (
SELECT
DATE_TRUNC('day', t.BLOCK_TIMESTAMP) AS block_date,
SUM(t.tx_fee) AS daily_fees_native
FROM kaia.core.fact_transactions t
GROUP BY DATE_TRUNC('day', t.BLOCK_TIMESTAMP)
),
kaia_fee_metrics AS (
SELECT
f.block_date,
f.daily_fees_native * p.price AS total_fees_usd
FROM kaia_daily_fees f
JOIN kaia_daily_prices p ON f.block_date = p.block_date
),
fee_revenue_q2 AS (
SELECT
SUM(f.daily_fees_native * p.price) AS total_fees_revenue_q2
FROM kaia_daily_fees f
JOIN kaia_daily_prices p ON f.block_date = p.block_date
WHERE f.block_date >= '2024-04-01' AND f.block_date <= '2024-06-30'
)
SELECT
total_fees_revenue_q2
QueryRunArchived: QueryRun has been archived