How to get specific number of email send within a time

Question

I want to get a list of Subscriber that has received 13+ email in July and know their aggregate unique Opens. Here is what I did but there is no result returned.

SELECT 
se.SubscriberKey,
sub.EmailAddress,
se.EventDate
From _Sent se
INNER JOIN _Subscribers sub
ON se.SubscriberKey = sub.SubscriberKey
WHERE se.EventDate BETWEEN '2023-07-01 12:00:00' AND '2023-07-31 23:59:59'
AND se.SubscriberKey IN (
   SELECT sub.SubscriberKey AS SubscriberKey
   FROM _Subscribers sub
   GROUP BY sub.SubscriberKey
   HAVING COUNT (Sub.SubscriberKey) >= 13
)

Answer 1

The logic was correct, you had only the wrong Source DE for the grouping. Since you want to find subscribers who received at least 13 emails then we need to group not _Subscribers Data View but _Sent and then also while grouping we can also apply the timeframe to have more efficient filtering that would give us already fewer records to join with other tables.

SELECT 
se.SubscriberKey,
sub.EmailAddress,
se.EventDate
From _Sent se
INNER JOIN _Subscribers sub
ON se.SubscriberKey = sub.SubscriberKey
INNER JOIN (
    SELECT SubscriberKey
    FROM _Sent
    WHERE EventDate BETWEEN '2023-07-01 12:00:00' AND '2023-07-31 23:59:59'
    GROUP BY SubscriberKey
    HAVING COUNT(*) >= 13
) x
ON x.SubscriberKey = sub.SubscriberKey

Note: it might be a heavy SQL Query and it has a good chance to time out because we use Data Views as a source 3 times. Consider splitting the query into 2 pieces or using preloaded Data Views into Data Extensions for instance.