-- Credit to 0xHaM☰d for the swaps

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'

),

bigtable 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,

ROUND((avg_fees_per_txn_usd - avg_fees_per_txn_usd_prev)/avg_fees_per_txn_usd_prev * 100,2) AS pct_diff_avg_fees_per_txn_usd,

ROUND((avg_fees_per_user_usd - avg_fees_per_user_usd_prev)/avg_fees_per_user_usd_prev * 100,2) AS pct_diff_avg_fees_per_user_usd

*,

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,

LAG(avg_fees_per_txn_usd) OVER (ORDER BY date) avg_fees_per_txn_usd_prev,

LAG(avg_fees_per_user_usd) OVER (ORDER BY date) avg_fees_per_user_usd_prev

tr.*,

txn_fees as tx_fees,

txn_fees*np.price AS txn_fees_usd,