WITH MonthlyVolumes AS (
SELECT
SOURCE_CHAIN,
DATE_TRUNC('month', CREATED_AT) AS Month,
SUM(SEND_AMOUNT) AS Total_Volume
FROM axelar.axelscan.fact_transfers
GROUP BY SOURCE_CHAIN, DATE_TRUNC('month', CREATED_AT)
),
MaxMonthlyVolumes AS (
SELECT
SOURCE_CHAIN,
MAX(Total_Volume) AS Max_Volume
FROM MonthlyVolumes
GROUP BY SOURCE_CHAIN
)
SELECT
mv.SOURCE_CHAIN,
mv.Month,
mv.Total_Volume
FROM
MonthlyVolumes mv
JOIN
MaxMonthlyVolumes mmv
ON
mv.SOURCE_CHAIN = mmv.SOURCE_CHAIN AND mv.Total_Volume = mmv.Max_Volume
ORDER BY
mv.SOURCE_CHAIN, mv.Month;