How to query records based on occurrence of a record

asp__developer · May 11, 2021, 2:00am

Test data in _Sent data view table - A log of sent emails are saved in this table

+-------+------------------+------------------+------------------------------------+
| JobID |   SubscriberID   |  SubscriberKey   |             EventDate              |
+-------+------------------+------------------+------------------------------------+
| 12301 | emai1@gmail.com  | emai1@gmail.com  | Monday, March 01, 2021 9:45 AM     |
| 12302 | email2@gmail.com | email2@gmail.com | Monday, March 01, 2021 9:50 AM     |
| 12303 | email3@gmail.com | email3@gmail.com | Tuesday, March 02, 2021 10:45 AM   |
| 12304 | email4@gmail.com | email4@gmail.com | Wednesday, March 03, 2021 11:45 AM |
| 12305 | emai1@gmail.com  | emai1@gmail.com  | Thursday, March 04, 2021 1:10 AM   |
| 12306 | email3@gmail.com | email3@gmail.com | Friday, March 05, 2021 3:20 AM     |
+-------+------------------+------------------+------------------------------------+

Test data in _Jobt data view table - A log of sent email job is saved in this table (one batch of send is associated with one job i.e., if 10,000 emails were sent in one batch then 10,000 rows will have same jobID)

+-------+--------------+------------------------------------+-------------+------------------+
| JobID |  EmailName   |            CreatedDate             |  Category   | SuppressTracking |
+-------+--------------+------------------------------------+-------------+------------------+
| 12301 | Email Name 1 | Monday, March 01, 2021 9:45 AM     | my tracking | FALSE            |
| 12302 | Email Name 2 | Monday, March 01, 2021 9:50 AM     | my tracking | FALSE            |
| 12303 | Email Name 3 | Tuesday, March 02, 2021 10:45 AM   | my tracking | FALSE            |
| 12304 | Email Name 4 | Wednesday, March 03, 2021 11:45 AM | my tracking | FALSE            |
| 12305 | Email Name 5 | Thursday, March 04, 2021 1:10 AM   | my tracking | FALSE            |
| 12306 | Email Name 6 | Friday, March 05, 2021 3:20 AM     | my tracking | FALSE            |
| 12307 | Email Name 7 | Saturday, March 06, 2021 1:10 AM   | my tracking | FALSE            |
| 12308 | Email Name 8 | Sunday, March 07, 2021 2:30 PM     | my tracking | FALSE            |
| 12309 | Email Name 9 | Monday, March 08, 2021 1:15 AM     | my tracking | FALSE            |
+-------+--------------+------------------------------------+-------------+------------------+

I have created a query to get count of subscribers who received emails in a specific time frame

    SELECT j.JobID AS Job_ID,  j.EmailName AS Email_Name, count(DISTINCT s.SubscriberID) as Sub_Count
    FROM _Sent s WITH (NOLOCK)
    INNER JOIN _Job j WITH (NOLOCK) on j.JobID = s.JobID
    where
    j.SuppressTracking = 'False' AND j.category = 'my tracking' AND cast(j.CreatedDate As Date) >=  cast('2021-03-01' as Date)
    AND cast(j.CreatedDate As Date) <=  cast('2021-03-31' as Date)
    GROUP BY j.JobID

But what do i need to do in the query if i am am looking for:

1) subscribers who received more than 1 emails within the time frame?

Desired output:

+------------------+-------------------------------------+
|  SubscriberKey   | EmailName_Count_received_by_contact |
+------------------+-------------------------------------+
| emai1@gmail.com  |                                   2 |
| email3@gmail.com |                                   2 |
| email5@gmail.com |                                   3 |
+------------------+-------------------------------------+

2) distinct email names for the subscribers who received multiple emails

Desired output:

+--------------+
|  EmailName   |
+--------------+
| Email Name 1 |
| Email Name 3 |
| Email Name 7 |
| Email Name 8 |
| Email Name 9 |
+--------------+

yosiasz · May 11, 2021, 3:08am

