SQL Queries Guide — SFMC Mastery

SQL basics in SFMC

SQL in SFMC runs as a Query Activity inside Automation Studio. Every query selects data from one or more Data Extensions (or System Data Views) and writes the results into a target Data Extension. The target DE must exist before the query runs.

Key things to know

SFMC uses T-SQL (similar to Microsoft SQL Server) — not MySQL or PostgreSQL
Table names are wrapped in square brackets: [YourDataExtensionName]
Queries must have a SELECT statement — no UPDATE, DELETE, or INSERT (use AMPscript/SSJS for those)
Query results are written to a target DE — set Overwrite to replace the target each run, or Add/Update to upsert
System Data Views (like _Open, _Click) are available for up to 6 months of data
Queries time out after 30 minutes — optimise queries with indexes on join fields and reasonable date ranges

⚠️ Query mode: Overwrite vs Add/Update

Use Overwrite when your query builds a fresh segment each run (e.g. active subscribers). Use Add/Update when appending new records to an existing DE (e.g. a cumulative event log). Using Overwrite on an Add/Update target will wipe existing data.

SELECT & filtering

SQL Basic SELECT with WHERE filter

-- Select all UK subscribers who are opted in
SELECT
    SubscriberKey,
    EmailAddress,
    FirstName,
    LastName,
    Country
FROM [MasterSubscriberDE]
WHERE
    Country = 'UK'
    AND OptInStatus = 'Subscribed'
    AND EmailAddress IS NOT NULL

SQL DISTINCT — remove duplicates

-- Get one row per subscriber even if they appear multiple times
SELECT DISTINCT
    SubscriberKey,
    EmailAddress
FROM [MultipleEntriesDE]
WHERE OptInStatus = 'Subscribed'

SQL IN / NOT IN — filter by list of values

-- Subscribers in multiple specific countries
SELECT SubscriberKey, EmailAddress, Country
FROM [MasterSubscriberDE]
WHERE Country IN ('UK', 'US', 'CA', 'AU')

-- Exclude known internal/test emails
SELECT SubscriberKey, EmailAddress
FROM [MasterSubscriberDE]
WHERE EmailAddress NOT IN (
    'test@company.com',
    'admin@company.com',
    'noreply@company.com'
)

SQL LIKE — pattern matching

-- All Gmail subscribers
SELECT SubscriberKey, EmailAddress
FROM [MasterSubscriberDE]
WHERE EmailAddress LIKE '%@gmail.com'

-- Exclude all internal domains
SELECT SubscriberKey, EmailAddress
FROM [MasterSubscriberDE]
WHERE EmailAddress NOT LIKE '%@yourcompany.com'
  AND EmailAddress NOT LIKE '%@yourcompany.co.uk'

JOINs

SQL INNER JOIN — subscribers who made a purchase

-- Only subscribers who have at least one purchase
SELECT DISTINCT
    s.SubscriberKey,
    s.EmailAddress,
    s.FirstName,
    s.LastName
FROM [MasterSubscriberDE] s
INNER JOIN [OrdersDE] o
    ON s.SubscriberKey = o.SubscriberKey
WHERE
    s.OptInStatus = 'Subscribed'
    AND o.OrderStatus = 'Completed'

SQL LEFT JOIN — subscribers with or without purchase

-- All subscribers, with their most recent order date (NULL if never purchased)
SELECT
    s.SubscriberKey,
    s.EmailAddress,
    s.FirstName,
    MAX(o.OrderDate) AS LastOrderDate,
    CASE
        WHEN MAX(o.OrderDate) IS NULL THEN 'Never purchased'
        ELSE 'Has purchased'
    END AS PurchaseStatus
FROM [MasterSubscriberDE] s
LEFT JOIN [OrdersDE] o
    ON s.SubscriberKey = o.SubscriberKey
WHERE s.OptInStatus = 'Subscribed'
GROUP BY s.SubscriberKey, s.EmailAddress, s.FirstName

SQL NOT EXISTS — suppress recent purchasers

-- Subscribers who have NOT purchased in the last 30 days
SELECT
    s.SubscriberKey,
    s.EmailAddress
FROM [MasterSubscriberDE] s
WHERE s.OptInStatus = 'Subscribed'
  AND NOT EXISTS (
      SELECT 1
      FROM [OrdersDE] o
      WHERE o.SubscriberKey = s.SubscriberKey
        AND o.OrderDate >= DATEADD(day, -30, GETDATE())
  )

Date functions

SQL DATEADD, DATEDIFF, GETDATE

-- GETDATE() returns current datetime
-- DATEADD(unit, n, date) adds n units to a date
-- DATEDIFF(unit, date1, date2) returns difference between dates

