date_trunc('day', block_timestamp) as date,

count(distinct tx_hash) as "Transfer Count",

sum(deposit/pow(10,24)) as amount,

max(deposit/pow(10,24)) as max_amount,-- max transfer amount

round(max_amount/amount*100,2) as max_percent,

avg(deposit/pow(10,24)) as avg_amount

FROM near.core.fact_transfers

WHERE status = TRUE

GROUP BY 1

),

BTC_PRICE AS (

SELECT

date(hour) as date_btc,

avg(price) as "BTC (USD)"

FROM ethereum.core.fact_hourly_token_prices

WHERE hour > '2021-10-01'

AND symbol IN ('WBTC')

GROUP BY 1

),

NEAR_PRICE AS (

SELECT

date(timestamp) as date_price,

avg(price_usd) as "NEAR (USD)"

FROM near.core.fact_prices

WHERE symbol = 'wNEAR'

GROUP BY 1

ORDER BY 1

SELECT

*,

amount as "NEAR Volume",

amount*"NEAR (USD)" as "NEAR USD Volume"

FROM NEAR_TRANSFERS

LEFT JOIN NEAR_PRICE ON date = date_price