0xHaM-dTotal Generated Fee in Terra Ecosystem
Updated 2024-01-16
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 Total Generated Fee in Terra Ecosystem @ https://flipsidecrypto.xyz/edit/queries/6324f229-7d41-467e-be4f-207ade88c16c
with
priceTb as (
SELECT
RECORDED_HOUR::date as p_date,
avg(price) as luna_PRICE
FROM osmosis.price.ez_prices
WHERE SYMBOL = 'LUNA'
AND RECORDED_HOUR::date < current_date() - 1
GROUP by 1
ORDER by 1
-- /*
)
-- , pre_filter as (
SELECT
BLOCK_TIMESTAMP::date as date,
case
when date < '2023-11-14' then 'Before Announcement'
else 'After Announcement' end as timespan,
sum(split(ATTRIBUTE_VALUE, 'uluna')[0]::float/1e6) as fee_amount_luna,
sum(luna_PRICE* (split(ATTRIBUTE_VALUE, 'uluna')[0]::float/1e6)) as fee_amount_usd,
avg(fee_amount_luna) over (partition by timespan order by date) as avg_fee_amount_luna
FROM terra.core.fact_msg_attributes_standard
JOIN priceTb on p_date = BLOCK_TIMESTAMP::date
WHERE msg_type = 'tx'
and attribute_key = 'fee'
and ATTRIBUTE_VALUE ilike '%uluna'
AND date >= '2023-10-24'
and date < current_date()
GROUP by 1,2
-- )
-- select
-- date_trunc('d', date) as dt,
-- case
QueryRunArchived: QueryRun has been archived