get most frequent values in every month in 2021

Trying to get the most frequent values in every month from tables

inspection table :

CREATE TABLE inspection (lno INT,
                         idate DATE,
                         iid INT,
                         stime TIME,
                         passed INT,
                         violations VARCHAR(100),
                         check (passed = 1 or passed = 0),
                         PRIMARY KEY(lno,idate),
                         FOREIGN  key (lno) REFERENCES restaurant);

can be ignored – > FOREIGN key (lno) REFERENCES restaurant)

data :

INSERT INTO inspection VALUES
(234,'6.1.2020' ,333, '16:00', 1 ,NULL),
(123,'7.2.2020' ,333 ,'12:15' ,0 ,'rats'),
(234, '7.2.2020', 333, '17:00', 0, 'Bugs'),
(456, '1.3.2021' ,222, '20:00' ,1,NULL),
(234, '10.3.2021', 333, '16:00', 1,NULL),
(567, '24.3.2021' ,333, '17:00' ,1,NULL),
(345, '9.4.2021' ,222, '18:00', 0, 'Rats'),
(345, '30.4.2021' ,222, '18:00' ,1,NULL),
(123,'11.5.2021', 111, '19:40', 0 ,'Mold'),
(567, '15.5.2021' ,111 ,'19:00' ,1,NULL),
(345, '17.5.2021' ,222, '19:00' ,1,NULL),
(456, '19.5.2021', 111 ,'17:00', 0 ,'Bats'),
(123, '13.6.2021' ,222, '13:00', 1,NULL),
(456, '16.6.2021' ,333 ,'21:00' ,0 ,'Mold');

query :

    SELECT date_part('month', idate) ,max(iid)
FROM inspector natural join inspection where date_part('year', idate) >=  date_part('year', current_date)
GROUP BY date_part('month', idate) 

output:

month	id
3	333
4	222
5	222
6	333

expected output –

month	id
3	333
4	222
5	111
6	222
6	333

Answer

IMHO you don’t need the inspector table for this calculation. A query like this would do:

with t1(month, iid, cnt) as 
(
   select date_part('month', idate), iid, count(*) 
   from inspection
   where date_part('year', idate) = date_part('year',current_date)
   group by date_part('month', idate), iid
),
t2 (month, maxCnt) as 
(
  select month, max(cnt)
  from t1
  group by month
)
select t1.month, t1.iid 
from t1 
  inner join t2 on t1.month = t2.month and t1.cnt = t2.maxCnt
order by t1.month, t1.iid;

Here is Dbfiddle demo link.