gistfile1.sql

WITH lista AS (
	SELECT
		periodo
		,cod_crm
		,cod_espec
		,cod_benef
		,dtconsulta::date
		,first_value(dtconsulta::date) OVER(PARTITION BY(cod_crm,cod_espec,cod_benef) ORDER BY dtconsulta DESC) last_dtconsulta
		,count(CASE WHEN periodo = 201001 THEN 1 ELSE NULL END) OVER(PARTITION BY periodo,cod_crm,cod_espec) qtd_consultas
	FROM 
		public.consultas c
	WHERE
		periodo >= 200912 AND periodo <= 201001 AND
		char_length(coalesce(cod_benef,'') ) > 1 AND
		cod_espec > 0 
	ORDER BY
		cod_benef
		,cod_crm
		,cod_espec
		,periodo DESC
		,dtconsulta DESC
),  totaldias AS (
	SELECT
		periodo
		,cod_crm
		,cod_espec
		,cod_benef
		,dtconsulta
		,last_dtconsulta
		,first_value(qtd_consultas) OVER(PARTITION BY(cod_crm,cod_espec,cod_benef) ORDER BY dtconsulta DESC) qtd_consultas
		,last_dtconsulta - dtconsulta dias
	FROM
		lista
), reconsultas AS (
	SELECT
		* 
		,CASE WHEN dias BETWEEN 1 AND 30 THEN 1 END reconsultas_30
		,CASE WHEN dias BETWEEN 1 AND 40 THEN 1 END reconsultas_40
		,CASE WHEN dias BETWEEN 1 AND 60 THEN 1 END reconsultas_60
		,CASE WHEN dias BETWEEN 1 AND 90 THEN 1 END reconsultas_90
	FROM
		totaldias
	WHERE
		dias > 0
), indicadores AS (
	SELECT
		cod_crm
		,sum(reconsultas_30) reconsultas_30
		,sum(reconsultas_40) reconsultas_40
		,sum(reconsultas_60) reconsultas_60
		,sum(reconsultas_90) reconsultas_90
	FROM
		reconsultas
	GROUP BY
		cod_crm
	ORDER BY
		cod_crm
)
SELECT * FROM indicadores