SQL

Home Page

Harness the Potential of SQL: A Valuable Collection of Code Snippets and Examples

Write SQL to find subscribers who received an email for a campaign

SELECT 
     S.SubscriberKey
    ,EmailName
    ,J.JobId
    ,S.EventDate
    ,CampaignCode
FROM _Sent S
INNER JOIN _Job J 
ON S.JobId = J.JobId
INNER JOIN _JourneyActivity JA 
ON JA.JourneyActivityObjectID = J.TriggererSendDefinitionObjectID
INNER JOIN Send_Log Sl on Sl.JobID = J.JobId and Sl.SubscriberKey=S.SubscriberKey
WHERE CampaignCode='CAMPAIGN001'
                                    
                                                            

Write SQL to find subscribers last opened date

SELECT distinct 
       O.SubscriberKey
      , max(O.EventDate)  AS LastOpeningDate
      ,Sl.utm_campaign AS Campaign_Code,O.JobId
FROM _Open O
INNER JOIN _JourneyActivity JA 
ON JA.JourneyActivityObjectID = O.TriggererSendDefinitionObjectID
INNER JOIN Send_Log Sl on Sl.JobID = O.JobId AND Sl.SubscriberKey=O.SubscriberKey
WHERE O.JobId IN (SELECT distinct JobId FROM Send_Log)
GROUP BY O.SubscriberKey, Sl.utm_campaign,O.JobId
                                    
                        

Write SQL to find a journey where journey name contains "CAMPAIGN_WEBCAST"

SELECT distinct 
       JourneyID
      ,JourneyName
      ,JA.VersionID
      ,JourneyActivityObjectID
FROM  _JourneyActivity JA 
INNER JOIN _Journey Jo ON Jo.VersionID = JA.VersionID 
WHERE UPPER(Jo.JourneyName) like UPPER('%CAMPAIGN_WEBCAST%')
                                    
                        

Write SQL to find a subscribers firstName and LastName when you don't have a centrailsed nomencalture

 select SubscriberKey, Email_Address,
case 
when (
       select First_Name from [DE1] vp where vp.Subscriber_Key=ng.SubscriberKey
     ) is null 
then (
    select FirstName from [DE2] mn where mn.SubscriberKey=ng.SubscriberKey
     )
else
(
select First_Name from [DE1] vp where vp.Subscriber_Key=ng.SubscriberKey
)
end as First_Name,
case 
when (
select Last_Name from [DE1] vp where vp.Subscriber_Key=ng.SubscriberKey
) is null 
then (select LastName from [DE2] mn where mn.SubscriberKey=ng.SubscriberKey)
else
(select Last_Name from [DE1] vp where vp.Subscriber_Key=ng.SubscriberKey)
end as Last_Name 
from TARGETDE ng                                 
                                    
                        

JOIN WITH ONLY ONE RECORD TO AVOID DUPLICATE DATA FROM DE 2

select 
s.SubscriberKey as SUBSCRIBER_KEY,
GetDate() as CREATED_DATE,
GetDate() as MODIFIED_DATE
from  Send_log s
inner join DE1 v 
on s.SubscriberKey=v.Subscriber_Key
left join [DE2] m
on s.campaign=m.CampaignCode
where S.EVENTDATE >= DATEADD(hour, -24, GETDATE())
and  m._customObjectKey = (select max(x._customObjectKey)
                            from [DE2] x
                            where x.campaign=m.CampaignCode)                             
                                    
                        

MERGE 2 DATA EXTENSION INTO ONE

SELECT 
Attribute5, Attribute6, Attribute7, Attribute8, 
Attribute9, Attribute10, Attribute11, Attribute12, Attribute13, 
Attribute14, Attribute15, Attribute16, Attribute17, 
Attribute18, Attribute19, Attribute20
FROM [DE1] 
UNION
SELECT 
Attribute5, Attribute6, Attribute7, Attribute8, 
Attribute9, Attribute10, Attribute11, Attribute12, Attribute13, 
Attribute14, Attribute15, Attribute16, Attribute17,
Attribute18, Attribute19, Attribute20
FROM (
SELECT 
ROW_NUMBER() OVER (PARTITION BY Attribute1 ORDER BY Attribute2 ASC) AS row_number,
Attribute5, Attribute6, Attribute7, Attribute8, 
Attribute9, Attribute10, Attribute11, Attribute12, Attribute13, 
Attribute14, Attribute15, Attribute16, Attribute17, 
Attribute18, Attribute19, Attribute20
FROM [DE2] 
) TEMP
WHERE row_number=1                            
                                    
                        

