*

,AVG(usdc_volume) OVER(ORDER BY dt ROWS BETWEEN 29 PRECEDING AND CURRENT ROW) / 7 AS "USDC Volume 30D MA"

,AVG(usdt_volume) OVER(ORDER BY dt ROWS BETWEEN 29 PRECEDING AND CURRENT ROW) / 7 AS "USDT Volume 30D MA"

,"USDC Volume 30D MA" / ("USDC Volume 30D MA" + "USDT Volume 30D MA") * 100 AS "USDC Volume Dominance"

FROM (

SELECT

date_trunc('week',block_timestamp) AS dt

,SUM(CASE WHEN SYMBOL = 'USDC' THEN amount_usd ELSE NULL END) AS usdc_volume

,SUM(CASE WHEN SYMBOL = 'USDT' THEN amount_usd ELSE NULL END) AS usdt_volume

,AVG(CASE WHEN SYMBOL = 'USDC' THEN amount_usd ELSE NULL END) AS usdc_c

,AVG(CASE WHEN SYMBOL = 'USDT' THEN amount_usd ELSE NULL END) AS usdt_c

FROM polygon.core.ez_token_transfers

WHERE (

contract_address = lower('0x2791Bca1f2de4661ED88A30C99A7a9449Aa84174') -- USDC

OR

contract_address = lower('0xc2132D05D31c914a87C6611C10748AEb04B58e8F') -- USDT

)

AND dt > '2020-06-01'

GROUP BY 1

WHERE dt < date_trunc('week',CURRENT_DATE)

ORDER BY 1 DESC