DATE_TRUNC ('Day', Block_timestamp) as Date,

'COQ' AS memecoin,

sum(CASE WHEN SYMBOL_IN != 'COQ' THEN AMOUNT_IN_USD ELSE 0 END) AS "BOUGHT VOLUME",

SUM (CASE WHEN SYMBOL_IN = 'COQ' THEN AMOUNT_IN_USD ELSE 0 END) AS "SOLD VOLUME",

SUM (CASE WHEN SYMBOL_IN != 'COQ' THEN AMOUNT_IN_USD ELSE AMOUNT_IN_USD * -1 END) AS "NET FLOW",

SUM ("BOUGHT VOLUME") OVER (ORDER by date) as "TOTAL BOUGHT VOLUME",

SUM ("SOLD VOLUME") OVER (ORDER BY Date) as "TOTAL SOLD VOLUME",

SUM ("NET FLOW") OVER (ORDER BY DATE) AS "TOTAL NET FLOW"

FROM avalanche.defi.ez_dex_swaps

WHERE

SYMBOL_IN = 'COQ'

OR

SYMBOL_OUT = 'COQ'

GROUP BY 1

ORDER by 1 DESC)

UNION ALL

(SELECT

DATE_TRUNC ('Day', Block_timestamp) as Date,

'KIMBO' AS memecoin,

sum(CASE WHEN SYMBOL_IN != 'KIMBO' THEN AMOUNT_IN_USD ELSE 0 END) AS "BOUGHT VOLUME",

SUM (CASE WHEN SYMBOL_IN = 'KIMBO' THEN AMOUNT_IN_USD ELSE 0 END) AS "SOLD VOLUME",

SUM (CASE WHEN SYMBOL_IN != 'KIMBO' THEN AMOUNT_IN_USD ELSE AMOUNT_IN_USD * -1 END) AS "NET FLOW",

SUM ("BOUGHT VOLUME") OVER (ORDER by date) as "TOTAL BOUGHT VOLUME",

SUM ("SOLD VOLUME") OVER (ORDER BY Date) as "TOTAL SOLD VOLUME",

SUM ("NET FLOW") OVER (ORDER BY DATE) AS "TOTAL NET FLOW"

FROM avalanche.defi.ez_dex_swaps