SELECT
    SubscriberKey,
    EmailAddress,
    JoinDate,
    DATEDIFF(day, JoinDate, GETDATE())  AS DaysSinceJoin,
    DATEADD(day, 365, JoinDate)           AS OneYearAnniversary
FROM [MasterSubscriberDE]
WHERE
    -- Joined in the last 7 days
    JoinDate >= DATEADD(day, -7, GETDATE())

    -- OR: 1-year anniversary is today (±1 day)
    OR (
        DATEPART(month, JoinDate) = DATEPART(month, GETDATE())
        AND DATEPART(day, JoinDate) = DATEPART(day, GETDATE())
    )

SQL Birthday campaign segment — subscribers whose birthday is tomorrow

SELECT
    s.SubscriberKey,
    s.EmailAddress,
    s.FirstName,
    s.DateOfBirth
FROM [MasterSubscriberDE] s
WHERE
    DATEPART(month, s.DateOfBirth) = DATEPART(month, DATEADD(day, 1, GETDATE()))
    AND DATEPART(day,   s.DateOfBirth) = DATEPART(day,   DATEADD(day, 1, GETDATE()))
    AND s.OptInStatus = 'Subscribed'

CASE WHEN

SQL CASE WHEN — create derived columns

SELECT
    s.SubscriberKey,
    s.EmailAddress,
    s.LoyaltyPoints,
    CASE
        WHEN s.LoyaltyPoints >= 5000 THEN 'Gold'
        WHEN s.LoyaltyPoints >= 2000 THEN 'Silver'
        WHEN s.LoyaltyPoints >= 500  THEN 'Bronze'
        ELSE                               'Standard'
    END AS ComputedTier,

    CASE
        WHEN s.Country IN ('UK', 'IE') THEN 'GBP'
        WHEN s.Country IN ('US', 'CA') THEN 'USD'
        ELSE                                   'EUR'
    END AS Currency

FROM [MasterSubscriberDE] s
WHERE s.OptInStatus = 'Subscribed'

Aggregation

SQL COUNT, SUM, AVG — aggregate subscriber stats

-- Aggregate order stats per subscriber for the last 12 months
SELECT
    s.SubscriberKey,
    s.EmailAddress,
    COUNT(o.OrderID)      AS TotalOrders,
    SUM(o.OrderValue)     AS TotalSpend,
    AVG(o.OrderValue)     AS AvgOrderValue,
    MAX(o.OrderDate)      AS LastOrderDate,
    MIN(o.OrderDate)      AS FirstOrderDate
FROM [MasterSubscriberDE] s
INNER JOIN [OrdersDE] o
    ON s.SubscriberKey = o.SubscriberKey
    AND o.OrderDate >= DATEADD(month, -12, GETDATE())
WHERE s.OptInStatus = 'Subscribed'
GROUP BY s.SubscriberKey, s.EmailAddress
HAVING COUNT(o.OrderID) >= 2  -- at least 2 orders

System Data Views

SFMC automatically maintains a set of System Data Views populated with every email event. You query them just like any other DE but they are prefixed with an underscore.

SQL Query _Open and _Click for engagement data

-- Find subscribers who opened AND clicked in the last 60 days
SELECT DISTINCT
    s.SubscriberKey,
    s.EmailAddress,
    s.FirstName
FROM [MasterSubscriberDE] s
INNER JOIN [_Open] o
    ON s.SubscriberKey = o.SubscriberKey
    AND o.EventDate >= DATEADD(day, -60, GETDATE())
    AND o.IsUnique = 1
INNER JOIN [_Click] c
    ON s.SubscriberKey = c.SubscriberKey
    AND c.EventDate >= DATEADD(day, -60, GETDATE())
    AND c.IsUnique = 1
WHERE s.OptInStatus = 'Subscribed'

SQL _Bounce — identify hard bounced subscribers

-- Subscribers with a hard bounce in the last 90 days — suppress these
SELECT DISTINCT
    b.SubscriberKey,
    b.EmailAddress,
    b.BounceCategory,
    b.BounceSubcategory,
    MAX(b.EventDate) AS LastBounceDate
FROM [_Bounce] b
WHERE
    b.BounceCategory = 'Hard'
    AND b.EventDate >= DATEADD(day, -90, GETDATE())
GROUP BY b.SubscriberKey, b.EmailAddress, b.BounceCategory, b.BounceSubcategory

SQL _Unsubscribe — build global unsubscribe suppression list

-- All subscribers who have unsubscribed from any send
SELECT DISTINCT
    u.SubscriberKey,
    u.EmailAddress,
    MIN(u.EventDate) AS FirstUnsubDate,
    MAX(u.EventDate) AS LastUnsubDate
FROM [_Unsubscribe] u
GROUP BY u.SubscriberKey, u.EmailAddress

