-- 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

),

project_transaction_counts AS (

SELECT

COALESCE(dl.PROJECT_NAME, 'other-dapps') AS project_name,

COUNT(t.tx_hash) AS transaction_count

FROM

bsc.core.fact_transactions AS t

LEFT JOIN bsc.core.dim_labels AS dl ON t.to_address = dl.ADDRESS

WHERE

t.block_timestamp BETWEEN '{{from_date}}'

AND '{{to_date}}'

GROUP BY

project_name

ptc.project_name,

ptc.transaction_count

project_transaction_counts AS ptc

WHERE project_name != 'other-dapps'

ORDER BY

ptc.transaction_count DESC