SELECT

HOUR::date as date,

avg(PRICE) as price,

FROM kaia.price.ez_prices_hourly

WHERE SYMBOL = 'KLAY'

GROUP by 1

SELECT

COUNT(DISTINCT tx_hash) AS n_swaps,

COUNT(DISTINCT from_address) AS n_swappers,

SUM(VALUE_PRECISE) AS swap_vol_klay,

sum(TX_FEE_PRECISE) as fee_amt_klay,

SUM(VALUE_PRECISE*price) AS swap_vol_usd,

sum(TX_FEE_PRECISE*price) as fee_amt_usd,

n_swaps / n_swappers AS avg_txs_per_user,

swap_vol_klay / n_swappers AS avg_vol_per_user,

swap_vol_klay / n_swaps AS avg_vol_per_txs,

avg(TX_FEE_PRECISE) as avg_tx_fee,

fee_amt_klay / n_swappers AS avg_fee_per_user,

fee_amt_klay / n_swaps AS avg_fee_per_swap

FROM kaia.core.fact_transactions tx

JOIN (

SELECT

distinct tx_hash,

block_timestamp

FROM kaia.core.fact_event_logs

join kaia.core.dim_labels on contract_address = address

where label_type = 'dex'

AND tx_succeeded = 'TRUE'

) using(block_timestamp, tx_hash)

LEFT JOIN priceTb on trunc(block_timestamp, 'd') = date