theericstoneUntitled Query
Updated 2022-01-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 staking_rewards as (
SELECT
date_trunc('day', block_timestamp) as date,
value:denom as currency,
value:amount / POW(10,6) as amount
from terra.transitions,
lateral flatten( input => event_attributes:amount)
WHERE transition_type='end_block'
and (event = 'rewards' or event = 'commission')
and block_timestamp > current_date - 90
),
prices as (
SELECT
date_trunc('day', block_timestamp) as date,
avg(price_usd) as price,
lower(currency) as currency
from terra.oracle_prices
where block_timestamp > current_date - 90
group by date, currency
),
staking_rewards_value as (
select SR.date as date,
SR.currency,
P.price,
SR.amount,
P.price * SR.amount as reward_value
from staking_rewards SR
left join prices P on SR.date = P.date and SR.currency = P.currency)
--commissions as (
--select
-- date,
-- sum(VALUE) as commission_value
--from commissions_unagg
Run a query to Download Data