PostgreSQL - maximum of sum over different months with ties over multiple years
For a given month:
SELECT tumour_count, illness
FROM (
   SELECT count(*) AS tumour_count, illness
        , rank() OVER (ORDER BY count(*) DESC) AS rnk
   FROM   illness
   WHERE  created_at  &gt;= '2017-01-01'  -- given month: 2007-01
   AND    created_at  &lt;  '2017-02-01'  -- optimized for index lookup
   GROUP  BY illness
   ) sub
WHERE  rnk = 1;

There should be an index on (created_at), or maybe even (created_at, illness) to allow index-only scans.
Subqueries are a bit faster than CTEs in Postgres. So only use CTEs where you need them, or when performance is not important.
Related:

How to optimise window queries in postgres
Count rows with max per group and additional condition

For any given period of time
As requested in the comment:
SELECT to_char(mon, 'YYYY-MM') AS month, tumour_count, illness
FROM  (
   SELECT date_trunc('month', created_at) AS mon
        , illness
        , count(*) AS tumour_count
        , rank() OVER (PARTITION BY date_trunc('month', created_at)
                       ORDER BY count(*) DESC) AS rnk
   FROM   illness
   WHERE  created_at  &gt;= '2017-01-01'  -- period from 2007-01 to 2019-01
   AND    created_at  &lt;  '2019-02-01'
   GROUP  BY 1, 2
   ) sub
WHERE  rnk = 1
ORDER  BY mon, illness;

Careful if you have leading or dangling partial months, the counts may be misleading.
This is functionally equivalent to what ypercube already provided. Just some simplifications to be a bit shorter / faster. And the added filter for a given period of time.
Index support becomes less important with the growing share of rows read from the table - and stops being useful at all for more than roughly 5 %. (Exceptions apply, like for index-only scans.)
You can still have 1 row per month, by aggregating tying peers. Like:
SELECT to_char(mon, 'YYYY-MM') AS month, tumour_count, string_agg(illness, ' | ')
FROM  (
   SELECT date_trunc('month', created_at) AS mon
        , illness
        , count(*) AS tumour_count
        , rank() OVER (PARTITION BY date_trunc('month', created_at)
                       ORDER BY count(*) DESC) AS rnk
   FROM   illness
   WHERE  created_at  &gt;= '2017-01-01'  -- period from 2007-01 to 2019-01
   AND    created_at  &lt;  '2019-02-01'
   GROUP  BY 1, 2
   ) sub
WHERE  rnk = 1
GROUP  BY mon, tumour_count
ORDER  BY mon;

db&lt;&gt;fiddle here