Write a SQL to find the most likely Day when a subscriber opens an email

SELECT 
SubscriberKey,
CASE 
WHEN s.WEEKDAY=0 THEN 'MONDAY'
WHEN s.WEEKDAY=1 THEN 'TUSEDAY'
WHEN s.WEEKDAY=2 THEN 'WEDNESDAY'
WHEN s.WEEKDAY=3 THEN 'THURSDAY'
WHEN s.WEEKDAY=4 THEN 'FRIDAY'
WHEN s.WEEKDAY=5 THEN 'SATURDAY'
WHEN s.WEEKDAY=6 THEN 'SUNDAY'
END
AS LIKELYOPENSON
FROM
(SELECT MAX(DATEPART(WEEKDAY, EventDate)) AS WEEKDAY,SubscriberKey from _Open
WHERE EventDate >= DATEADD(day, -30, GETDATE())
group by SubscriberKey) s
WHERE SubscriberKey IN (SELECT SubscriberKey FROM Interaction_Status )                           
                                    
                        

FREQUENCY OF EMAIL SENTS PER MONTH TO A SUBSCRIBER

 SELECT
      subs.EmailAddress
    , temp.[Counter]
FROM (
    SELECT
          SubscriberKey
        , COUNT(*) AS [Counter]
    FROM _Sent
    WHERE DATEDIFF(MONTH,EventDate,GETDATE())=1
    GROUP BY SubscriberKey
) AS temp
    INNER JOIN ent._Subscribers AS subs
        ON temp.SubscriberKey = subs.SubscriberKey
                        

FREQUENCY OF EMAIL Opened PER MONTH TO A SUBSCRIBER

SELECT
      subs.EmailAddress
    , temp.[Counter]
FROM (
    SELECT
          SubscriberKey
        , COUNT(*) AS [Counter]
    FROM _Open
    WHERE DATEDIFF(MONTH,EventDate,GETDATE())=1
    GROUP BY SubscriberKey
) AS temp
    INNER JOIN ent._Subscribers AS subs
        ON temp.SubscriberKey = subs.SubscriberKey
                        

FREQUENCY OF EMAIL Clicked PER MONTH TO A SUBSCRIBER

SELECT
      subs.EmailAddress
    , temp.[Counter]
FROM (
    SELECT
          SubscriberKey
        , COUNT(*) AS [Counter]
    FROM _Click
    WHERE DATEDIFF(MONTH,EventDate,GETDATE())=1 
    AND IsUnique=1
    GROUP BY SubscriberKey
) AS temp
    INNER JOIN ent._Subscribers AS subs
        ON temp.SubscriberKey = subs.SubscriberKey
                        

FETCH MAX EVENT DATE FROM DATA VIEWS TO CAPTURE LATEST INTERACTION OF THE SUBSCRIBERS FOR JOBID 1234

select subscriberkey, Max(eventdate) As MaxInteractionDate
from (
          select subscriberkey, eventdate
          from _sent
          where jobid=1234
      union
          select subscriberkey, eventdate 
          from _open
          where jobid=1234
      union
          select subscriberkey, eventdate 
          from _click
          where jobid=1234
      union
          select subscriberkey, eventdate 
          from _Unsubscribe 
          where jobid=1234 
    ) temp
group by subscriberkey
                        

Write a query to fetch subscriber associated with a journey for the last 90 days

Select Distinct
     Subscriberkey
   ,  JourneyName
from _JourneyActivity 
  inner join 
  _sent on
      _JourneyActivity.JourneyActivityObjectID=_sent.TriggererSendDefinitionObjectID
   inner join 
   _Journey on
      _JourneyActivity.VersionID=_Journey.VersionID
       where DATEDIFF(D,_sent.eventdate,GETDATE())=90
                        

Write a query to fetch subscriber opted out from SMS

 select
        SubscriberKey,
        MobileNumber,
        OptInStatusID,
        OptOutStatusID,
        ModifiedDate
    from _SMSSubscriptionLog 
    where
        datediff(d,ModifiedDate,Getdate())=1
        and 
        SubscriptionDefinitionID='abcd'
        and
        (OptOutStatusID = 1 or OptInStatusID = 2)
                        

WRITE A QUERY TO FIND OUT EMAIL DELIVERED

SELECT 
     S.AccountID
    ,S.OYBAccountID
    ,S.JobID
    ,S.ListID
    ,S.BatchID
    ,S.SubscriberID
    ,S.SubscriberKey
    ,S.EventDate
    ,S.[Domain]
