SELECT date(RECORDED_HOUR) as pdate, avg(CLOSE) as price

from crosschain.core.fact_hourly_prices

where id ilike 'terran-coin'

GROUP BY pdate

DATE_TRUNC('week', BLOCK_TIMESTAMP) AS date,

SUM(FEE) as total_tx_fee,

SUM(FEE * price) as tx_fee_usd,

AVG(FEE) as avg_tx_fee,

AVG(FEE * price) as avg_tx_fee_usd,

sum(total_tx_fee) over (order by date) as cumu_fee,

percentile_cont(0.5) within group (order by FEE) as med_tx_fee,

percentile_cont(0.5) within group (order by FEE * price) as med_tx_fee_usd,

COUNT(DISTINCT tx_id) as tx_count,

tx_count/604800 as tps

FROM terra.core.fact_transactions

left JOIN luna_price on pdate = date(BLOCK_TIMESTAMP)

where fee_denom = 'uluna'

GROUP BY date

ORDER BY date DESC