MySQL - maximum of sum over different months with ties over multiple years
My attempt to solve this is as follows. I would appreciate any advice on how this query could be improved:
SELECT
t3.c_year AS "Year",
t3.c_month AS "Month",
t3.il_mc AS "Tumour count",
t4.ill_nat AS "Type" FROM
(
SELECT c_year, c_month, il_mc FROM
(
SELECT
c_year,
c_month,
MAX(month_count) AS il_mc
FROM
(
SELECT nature_of_illness as illness,
EXTRACT(YEAR FROM created_at) AS c_year,
EXTRACT(MONTH FROM created_at) AS c_month,
COUNT(EXTRACT(MONTH FROM created_at)) AS month_count
FROM illness
GROUP BY illness, c_year, c_month
ORDER BY c_year, c_month
) AS t1
GROUP BY c_year, c_month
) AS t2
) AS t3
JOIN
(
SELECT
EXTRACT(YEAR FROM created_at) AS t_year,
EXTRACT(MONTH FROM created_at) AS t_month,
nature_of_illness AS ill_nat,
COUNT(nature_of_illness) AS ill_cnt
FROM illness
GROUP BY t_year, t_month, nature_of_illness
ORDER BY t_year, t_month, nature_of_illness
) AS t4
ON t3.c_year = t4.t_year
AND t3.c_month = t4.t_month
AND t3.il_mc = t4.ill_cnt
And it does give the correct result, as can be seen in the fiddle here!