with t1 as 
(SELECT 
mnt,
case when rank() over (partition by ltrim(rtrim(mnt)) order by ltrim(rtrim(mnt)) asc) >1 then 'Y' else 'N'  end as flag,
rank() over (partition by mnt order by mnt) as rn,
dense_rank() over (partition by mnt order by mnt) as drn,
FROM tablename1),
t2 as (
SELECT
mnt, 
rel,
lif,
lts,
lokez
FROM tablename2
WHERE lts <> "" 
AND PARTITIONTIME = TIMESTAMP(CURRENT_DATE())
)
,t3 as 
(SELECT 
lif,
lifn, 
lts, 
par FROM tablename3)  

,t4 as (SELECT rcv FROM tablename4
WHERE mes = 'PRO')
select * from (
SELECT t1.mnt as mnt,
       t2.rel as zel,
       t2.lokez as ZLOEKZ,
       t4.rcv as Zrcv
       FROM t1
left join t2
on replace(t1.mnt, '00000000', '') = REPLACE(t2.mnt, '00000000', '') AND t1.lif = t2.lif and t2.lts <> "" 
and 
case when t1.flag = 'Y' and  t2.rel ='X' then 1
     when (t1.flag ='N' and t2.rel=t2.rel) or (t1.flag ='N' and t2.rel is null)  then 1
     when t1.flag = 'Y' and  t2.rel <>'X' then 2
     else 3
     end = 1
left join t3  ON t1.lif = t3.lif 
AND t2.lts = t3.lts
AND t3.par = 'BA'
left join t4 on t4.rcv = t3.lifn
and  t2.lokez is null 
) where ZLOEKZ is null 
order by mnt 

case when rank() over (partition by ltrim(rtrim(mnt)) order by ltrim(rtrim(mnt)) asc) >1 then 'Y' else 'N'  end as flag

case when t1.flag = 'Y' and  t2.rel ='X' then 1
     when (t1.flag ='N' and t2.rel=t2.rel) or (t1.flag ='N' and t2.rel is null)  then 1
     when t1.flag = 'Y' and  t2.rel <>'X' then 2
     else 3
     end = 1

mnt     flag     rn     drn     rel     lokez     rcv
100      N       1      1       X       abc       123
100      N       1      1      null     xyz       123
100      N       1      1      null     def       234