WITH weekly_data AS (

SELECT

DATE_TRUNC('week', block_timestamp) AS "week",

COUNT(DISTINCT tx_hash) AS "weekly swaps",

COUNT(DISTINCT sender) AS "weekly users",

SUM(amount_in_usd) AS "weekly swap volume"

FROM

avalanche.defi.ez_dex_swaps

WHERE

block_timestamp >= '2024-01-01'

AND block_timestamp <= '2024-12-31'

AND platform = 'uniswap-v3'

GROUP BY

DATE_TRUNC('week', block_timestamp)

),

weekly_change AS (

SELECT

"week",

"weekly swap volume",

"weekly swaps",

"weekly users",

LAG("weekly swap volume") OVER (ORDER BY "week") AS "previous_week_swap_volume",

LAG("weekly swaps") OVER (ORDER BY "week") AS "previous_week_swaps",

LAG("weekly users") OVER (ORDER BY "week") AS "previous_week_users",

CASE

WHEN LAG("weekly swap volume") OVER (ORDER BY "week") IS NULL THEN '0%'

WHEN "weekly swap volume" > LAG("weekly swap volume") OVER (ORDER BY "week") THEN

CONCAT(ROUND((( "weekly swap volume" - LAG("weekly swap volume") OVER (ORDER BY "week") ) / LAG("weekly swap volume") OVER (ORDER BY "week") ) * 100, 2), '% 🔼')

ELSE

CONCAT(ROUND((( "weekly swap volume" - LAG("weekly swap volume") OVER (ORDER BY "week") ) / LAG("weekly swap volume") OVER (ORDER BY "week") ) * 100, 2), '% 🔻')

END AS "volume_change_percentage",

CASE