Здравствуйте, mogadanez, Вы писали:

Так?

Select  lu.email as email, e.subject_id, count( DISTINCT lu.email, e.subject_id ) as viewed
                        from  list_user lu
                        
                        left JOIN list l on l.id = lu.list_id
                        left JOIN automation_user_list aul   on l.id = aul.list_user_id
                        left JOIN automation_event ae on aul.automation_id = ae.automation_id
                        left JOIN automation_event_subject aes on aes.automation_event_id = ae.id
                        left JOIN automation a on a.id = ae.automation_id
                        left JOIN event e on e.company_id = a.company_id 
                                         and e.email = lu.email 
                                         and e.subject_id = aes.subject_id 
                                         and e.event_type_id = 5 
                                                     
                        left JOIN event e2 on e2.company_id = a.company_id 
                                         and e2.email = lu.email 
                                         and e2.subject_id = aes.subject_id 
                                         
                        where aul.automation_id = ? and ae.status > 0 and e.event_type_id = 5 and e2.event_type_id in (12, 13)
                        
                        GROUP by lu.email, e2.subject_id

Здравствуйте, mogadanez, Вы писали:

Так?

Select  lu.email as email, e.subject_id, count( DISTINCT lu.email, e.subject_id ) as viewed
                        from  list_user lu
                        
                        left JOIN list l on l.id = lu.list_id
                        left JOIN automation_user_list aul   on l.id = aul.list_user_id
                        left JOIN automation_event ae on aul.automation_id = ae.automation_id
                        left JOIN automation_event_subject aes on aes.automation_event_id = ae.id
                        left JOIN automation a on a.id = ae.automation_id
                        left JOIN event e on e.company_id = a.company_id 
                                         and e.email = lu.email 
                                         and e.subject_id = aes.subject_id 
                                                     
                        left JOIN event e2 on e2.company_id = a.company_id 
                                         and e2.email = lu.email 
                                         and e2.subject_id = aes.subject_id 
                                         
                        where aul.automation_id = ? and ae.status > 0 and e.event_type_id = 5 and e2.event_type_id in (12, 13)
                        
                        GROUP by lu.email, e2.subject_id