with near_price as (

select

date_trunc('day', timestamp) as date,

avg(price_usd) as price

from near.core.fact_prices

where symbol = 'wNEAR'

and timestamp::date >= '{{start_date}}' AND timestamp::date <= '{{end_date}}'

group by date

),

txns as (

select

t.transaction_fee/1e24 as tx_fee,

t.transaction_fee/1e24 * p.price as tx_fee_usd,

iff(r.status_value:SuccessValue is not null,'Succeeded','Failed') as status,

-- CREDIT https://app.flipsidecrypto.com/velocity/queries/ec06c23f-3520-43ac-8a0a-e09143f7e6ce

substr(split(tx:"actions"[0], ':')[0], 3, len(split(tx:"actions"[0], ':')[0])-3) as tx_method,

t.*

-- ((t.transaction_fee/1e9) * (t.gas_used)) * (price) as gas_used_usd

from near.core.fact_transactions t

join near_price p on t.block_timestamp::date = p.date and t.block_timestamp::date >= '{{start_date}}' AND t.block_timestamp::date <= '{{end_date}}'

join near.core.fact_receipts r on t.tx_hash = r.tx_hash and t.block_timestamp::date >= '{{start_date}}' AND t.block_timestamp::date <= '{{end_date}}'

select

block_timestamp::date as date,

status as type,