select date_trunc('hour', BLOCK_TIMESTAMP) as date,

count(DISTINCT tx_id) as transaction_count,

count(DISTINCT tx_sender) as users,

avg(fee) as avg_fee,

sum(fee) as total_fee

from terra.core.fact_transactions

where date::date >= CURRENT_DATE - 30

group by 1

order by 1

select

case

when extract(dow from date) = 0 then '7.Sunday'

when extract(dow from date) = 1 then '1.Monday'

when extract(dow from date) = 2 then '2.Tuesday'

when extract(dow from date) = 3 then '3.Wednesday'

when extract(dow from date) = 4 then '4.Thursday'

when extract(dow from date) = 5 then '5.Friday'

when extract(dow from date) = 6 then '6.Saturday'

end as days,

date_part(hour, date) as hours,

avg(transaction_count) as avg_count,

avg(users) as avg_volume,

avg(avg_fee) as avg_fee,

avg(total_fee) as total_fee

from transaction_data

group by 1, 2

order by 1, 2 desc