SQL for Adobe — Experience Platform Query Service

Concept

SELECT picks columns, FROM picks the dataset (table), LIMIT caps rows. Each AEP dataset is queryable as a table.

Where it's used

First look at newly ingested data in the Dev sandbox.

SELECT *
FROM crm_profiles
LIMIT 10;

Practice: Run this on your own CRM or web events dataset and note 3 fields you recognise from the client JSON.

Concept

WHERE filters rows; AND/OR combine conditions; IS NULL finds missing values; ORDER BY sorts.

Where it's used

Find records missing required fields before enabling Profile.

SELECT _tenantId.crmId, personalEmail.address
FROM crm_profiles
WHERE personalEmail.address IS NULL
ORDER BY _tenantId.crmId;

Practice: List every profile with no email. How many are there?

Concept

XDM data is nested. Use dot notation to reach inner fields, e.g. person.name.firstName or your custom _tenantId fields.

Where it's used

Reading the same structure you designed in the schema editor.

SELECT _tenantId.crmId AS crm_id,
       person.name.firstName AS first_name,
       person.name.lastName AS last_name
FROM crm_profiles;

Practice: Write a query returning crmId, first name, last name and email from your sample data.

Concept

COUNT, SUM, AVG, MIN, MAX summarise rows; GROUP BY makes one row per group; HAVING filters groups.

Where it's used

Ingestion QC: event counts per type per day vs. the source system.

SELECT eventType, COUNT(*) AS events
FROM web_events
WHERE timestamp >= current_date - INTERVAL 7 DAY
GROUP BY eventType
ORDER BY events DESC;

Practice: Count events per day for the last 7 days and compare with the source total.

Concept

Use CASE inside SUM to count conditions; GROUP BY + HAVING COUNT(*) > 1 finds duplicates.

Where it's used

QC checklist: null rates, duplicate IDs, consent coverage before activation.

SELECT COUNT(*) AS total,
  SUM(CASE WHEN personalEmail.address IS NULL
      THEN 1 ELSE 0 END) AS missing_email
FROM crm_profiles;

SELECT _tenantId.crmId, COUNT(*)
FROM crm_profiles
GROUP BY _tenantId.crmId
HAVING COUNT(*) > 1;

Practice: Calculate the % of profiles missing email and list any duplicate crmIds.

Concept

INNER JOIN keeps matches in both tables; LEFT JOIN keeps all rows from the left table even with no match.

Where it's used

Link purchases to profiles to check identity keys line up across datasets.

SELECT p._tenantId.crmId,
       COUNT(e._id) AS purchases
FROM crm_profiles p
LEFT JOIN purchase_events e
  ON p._tenantId.crmId = e._tenantId.crmId
GROUP BY p._tenantId.crmId;

Practice: Find customers with zero purchases. Which join type do you need, and why?

Concept

ROW_NUMBER() OVER (PARTITION BY … ORDER BY …) ranks rows within a group without collapsing them.

Where it's used

Get each customer’s latest event, or first purchase, for segmentation.

SELECT * FROM (
  SELECT _tenantId.crmId, eventType, timestamp,
    ROW_NUMBER() OVER (PARTITION BY _tenantId.crmId
      ORDER BY timestamp DESC) AS rn
  FROM web_events) t
WHERE rn = 1;

Practice: Return each customer’s most recent event type and time.

Concept

CREATE TABLE … AS SELECT saves query output as a new dataset; scheduled queries refresh it.

Where it's used

Build derived attributes (e.g. lifetime orders) for audiences and dashboards.

CREATE TABLE customer_order_summary AS
SELECT _tenantId.crmId AS crm_id,
       COUNT(*) AS lifetime_orders
FROM purchase_events
GROUP BY _tenantId.crmId;

Practice: Create a summary dataset and find it under Data management > Datasets.