-- forked from c3641c4b-d0d2-4af1-b3a1-f241d1e1330a
WITH swaps AS (
-- 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'
and block_timestamp::date>=CURRENT_DATE-INTERVAL '1 MONTH'
)
SELECT
*,
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
FROM
(
SELECT
*,
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
FROM
(
SELECT
tr.*,
txn_fees*np.price AS txn_fees_usd
FROM
(
SELECT