重かったSQL.sql

-- select 
--  A.asmst_id, 
--  B.email, B.entry_group_cd, 
--  C.website_name 

select count(*)
 from ( 
  select 
   a.asmst_id 
   from ( 
    select 
     ad.asmst_id, ad.ins_id 
     from 
     agreement_data ad 
     where 
     ad.sp_id = 's00000000018'
     and ad.ins_id in ('s00000000018015') 
     and ad.accept_cd in (13, 14, 15, 16) 
     and ad.del_flg = '0' 
     and not exists( 
                    select * 
                    from 
                    as_ticket_mst atm 
                    where 
                    ad.asmst_id = atm.asmst_id 
                    and atm.status_cd in('0002') 
                    and atm.ticket_close_ymd is null 
                   ) 
  ) a 
  inner join 
  ( 
   select 
    ins_id 
    from 
    program_data 
    where 
    del_flg is null 
  ) b 
  on a.ins_id = b.ins_id 
  group by 
  a.asmst_id 
 ) A 
 inner join 
 ( 
  select 
   asmst_id, email, entry_group_cd 
   from 
   as_admin_mst 
   where 
   email_validate_flg = '0' 
   and entry_group_cd <> '099' 
 ) B 
 on A.asmst_id = B.asmst_id 
 inner join 
 ( 
  select 
   asmst_id,  
   min(website_name) keep ( dense_rank first order by website_id ) as website_name,  
   min(website_id) as website_id 
   from 
   as_data 
   where 
   screening_flg = '1' 
   and 
   del_flg is null 
   group by asmst_id 
 ) C 
 on A.asmst_id = C.asmst_id 
 order by A.asmst_id 
;