Please create proper DDL AND DML for this? Help us help you

declare @_sent(JobID int,  SubscriberID varchar(50), 
  SubscriberKey   varchar(50),  EventDate datetime)

insert into @_sent
select 12301, 'emai1@gmail.com', 'emai1@gmail.com', '2021-03-01 09:45:00'

fill out the rest

asp__developer · May 12, 2021, 5:53pm

Here you go @yosiasz - great idea, thanks

-- create a _Sent table
CREATE TABLE _Sent (
  JobID INTEGER NOT NULL,
  SubscriberID TEXT NOT NULL,
  SubscriberKey TEXT NOT NULL,
  EventDate datetime NOT NULL
);
-- insert some values
INSERT INTO _Sent VALUES (12301, 'emai1@gmail.com', 'emai1@gmail.com', 'Monday, March 01, 2021 9:45 AM');
INSERT INTO _Sent VALUES (12302, 'email2@gmail.com', 'email2@gmail.com', 'Monday, March 01, 2021 9:45 AM');
INSERT INTO _Sent VALUES (12303, 'email3@gmail.com', 'email3@gmail.com', 'Monday, March 01, 2021 9:45 AM');
INSERT INTO _Sent VALUES (12304, 'email4@gmail.com', 'email4@gmail.com', 'Monday, March 01, 2021 9:45 AM');
INSERT INTO _Sent VALUES (12305, 'emai1@gmail.com', 'emai1@gmail.com', 'Monday, March 01, 2021 9:45 AM');
INSERT INTO _Sent VALUES (12306, 'email3@gmail.com', 'email3@gmail.com', 'Monday, March 01, 2021 9:45 AM');





-- create a _Job table
CREATE TABLE _Job (
  JobID INTEGER NOT NULL,
  EmailName TEXT NOT NULL,
  CreatedDate  datetime NOT NULL,
  Category TEXT NOT NULL,
  SuppressTracking TEXT NOT NULL
);
-- insert some values
INSERT INTO _Job VALUES (12301, 'Email Name 1', 'Monday, March 01, 2021 9:45 AM', 'my tracking', 'FALSE');
INSERT INTO _Job VALUES (12302, 'Email Name 2', 'Monday, March 01, 2021 9:50 AM', 'my tracking', 'FALSE');
INSERT INTO _Job VALUES (12303, 'Email Name 3', 'Tuesday, March 02, 2021 10:45 AM', 'my tracking', 'FALSE');
INSERT INTO _Job VALUES (12304, 'Email Name 4', 'Wednesday, March 03, 2021 11:45 AM', 'my tracking', 'FALSE');
INSERT INTO _Job VALUES (12305, 'Email Name 5', 'Thursday, March 04, 2021 1:10 AM', 'my tracking', 'FALSE');
INSERT INTO _Job VALUES (12306, 'Email Name 6', 'Friday, March 05, 2021 3:20 AM', 'my tracking', 'FALSE');
INSERT INTO _Job VALUES (12307, 'Email Name 7', 'Saturday, March 06, 2021 1:10 AM', 'my tracking', 'FALSE');
INSERT INTO _Job VALUES (12308, 'Email Name 8', 'Sunday, March 07, 2021 2:30 PM', 'my tracking', 'FALSE');
INSERT INTO _Job VALUES (12309, 'Email Name 9', 'Monday, March 08, 2021 1:15 AM', 'my tracking', 'FALSE');

yosiasz · May 12, 2021, 9:59pm

always use table variables otherwise you need to provide a

drop table

thingy in there. it has to be re-runnable DDL and DML.

Also you need to test what you posted making sure it works on your computer first.

declare @_sent(JobID int,  SubscriberID varchar(50), 
  SubscriberKey   varchar(50),  EventDate datetime)

insert into @_sent
select 12301, 'emai1@gmail.com', 'emai1@gmail.com', '2021-03-01 09:45:00'

yours produces errors Look at the dates I provided here ^^
Help us help you