date_trunc('day', block_timestamp) as date,

count(distinct tx_signer) as "Users",

count(distinct tx_hash) as "Transfer Count",

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

median(deposit/pow(10,24)) as median_amount,-- max transfer 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 tx_hash IN (SELECT distinct tx_hash from near.core.fact_receipts WHERE regexp_substr(status_value, 'Success') IS NOT NULL )

-- WHERE status = TRUE

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 = 'NEAR'

GROUP BY 1

ORDER BY 1

SELECT

*,

amount as "NEAR",

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

FROM NEAR_TRANSFERS

LEFT JOIN NEAR_PRICE ON date = date_price

WHERE "NEAR (USD)" IS not null

-- AND CURRENT_DATE - date(date) < 60

ORDER BY 1