swaps AS (

SELECT

block_timestamp,

logs[0] AS log,

substring(log, 1, CHARINDEX(' wrap.near for', log)) AS first_part,

regexp_replace(first_part, '[^0-9]', '')/pow(10, 24) AS near_amount,

substring(log, CHARINDEX('for', log), 100) AS second_part,

substring(second_part, 1, CHARINDEX('dac', second_part)-2) AS second_part_amount,

regexp_replace(second_part_amount, '[^0-9]', '')/pow(10,6) AS usdt_amount

FROM near.core.fact_receipts

WHERE logs[0] like 'Swapped % wrap.near for % dac17f958d2ee523a2206206994597c13d831ec7.factory.bridge.near'

and block_timestamp::date>=CURRENT_DATE-INTERVAL '1 WEEK'

),

hourly as (

*,

ROUND((active_users - active_users_prev)/active_users_prev * 100,2) AS pct_diff_active,

ROUND((number_transactions - number_transactions_prev)/number_transactions_prev * 100,2) AS pct_diff_transactions,

ROUND((txn_fees_usd - txn_fees_prev)/txn_fees_prev * 100,2) AS pct_diff_txn_fees

*,

LAG(active_users,1) OVER (ORDER BY date) active_users_prev,

LAG(number_transactions,1) OVER (ORDER BY date) number_transactions_prev,

LAG(txn_fees_usd) OVER (ORDER BY date) txn_fees_prev

tr.*,

txn_fees*np.price AS txn_fees_usd

FROM

(

SELECT

DATE_TRUNC('hour',block_timestamp::date) AS date,