Postgres 10

By using db<>fiddle, you agree to license everything you submit by Creative Commons CC0.
Help with an interesting Postgres question: Why isn't an Index Only Scan used on a partition accessed via the parent table?.
CREATE table illness (nature_of_illness VARCHAR(25), created_at TIMESTAMP);

INSERT INTO illness VALUES 
  ('Cervix', '2018-01-03 15:45:40'),
  ('Cervix', '2018-01-03 15:45:40'),
  ('Cervix', '2018-01-03 15:45:40'),
  ('Cervix', '2018-01-03 15:45:40'),
  ('Lung',   '2018-01-03 17:50:32'),
  ('Lung',   '2018-02-03 17:50:32'),
  ('Lung',   '2018-02-03 17:50:32'),
  ('Lung',   '2018-02-03 17:50:32'),
  ('Cervix', '2018-02-03 17:50:32'),
-- 2017, with 1 Cervix and Lung each for the month of Jan - tie!
  ('Cervix', '2017-01-03 15:45:40'),
  ('Lung',   '2017-01-03 17:50:32'),
  ('Lung',   '2017-02-03 17:50:32'),
  ('Lung',   '2017-02-03 17:50:32'),
  ('Lung',   '2017-02-03 17:50:32'),
  ('Cervix', '2017-02-03 17:50:32') ;
15 rows affected

  SELECT  
    EXTRACT(YEAR  FROM year_month) AS c_year,
    EXTRACT(MONTH FROM year_month) AS c_month,
    nature_of_illness,
    month_count
  FROM
    (
      SELECT 
        nature_of_illness,
        DATE_TRUNC('month', created_at) AS year_month,
        COUNT(*) AS month_count,
        RANK() OVER (PARTITION BY DATE_TRUNC('month', created_at) 
                     ORDER BY COUNT(*) DESC)
          AS rnk
      FROM illness
      GROUP BY nature_of_illness, DATE_TRUNC('month', created_at)
    ) AS t
  WHERE rnk = 1 ;


c_year
c_month
nature_of_illness
month_count


2017
1
Cervix
1

2017
1
Lung
1

2017
2
Lung
3

2018
1
Cervix
4

2018
2
Lung
3


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


Year
Month
Tumour count
Type


2017
1
1
Cervix

2017
1
1
Lung

2017
2
3
Lung

2018
1
4
Cervix

2018
2
3
Lung