transfer_logs as (

select

block_timestamp,

tx_hash,

receiver_id,

logs,

split(logs[0],' ') as t_log,

l.token as token_name,

l.symbol as token_symbol,

case when l.decimals is null then 18 else l.decimals end as token_decimals,

t_log[1]::integer/pow(10,token_decimals) as amt,

t_log[3]::string as from_id,

t_log[5]::string as to_id

from near.core.fact_receipts r

left outer join near.core.dim_token_labels l on r.receiver_id = l.token_contract

where array_size(logs) > 0

and split(logs[0],' ')[0] = 'Transfer'

and try_to_decimal(to_varchar(split(logs[0],' ')[1])) is not null

-- ^ Check if the 2nd word is number. Requires converting the 2nd word to string, then try decimal casting and check if decimal casting worked

),

prices as (

select

date(timestamp) as date,

token_contract,

avg(price_usd) as price

from near.core.fact_prices

group by 1,2

),

daily_token_transfers as (

select

date(block_timestamp) as date,

receiver_id as token_contract,

token_symbol,

count(tx_hash) as txs,

sum(amt) as token_vol