select

BLOCK_TIMESTAMP,

case

when ifnull(tx:actions[0]:Delegate:delegate_action:receiver_id::string, TX_RECEIVER) ilike 'tg'

or ifnull(tx:actions[0]:Delegate:delegate_action:receiver_id::string, TX_RECEIVER) ilike '%.hot.tg'

or ifnull(tx:actions[0]:Delegate:delegate_action:receiver_id::string, TX_RECEIVER) ilike '%.herewallet.near'

then 'Here Wallet' else 'Other' end as type,

TX_HASH,

ifnull(tx:actions[0]:Delegate:delegate_action:sender_id::string, TX_SIGNER) as user,

TRANSACTION_FEE/1e24 as fee

from near.core.fact_transactions

where TX_SUCCEEDED=true

and BLOCK_TIMESTAMP::date>='2023-10-01'

,

txs as (

SELECT DISTINCT

block_timestamp,

tx_hash,

user as TX_SIGNER

FROM hereW

WHERE type = 'Here Wallet'

,

txsGap AS (

SELECT

TX_SIGNER as User,

block_timestamp as Txs_date,

LAG(block_timestamp) OVER (

PARTITION BY User

ORDER BY

block_timestamp

) AS prev_Txs_date

FROM txs

),