select distinct token_contract,

TRUNC(TIMESTAMP,'hour') as timestamp_h,

avg(price_usd) as price_usd

from near.core.fact_prices

where timestamp >= (current_date - {{metric_days}})

group by 1,2

),

lp_stake AS (

Select Distinct

tx_hash,

DEPOSIT / pow(10,24) as near_amount

from NEAR.CORE.FACT_ACTIONS_EVENTS_FUNCTION_CALL

WHERE block_timestamp >= (current_date - {{metric_days}})

AND method_name in ('deposit_and_stake', 'internal_deposit_and_stake', 'internal_manager_deposit_and_stake',

'manager_deposit_and_stake', 'manual_stake', 'stake', 'stake_all')

),

total_stakes as (

SELECT p.tx_signer as trader,

p.TX_RECEIVER as protocol,

count(distinct p.tx_hash) as num_stake_transactions,

sum(lp_stake.near_amount) as near_stake_amt

FROM near.core.fact_transactions p

inner JOIN lp_stake ON lp_stake.tx_hash = p.tx_hash

WHERE p.block_timestamp >= (current_date - {{metric_days}})

and p.tx_status != 'Fail'

group by 1,2

),

lp_unstake as (

select tx_hash,

coalesce(PARSE_JSON(args):amount, PARSE_JSON(args):min_expected_near, PARSE_JSON(args):min_amount_out, NULL) as unstake,

case when contains(unstake, 'e') then 0

when contains(unstake, ',') then 0

when contains(unstake, 'o') then 0

when contains(unstake, 's') then 0

when contains(unstake, ' ') then 0