-- Average BNB prices by day

SELECT

date_trunc('day', HOUR) AS date_day,

AVG(price) AS bnb_price

FROM

crosschain.core.ez_hourly_prices

WHERE

symbol = 'BNB'

AND hour BETWEEN '{{from_date}}'

AND '{{to_date}}'

GROUP BY

1

),

monthly_kpis AS (

SELECT

date_trunc('month', t.block_timestamp) AS month,

COALESCE(dl.LABEL_TYPE, 'Unknown') AS category,

COUNT(t.tx_hash) AS transaction_count,

SUM(t.tx_fee) AS total_tx_fee_bnb,

SUM(((LENGTH(t.input_data) - 2) / 2) + 112) / 1024 / 1024 AS total_data_size_MB

FROM

bsc.core.fact_transactions AS t

LEFT JOIN

bsc.core.dim_labels AS dl

ON

t.to_address = dl.ADDRESS

JOIN

bnb_prices AS bp

ON

date_trunc('day', t.block_timestamp) = bp.date_day

WHERE

t.block_timestamp BETWEEN '{{from_date}}'

AND '{{to_date}}'

GROUP BY

1, category