Records duplicated on SQL Join (Marketing Cloud)

Question

I'm trying to return the results of subscribers who have been sent emails, when they've been sent, if they've opened/clicked and whether or not is house bounced/they've unsubscribed.

I'm using the following code which is working but it is creating duplicate rows rather than grouping by subscriber key and jobID;

SELECT
s.SubscriberKey, 
s.JobId, 
s.EventDate,
j.EmailName,
b.Bounces,
o.[Opens],
c.Clicks, 
u.Unsubscribed
FROM (SELECT 
    snt.SubscriberKey, 
    snt.JobId,
    snt.EventDate
 FROM _Sent snt
 GROUP BY snt.SubscriberKey, snt.JobID, snt.EventDate
) s
LEFT JOIN (
    SELECT JobID, COUNT(*) as [Bounces], bnc.SubscriberKey
    FROM [_Bounce] bnc
    GROUP BY bnc.JobID, bnc.SubscriberKey
) b
ON s.SubscriberKey = b.SubscriberKey
LEFT JOIN (
    SELECT SubscriberKey, COUNT(*) as [Opens], JobID
    FROM [_Open] op
GROUP BY op.SubscriberKey, op.JobID
) o
ON s.SubscriberKey = o.SubscriberKey
LEFT JOIN (
    SELECT cl.JobID, COUNT(*) as [Clicks], cl.SubscriberKey
    FROM [_Click] cl
    GROUP BY cl.JobID, cl.SubscriberKey
) c
ON s.SubscriberKey = c.SubscriberKey
LEFT JOIN
 (SELECT un.JobID, 
  COUNT(*) AS [Unsubscribed], 
  un.SubscriberKey
  FROM [_Unsubscribe] un
  GROUP BY un.JobId, un.SubscriberKey
 ) u
ON s.SubscriberKey = u.SubscriberKey
LEFT JOIN _Job j
ON s.JobId = j.JobId

For example, this returns;

SubscriberKey	JobID	EventDate	EmailName	Bounces	Opens	Clicks	Unsubs
001	14503	09/03/2022	Example 1	null	2	4	null
001	14503	09/03/2022	Example 1	null	1	2	null
002	14503	09/03/2022	Example 1	null	3	4	null
001	14505	08/03/2022	Example 2	null	1	0	null
001	14505	08/03/2022	Example 2	null	1	3	null

What I'm struggling to achieve is grouping the rows by the subscriber key and the Job ID.

Is anybody able to support?

Thanks in advance

Answer 1

You're applying GROUP BY inside the Inner Queries, that's the reason. I'd first gather everything raw and then apply GROUP BY in the end.

SELECT
s.SubscriberKey, 
s.JobId, 
s.EventDate,
j.EmailName,
count(b.SubscriberKey) as Bounces,
count(o.SubscriberKey) as Opens,
count(c.SubscriberKey) as Clicks, 
count(u.SubscriberKey) as Unsubscribed
FROM (SELECT 
    snt.SubscriberKey, 
    snt.JobId,
    snt.EventDate
 FROM _Sent snt
 GROUP BY snt.SubscriberKey, snt.JobID, snt.EventDate
) s
LEFT JOIN (
    SELECT JobID, bnc.SubscriberKey
    FROM [_Bounce] bnc
) b
ON s.SubscriberKey = b.SubscriberKey
LEFT JOIN (
    SELECT SubscriberKey, JobID
    FROM [_Open] op
) o
ON s.SubscriberKey = o.SubscriberKey
LEFT JOIN (
    SELECT cl.JobID, cl.SubscriberKey
    FROM [_Click] cl
) c
ON s.SubscriberKey = c.SubscriberKey
LEFT JOIN
 (SELECT un.JobID, 
  un.SubscriberKey
  FROM [_Unsubscribe] un
 ) u
ON s.SubscriberKey = u.SubscriberKey
LEFT JOIN _Job j
ON s.JobId = j.JobId
group BY
s.SubscriberKey, 
s.JobId, 
s.EventDate,
j.EmailName