I'm trying to run this as a query activity (writing to a data extension) but am finding that it's timing out. It's not all that 'heavy' of a query but it's not completing. I'm a database developer by trade...but don't know the intricacies of SalesForce query. For example - I've tried to introduce variables and CTEs into this query but I'm getting an error stating that 'select' must be the first word in the query.
select j.JobID, j.emailname, j.emailsubject, a.Segment_Name, Count(distinct s.subscriberkey) Sends, Count(distinct o.subscriberkey) Opens, Count(distinct c.subscriberkey) Clicks, count(distinct u.subscriberkey) Unsubs, Count(distinct b.subscriberkey) Bounces, Count(distinct s.subscriberkey) - Count(distinct b.subscriberkey) Delivered from _Job j join _Sent s on j.JobID = s.JobID join [Snapshot - RPI_Standard_Audience 2_14_20] a on s.subscriberkey = a.email_address left join _Open o on j.JobID = o.JobID and a.email_address = o.subscriberkey left join _Click c on j.JobID = c.JobID and a.email_address = c.subscriberkey left join _Unsubscribe u on j.JobID = u.JobID and a.email_address = u.subscriberkey left join _Bounce b on j.JobID = b.JobID and a.email_address = b.subscriberkey where j.JobID in ('4142867','4142864','4142863') group by j.JobID, j.emailname, j.emailsubject, a.Segment_Name