FROM _SENT S
WHERE 
 NOT EXISTS 
(
SELECT 
      B.JOBID
     ,B.LISTID
     ,B.BATCHID
     ,B.SUBSCRIBERID
FROM _BOUNCE B
WHERE 
     S.JOBID=B.JOBID
AND  S.LISTID=B.LISTID
AND  S.BATCHID=B.BATCHID
AND  S.SUBSCRIBERID=S.SUBSCRIBERID )
AND DATEDIFF(MONTH,S.EVENTDATE,GETDATE())=1
                        

Write a query to count no. of Sends per running journey

SELECT
  count(S.JOBID) as SENT,
  JourneyName
FROM _SENT S
INNER JOIN
(
SELECT 
  ActivityName
, ActivityID
, JourneyActivityObjectID
, ActivityType
, ActivityExternalKey, 
_JourneyActivity.VersionID
,JourneyName
FROM _JourneyActivity inner join _Journey ON
_JourneyActivity.VersionID=_Journey.VersionID
WHERE _Journey.JourneyStatus='Running'
) JOURNEY
ON JourneyActivityObjectID =S.TriggererSendDefinitionObjectID 
GROUP BY JourneyName
                        

Write a query to count no. of Opens per running journey

SELECT
  count(S.JOBID) as OPEN,
  JourneyName
FROM _OPEN O
INNER JOIN
(
SELECT 
  ActivityName
, ActivityID
, JourneyActivityObjectID
, ActivityType
, ActivityExternalKey, 
_JourneyActivity.VersionID
,JourneyName
FROM _JourneyActivity inner join _Journey ON
_JourneyActivity.VersionID=_Journey.VersionID
WHERE _Journey.JourneyStatus='Running'
) JOURNEY
ON JourneyActivityObjectID =O.TriggererSendDefinitionObjectID 
GROUP BY JourneyName
                        

Write a query to count no. of Clicks per running journey

SELECT
  count(S.JOBID) as CLICK,
  JourneyName
FROM _CLICK C
INNER JOIN
(
SELECT 
  ActivityName
, ActivityID
, JourneyActivityObjectID
, ActivityType
, ActivityExternalKey, 
_JourneyActivity.VersionID
,JourneyName
FROM _JourneyActivity inner join _Journey ON
_JourneyActivity.VersionID=_Journey.VersionID
WHERE _Journey.JourneyStatus='Running'
) JOURNEY
ON JourneyActivityObjectID =C.TriggererSendDefinitionObjectID 
GROUP BY JourneyName
                        

Write a query to count no. of bounced per running journey

SELECT
  count(S.JOBID) as BOUNCED,
  JourneyName
FROM _BOUNCE B
INNER JOIN
(
SELECT 
  ActivityName
, ActivityID
, JourneyActivityObjectID
, ActivityType
, ActivityExternalKey, 
_JourneyActivity.VersionID
,JourneyName
FROM _JourneyActivity inner join _Journey ON
_JourneyActivity.VersionID=_Journey.VersionID
WHERE _Journey.JourneyStatus='Running'
) JOURNEY
ON JourneyActivityObjectID =B.TriggererSendDefinitionObjectID 
GROUP BY JourneyName
                        

Write a query to count no. of unsubscribed per running journey

SELECT
  count(S.JOBID) as UNSUBSCRIBED,
  JourneyName
FROM _UNSUBSCRIBE U
INNER JOIN
_SENT S
ON U.JOBID=S.JOBID
AND U.LISTID=S.LISTID
AND U.BATCHID=S.BATCHID
AND U.SUBSCRIBERKEY=S.SUBSCRIBERKEY
INNER JOIN
(
SELECT 
  ActivityName
, ActivityID
, JourneyActivityObjectID
, ActivityType
, ActivityExternalKey, 
_JourneyActivity.VersionID
,JourneyName
FROM _JourneyActivity inner join _Journey ON
_JourneyActivity.VersionID=_Journey.VersionID
WHERE _Journey.JourneyStatus='Running'
) JOURNEY
ON JourneyActivityObjectID =S.TriggererSendDefinitionObjectID 
GROUP BY JourneyName
                        

Write a query to retrieve records from Contacts without duplicates joining Ticket Sales. One contact can buy many tickets with same email I’d.

SELECT
    Email,
    [Name]
