WITH t AS
(
SELECT 'A' grp, 'APPLE' id, '사과' nm, '서울 서초구' addr, '20180101' sdt, '20180131' edt FROM dual
UNION ALL SELECT 'A', 'ANIMAL', '동물', '서울 강서구', '20180401', '20180531' FROM dual
UNION ALL SELECT 'A', 'ANIMAL', '동물', '서울 강남구', '20180101', '20180230' FROM dual
UNION ALL SELECT 'A', 'APPLE' , '사과', '서울 종로구', '20180501', '20180731' FROM dual
UNION ALL SELECT 'A', 'ANT'   , '개미', '서울 서초구', '20180104', '20180131' FROM dual
UNION ALL SELECT 'A', 'ANT'   , '개미', '서울 강서구', '20180201', '20180331' FROM dual
UNION ALL SELECT 'A', 'ANY'   , '애니', '서울 강남구', '20180101', '20180130' FROM dual
UNION ALL SELECT 'A', 'ANY'   , '애니', '서울 종로구', '20180131', '20180531' FROM dual
UNION ALL SELECT 'A', 'ANY'   , '애니', '서울 서초구', '20180601', '20180630' FROM dual
)
SELECT a.grp
     , a.id id_a, a.sdt sdt_a, a.edt edt_a
     , b.id id_b, b.sdt sdt_b, b.edt edt_b
     , GREATEST(a.sdt, b.sdt) sdt_dup
     , LEAST   (a.edt, b.edt) edt_dup
  FROM t a
     , t b
 WHERE a.grp  = b.grp
   AND a.id  != b.id
   AND a.sdt <= b.edt
   AND b.sdt <= a.edt
   AND a.grp  = 'A'
   AND a.id   = 'APPLE'
   AND MONTHS_BETWEEN( TO_DATE(LEAST   (a.edt, b.edt), 'yyyymmdd') + 1
                     , TO_DATE(GREATEST(a.sdt, b.sdt), 'yyyymmdd') ) >= 1
;