WITH A AS ( SELECT *

FROM ethereum.sushi.ez_swaps

WHERE BLOCK_TIMESTAMP::DATE >= '2022-07-01'),

----TO COUNT NUMBER OF UNIQUE USERS, TRANSACTIONS, DEPOSIT, WITHDRAWAL, AND NET

B AS( SELECT DATE_TRUNC('DAY', BLOCK_TIMESTAMP) AS BLOCK_DAY, COUNT(DISTINCT ORIGIN_FROM_ADDRESS) AS USERS, COUNT(TX_HASH) AS TX,

SUM(AMOUNT_IN_USD) AS DEPOSIT,SUM(AMOUNT_OUT_USD) AS WITHDRAWAL, SUM(AMOUNT_IN_USD)-SUM(AMOUNT_OUT_USD) AS NET

FROM A

WHERE BLOCK_TIMESTAMP::DATE BETWEEN '2022-07-01' AND CURRENT_DATE

GROUP BY BLOCK_DAY

ORDER BY BLOCK_DAY),

-------TO FILTER THE DATE

D AS (SELECT *

FROM ethereum.uniswapv3.ez_swaps

WHERE BLOCK_TIMESTAMP::DATE >= '2022-07-01'),

----TO COUNT NUMBER OF UNIQUE USERS, TRANSACTIONS, DEPOSIT, WITHDRAWAL, AND NET

E AS (SELECT DATE_TRUNC('DAY', BLOCK_TIMESTAMP) AS UNI_BLOCK_DAY, COUNT(DISTINCT SENDER) AS UNI_USERS, COUNT(TX_HASH) AS UNI_TX,

SUM(AMOUNT0_USD) AS UNI_DEPOSIT,SUM(AMOUNT1_USD) AS UNI_WITHDRAWAL, SUM(AMOUNT0_USD)-SUM(AMOUNT1_USD) AS UNI_NET

FROM D

WHERE BLOCK_TIMESTAMP::DATE BETWEEN '2022-07-01' AND CURRENT_DATE

GROUP BY UNI_BLOCK_DAY

ORDER BY UNI_BLOCK_DAY)

SELECT B.*,SUM(B.USERS) OVER(ORDER BY B.BLOCK_DAY) AS TOTAL_USERS, SUM(B.TX) OVER(ORDER BY B.BLOCK_DAY) AS TOTAL_TX, SUM(B.DEPOSIT) OVER(ORDER BY B.BLOCK_DAY) AS TOTAL_DEPOSIT,

SUM(B.WITHDRAWAL)OVER(ORDER BY B.BLOCK_DAY) AS TOTAL_WITHDRAWAL,SUM(B.NET)OVER(ORDER BY B.BLOCK_DAY) AS TOTAL_NET,

E.*,SUM(E.UNI_USERS) OVER(ORDER BY E.UNI_BLOCK_DAY) AS UNI_TOTAL_USERS, SUM(E.UNI_TX) OVER(ORDER BY E.UNI_BLOCK_DAY) AS UNI_TOTAL_TX, SUM(E.UNI_DEPOSIT) OVER(ORDER BY E.UNI_BLOCK_DAY) AS UNI_TOTAL_DEPOSIT,

SUM(E.UNI_WITHDRAWAL)OVER(ORDER BY E.UNI_BLOCK_DAY) AS UNI_TOTAL_WITHDRAWAL,SUM(E.UNI_NET)OVER(ORDER BY E.UNI_BLOCK_DAY) AS UNI_TOTAL_NET

FROM B

JOIN E

ON B.BLOCK_DAY = E.UNI_BLOCK_DAY