WITH TVL_WEEKLY AS (

select

date_part(week, EB.balance_date) as "WEEK",

SUM(EB.AMOUNT_USD) AS "WEEKLY_TVL",

date_part(week, CURRENT_DATE) AS "CURRENT_WEEK",

"WEEKLY_TVL" - lag("WEEKLY_TVL", 1) over (order by "WEEK" ASC) as "VOLUME_CHANGE_FROM_PREV_WEEK",

'100' * "VOLUME_CHANGE_FROM_PREV_WEEK" / "WEEKLY_TVL" AS "WEEKLY_PERCENT_CHANGE",

concat( dateadd(day, -7, last_day(EB.balance_date, 'week')), ' to ' , last_day(EB.balance_date, 'week')) AS "WEEK_START_END"

from ethereum.dex_liquidity_pools as LP

JOIN ethereum.erc20_balances as EB

ON LP.POOL_ADDRESS = EB.USER_ADDRESS

AND (LP.TOKEN0 = EB.CONTRACT_ADDRESS OR LP.TOKEN1 = EB.CONTRACT_ADDRESS)

WHERE

EB.AMOUNT_USD > 0

AND "CURRENT_WEEK" - "WEEK" > '0' -- Skipping the current week, as it is not complete.

AND "CURRENT_WEEK" - "WEEK" < '12'

AND LP.PLATFORM IN ('uniswap-v2','uniswap-v3')

GROUP BY 1,6

ORDER BY 1,6 ASC