Trouble With Query in SQL

Question

I have the following inconvenience with SQL.
I have to list the contents reproduced more than 3 times in each month of 2018 and less than 2 in the months that go of the 2019.

The structure for that is the following:

CREATE TABLE content (
    content INT NOT NULL,
    CONSTRAINT PK_Codcontent PRIMARY KEY(Codcontent) 
)

CREATE TABLE Reproduced (
    Coduser INT NOT NULL,
    Codcontent INT NOT NULL,
    CONSTRAINT PK_Coduser PRIMARY KEY(Coduser, Codcontent),
    CONSTRAINT FK_Codusr FOREIGN KEY(Coduser) REFERENCES Perfil(Codpuser),
    CONSTRAINT FK_Codcont FOREIGN KEY(Codcontent) REFERENCES Contenido(Codcontent)
)

I have created the following view to list the contents, reproductions and months of the whole year, but the thing is that I do not know how to filter those with more than 3 reproductions for each month.

CREATE VIEW TODOSLOSMESES2018
AS
SELECT
      R.Codcontenido
     , count(*)Reproducciones
     , count(Distinct Month(R.fecha)) as 'Meses'
FROM (
       SELECT * 
       FROM Reproduce 
       WHERE fecha>='20180101' AND fecha<='20181231')R
group by Codcontenido
HAVING count(Distinct Month(R.fecha))=12

Answer 1

You can use conditional aggregation -- but two levels. One by content and month and the other by content:

select codcontent
from (select codcontent, year(fecha) as yr, month(fecha) as mon, count(*) as cnt
      from reproduced r
      group by codcentent, year(fecha), month(fecha)
     ) r
group by codcontent
having sum(case when year = 2018 then 1 else 0 end) = 12 and  -- all months
       min(case when year = 2018 then cnt end) >= 3 and
       max(case when year = 2019 then cnt else 0 end) < 3;