with succeeded_txs as (

select

tx_hash as tx

from near.core.fact_transactions

where TX_STATUS='Success'

AND block_timestamp>='2023-08-17'

,

kaic_events as (

select

BLOCK_TIMESTAMP

, TRY_PARSE_JSON(REPLACE(l.value::STRING, 'EVENT_JSON:', '')) AS event_json

, event_json:event as event

, CASE WHEN l.value::STRING ILIKE 'Claiming%' THEN 'claim' ELSE NULL END as cat

, tx_hash

from near.core.fact_receipts a, LATERAL FLATTEN(INPUT => a.logs) l

where 1=1

AND ( --DEFAULT, all token transfers are transfers duh

(event_json:standard='nep141' AND RECEIVER_ID='wallet.kaiching' AND event_json:event='ft_transfer')

OR

--CLAIM CASE, some transfers are specifically triggered by claim func

(cat IS NOT NULL AND receiver_ID ilike '%kaiching')

)

AND block_timestamp>='2023-08-17'

,

kaic_transfers as (

select

MAX(BLOCK_TIMESTAMP) as BLOCK_TIMESTAMP

, MAX(event_json:data[0]:old_owner_id)::STRING as sender

, MAX(event_json:data[0]:new_owner_id)::STRING as receiver