Cacher is the code snippet organizer for pro developers

(SELECT 	
	a.piloto as piloto,
	SUM(a.click) as click, 
	SUM(a.views) as views	
 FROM
(
	SELECT 
		canv_idAgendamentoNotificacao as identitifier,
		CONVERT(VARCHAR, fa.fa_id) as piloto,
		COUNT(fv.canv_dataView) as views,	
		COUNT(fv.canv_dataClick) as click,	
		ROW_NUMBER() OVER(
                PARTITION BY fv.canv_idAgendamentoNotificacao 
                ORDER BY fan.fan_dataEnvio DESC
        ) rn
	FROM	
		FornecedorAgendamento  fa
		JOIN FornecedorAgendamentoNotificacao fan
		on fa.fa_id = fan.fan_idAgendamento
		 
		JOIN FornecedorAgendamentoNotificacaoView fv
		ON fan_id = canv_idagendamentonotificacao    		 
	WHERE 
		( fan.fan_dataEnvio > '01/10/2017' AND fan.fan_dataEnvio < '25/03/2018' ) 
		AND ( fv.canv_dataView IS NOT NULL or fv.canv_dataView <> '' )         
		AND (fv.canv_dataClick IS NULL or fv.canv_dataClick = '' )   		   
	GROUP BY 
		fa.fa_id,
        fv.canv_idAgendamentoNotificacao, 
        fan.fan_dataEnvio
                     	  
	UNION 
	  
	SELECT 
		0 as identitifier,
		CONVERT(VARCHAR, fa.fa_id) as piloto,
		COUNT(fv.canv_dataView)as views,	
		COUNT(fv.canv_dataClick) as click,		
	    ROW_NUMBER() OVER(
				PARTITION BY fv.canv_idAgendamentoNotificacao 
				ORDER BY fan.fan_dataEnvio DESC
		) rn
	FROM	
		FornecedorAgendamento  fa
		JOIN FornecedorAgendamentoNotificacao fan
		on fa.fa_id = fan.fan_idAgendamento
		 
		JOIN FornecedorAgendamentoNotificacaoView fv
		ON fan_id = canv_idagendamentonotificacao  
	WHERE 
		(fan.fan_dataEnvio > '01/10/2017' AND fan.fan_dataEnvio < '25/03/2018') 
		AND ( fv.canv_textoClick IS NOT NULL or fv.canv_dataClick <> '' )        
		AND ( fv.canv_dataView IS NULL or fv.canv_dataView = '' )  		
	GROUP BY 
		fa.fa_id,
        fv.canv_idAgendamentoNotificacao, 
        fan.fan_dataEnvio
)  a
	 WHERE rn = 1	 
	
	 GROUP BY a.piloto 	
	 
) ORDER BY a.piloto DESC