FROM
(
SELECT 
     Email
    ,[Name]
    ,RN =ROW_NUMBER() OVER (PARTITION BY Contact.Email ORDER BY Contact.ContactID)
FROM Contact JOIN TicketSales ON Contact.ContactID=TicketSales.ContactID
) T
WHERE RN=1
                        

Write a query to retrieve subscribers whose birthday is after 15 or 30 days.

SELECT 
        ContactKey
       ,Birthdate
FROM   B1pA6fPm
WHERE 
        (
        DAY(Birthdate)= DAY(DATEADD(DAY,+15,GETDATE()))
        AND
        MONTH(Birthdate)= MONTH(DATEADD(DAY,+15,GETDATE()))
        )
        OR
       ( 
        DAY(Birthdate)= DAY(DATEADD(DAY,+30,GETDATE()))
        AND
        MONTH(Birthdate)= MONTH(DATEADD(DAY,+30,GETDATE()))
        )
                        

Extract distinct email domains from Contact Object for IP warm-up planning.

SELECT DISTINCT
                REVERSE
                (LEFT(
                         REVERSE(PersonEmail)
                        ,CHARINDEX('@',REVERSE(PersonEmail))-1
                     )
                )
FROM 
     ENT.[Contact_Salesforce]
WHERE PersonEmail IS NOT NULLT
                        

Prepare a not sent summary report for you Journey Sends

SELECT 
    notSent.SubscriberKey,
    notSent.EventDate,
    notSent.TriggeredSendExternalKey,
    notSent.Reason,
    _Journey.JourneyName,
    _JourneyActivity.ActivityName
FROM 
    NotSent_DataViews notSent
INNER JOIN 
    TriggeredSendDefinition tsd ON tsd.CustomerKey = notSent.TriggeredSendExternalKey
INNER JOIN 
    _JourneyActivity ON tsd.ObjectID = _JourneyActivity.JourneyActivityObjectID
INNER JOIN 
    _Journey ON _JourneyActivity.VersionID = _Journey.VersionID


                        

Write a SQL to update the filename into the import results summary

SELECT
        IDR.OBJECTID,
        IDR.ImportDefinitionCustomerKey,
        ID.FileSpec
FROM ImportDefinitionResults IDR
LEFT JOIN ImportDefinition ID
          ON ID.CustomerKey= IDR.ImportDefinitionCustomerKey
                         

Write a SQL for Mobile Push Address

SELECT 
    PA._DeviceID AS DeviceID,
    PA._ContactID AS ContactID,
    PA._APID AS APID,
    PA._Status AS Status,
    PA._Source AS Source,
    PA._SourceObjectId AS SourceObjectId,
    PA._Platform AS Platform,
    PA._PlatformVersion AS PlatformVersion,
    PA._Alias AS Alias,
    PA._OptOutStatusID AS OptOutStatusID,
    PA._OptOutMethodID AS OptOutMethodID,
    PA._OptOutDate AS OptOutDate,
    PA._OptInStatusID AS OptInStatusID,
    PA._OptInMethodID AS OptInMethodID,
    PA._OptInDate AS OptInDate,
    PA._Channel AS Channel,
    PA._CreatedDate AS PushAddressCreatedDate,
    PA._CreatedBy AS PushAddressCreatedBy,
    PA._ModifiedDate AS PushAddressModifiedDate,
    PA._ModifiedBy AS PushAddressModifiedBy,
    PA._City AS City,
    PA._State AS State,
    PA._ZipCode AS ZipCode,
    PA._FirstName AS FirstName,
    PA._LastName AS LastName,
    PA._UTCOffset AS UTCOffset,
    PA._IsHonorDST AS IsHonorDST,
    PA._SystemToken AS SystemToken,
    PA._ProviderToken AS ProviderToken,
    PA._Badge AS Badge,
    PA._LocationEnabled AS LocationEnabled,
    PA._TimeZone AS TimeZone,
    PA._Device AS Device,
    PA._HardwareId AS HardwareId,
    PA._DeviceType AS DeviceType,
    MLV.ContactKey AS ContactKey,
    MLV.ChannelID AS ChannelID,
    MLV.AddressID AS AddressID,
    MLV.IsActive AS IsActive,
    MLV.CreatedDate AS SubscriptionCreatedDate,
    MLV.ModifiedDate AS SubscriptionModifiedDate
FROM 
    _PushAddress PA
LEFT JOIN 
    _MobileLineAddressContactSubscriptionView MLV
ON 
    PA._ContactID = MLV.ContactID

                         

TOP