Ready-to-use queries

Engagement-based segment: Highly engaged 90 days

SQL Active segment — opened or clicked in 90 days

SELECT DISTINCT
    s.SubscriberKey,
    s.EmailAddress,
    s.FirstName,
    s.LastName,
    s.Country
FROM [MasterSubscriberDE] s
WHERE
    s.OptInStatus = 'Subscribed'
    AND s.SubscriberKey IN (
        SELECT DISTINCT SubscriberKey
        FROM [_Open]
        WHERE EventDate >= DATEADD(day, -90, GETDATE())
        UNION
        SELECT DISTINCT SubscriberKey
        FROM [_Click]
        WHERE EventDate >= DATEADD(day, -90, GETDATE())
    )

Sunset suppression: Unengaged 180+ days

SQL Sunset / unengaged suppression list

-- Subscribers with no open or click in 180 days
-- Use as suppression DE in batch sends
SELECT
    s.SubscriberKey,
    s.EmailAddress
FROM [MasterSubscriberDE] s
WHERE
    s.OptInStatus = 'Subscribed'
    AND s.SubscriberKey NOT IN (
        SELECT DISTINCT SubscriberKey
        FROM [_Open]
        WHERE EventDate >= DATEADD(day, -180, GETDATE())
        UNION
        SELECT DISTINCT SubscriberKey
        FROM [_Click]
        WHERE EventDate >= DATEADD(day, -180, GETDATE())
    )

New subscribers — joined in last 24 hours

SQL Welcome journey entry — new subscribers today

-- Used as a daily Automation Studio query to feed a Journey Builder entry DE
SELECT
    s.SubscriberKey,
    s.EmailAddress,
    s.FirstName,
    s.LastName,
    s.JoinDate,
    s.Source  -- where they signed up
FROM [MasterSubscriberDE] s
WHERE
    s.JoinDate >= DATEADD(hour, -24, GETDATE())
    AND s.OptInStatus = 'Subscribed'
    AND s.EmailAddress IS NOT NULL

RFM scoring query

SQL RFM — Recency, Frequency, Monetary scoring

SELECT
    s.SubscriberKey,
    s.EmailAddress,
    DATEDIFF(day, MAX(o.OrderDate), GETDATE())  AS Recency_Days,
    COUNT(o.OrderID)                               AS Frequency,
    SUM(o.OrderValue)                              AS Monetary_Total,

    CASE
        WHEN DATEDIFF(day, MAX(o.OrderDate), GETDATE()) <= 30  THEN 5
        WHEN DATEDIFF(day, MAX(o.OrderDate), GETDATE()) <= 60  THEN 4
        WHEN DATEDIFF(day, MAX(o.OrderDate), GETDATE()) <= 90  THEN 3
        WHEN DATEDIFF(day, MAX(o.OrderDate), GETDATE()) <= 180 THEN 2
        ELSE 1
    END AS R_Score,

    CASE
        WHEN COUNT(o.OrderID) >= 10 THEN 5
        WHEN COUNT(o.OrderID) >= 6  THEN 4
        WHEN COUNT(o.OrderID) >= 3  THEN 3
        WHEN COUNT(o.OrderID) >= 2  THEN 2
        ELSE 1
    END AS F_Score

FROM [MasterSubscriberDE] s
INNER JOIN [OrdersDE] o
    ON s.SubscriberKey = o.SubscriberKey
WHERE s.OptInStatus = 'Subscribed'
GROUP BY s.SubscriberKey, s.EmailAddress

Send performance report by email

SQL Email performance summary from system data views

SELECT
    j.JobID,
    j.EmailName,
    j.SendDate,
    COUNT(DISTINCT s.SubscriberKey)                        AS TotalSent,
    COUNT(DISTINCT CASE WHEN o.IsUnique = 1 THEN o.SubscriberKey END) AS UniqueOpens,
    COUNT(DISTINCT CASE WHEN c.IsUnique = 1 THEN c.SubscriberKey END) AS UniqueClicks,
    COUNT(DISTINCT b.SubscriberKey)                        AS Bounces,
    COUNT(DISTINCT u.SubscriberKey)                        AS Unsubscribes
FROM [_Job] j
LEFT JOIN [_Sent]        s  ON j.JobID = s.JobID
LEFT JOIN [_Open]        o  ON j.JobID = o.JobID
LEFT JOIN [_Click]       c  ON j.JobID = c.JobID
LEFT JOIN [_Bounce]      b  ON j.JobID = b.JobID
LEFT JOIN [_Unsubscribe] u  ON j.JobID = u.JobID
WHERE
    j.SendDate >= DATEADD(month, -3, GETDATE())
GROUP BY j.JobID, j.EmailName, j.SendDate
ORDER BY j.SendDate DESC