Improving a SQL teradata query

Question

I have a table like below and I want 'Y' in front of Ref 345 and 789 in the result-set on basis of count(Ref) = 1 where the amount is less than 0. I am using this query to get the desired output. My question is, is there any other (and more efficient) way to do it in Teradata?

SELECT T.Ref,T.AMOUNT, R.Refund_IND as Refund_IND    
FROM Table1 t    
LEFT JOIN (select 'Y' as Refund_IND, Ref from Table1 where Ref in    
(select Ref from Table1 where amount < 0)     
group by Ref having count(Ref) = 1) R on t.Ref = R.Ref

Answer 1

You can use window functions to test these conditions:

 SELECT
     Ref,
     Amount,
     CASE WHEN COUNT(*) OVER (PARTITION BY REF) = 1 AND Amount < 0 THEN 'Y' ELSE '' END AS Refund_Ind 
  FROM Table1