SELECT

date_trunc('day', a.block_timestamp) AS Date,

b.PROJECT_NAME,

count(DISTINCT a.tx_hash) AS txns,

count(DISTINCT a.ORIGIN_FROM_ADDRESS) AS Users,

SUM(c.value) AS txn_volume

FROM

kaia.core.fact_event_logs a

JOIN kaia.core.dim_labels b ON a.contract_address = b.address

JOIN kaia.core.fact_transactions c ON a.tx_hash = c.tx_hash

WHERE

a.TX_SUCCEEDED = 'TRUE'

AND b.label_type IS NOT NULL

AND a.block_timestamp >= '2024-01-01'

GROUP BY 1, 2

),

usd_stats AS (

SELECT

date_trunc('day', hour) AS Date,

AVG(price) AS price

FROM

kaia.price.ez_prices_hourly

WHERE

symbol = 'KLAY'

GROUP BY 1

),

totals AS (

SELECT

k.Date,

k.PROJECT_NAME,

k.Users,

k.txns,

k.txn_volume * u.price AS usd_txn_volume