# Marketing Campaigns V2 Segmentation Query Reference

This document is a reference for the V2 Segmentation API. The V2 Segmentation API allows you to create and manage segments using a subset of SQL that includes statements and operations necessary to define any segment. In addition, this version of the Segmentation API exposes the `contact_data` and `event_data` tables to enable SQL-compatible queries.

## General Query Format

```sql
SELECT contact_id, updated_at
FROM <table_reference> [ AS <table_alias> ]
[ [ INNER ] JOIN <table_reference> ON contact_id ]
[ WHERE <predicate> ]
```

In the previous example, the `contact_id` and `updated_at` columns are selected in the query. These two columns must be selected, and aggregate function MAX and its alias can be selected part of [Last Event](#email-activity---last-event-examples) queries. Other than those columns, no additional columns can be selected. When joining two tables, they must be joined on the `contact_id` column only because it is the foreign key used to connect the `contact_data` and `event_data` tables.

### All Available Query Keywords

* `SELECT`
* `FROM`
* `JOIN` (defaults to `INNER JOIN`)
* `INNER JOIN`
* `ON`
* `WHERE`

### Query Operators for \[predicate]

* `[NOT] IN`
* `IS [NOT]`
* `AND`
* `OR`
* `NOT`
* `=`
* `>`
* `>=`
* `<`
* `<=`
* `!=`
* `+`
* `-`
* `*`
* `/`
* `%`

## Available Properties

### contact\_data(required)

| Column Name             | Data Type                                                                                                                          |
| ----------------------- | ---------------------------------------------------------------------------------------------------------------------------------- |
| CONTACT\_ID             | VARCHAR(36)                                                                                                                        |
| EMAIL                   | VARCHAR(254)                                                                                                                       |
| PHONE\_NUMBER\_ID       | VARCHAR                                                                                                                            |
| EXTERNAL\_ID            | VARCHAR(254)                                                                                                                       |
| ANONYMOUS\_ID           | VARCHAR(254)                                                                                                                       |
| FIRST\_NAME             | VARCHAR(50)                                                                                                                        |
| LAST\_NAME              | VARCHAR(50)                                                                                                                        |
| ALTERNATE\_EMAILS       | VARCHAR ARRAY                                                                                                                      |
| ADDRESS\_LINE\_1        | VARCHAR(100)                                                                                                                       |
| ADDRESS\_LINE\_2        | VARCHAR(100)                                                                                                                       |
| CITY                    | VARCHAR(60)                                                                                                                        |
| STATE\_PROVINCE\_REGION | VARCHAR(50)                                                                                                                        |
| POSTAL\_CODE            | VARCHAR(60)                                                                                                                        |
| COUNTRY                 | VARCHAR(55)                                                                                                                        |
| LIST\_IDS               | VARCHAR ARRAY                                                                                                                      |
| CREATED\_AT             | TIMESTAMP                                                                                                                          |
| UPDATED\_AT             | TIMESTAMP                                                                                                                          |
| EMAIL\_DOMAINS          | VARCHAR ARRAY                                                                                                                      |
| PHONE\_NUMBER           | VARCHAR(24)                                                                                                                        |
| WHATSAPP                | VARCHAR(254)                                                                                                                       |
| LINE                    | VARCHAR(254)                                                                                                                       |
| FACEBOOK                | VARCHAR(254)                                                                                                                       |
| UNIQUE\_NAME            | VARCHAR(254)                                                                                                                       |
| CUSTOM\_FIELDS \*       | Each custom field will appear as a column on the CONTACT table based on its name. Valid data types are: VARCHAR, NUMBER, TIMESTAMP |

### event\_data(optional)

Event data that can be optionally be used to enhanced segments

| Column name   | Data type                               |
| ------------- | --------------------------------------- |
| CONTACT\_ID   | VARCHAR(36)                             |
| EVENT\_SOURCE | VARCHAR(256)                            |
| EVENT\_TYPE   | VARCHAR(256)                            |
| TIMESTAMP     | TIMESTAMP                               |
| DATA          | JSON BLOB. Numeric and String type data |

### Possible Event Types

* **click** - Whenever a recipient clicks one of the Click Tracked links in your email. In the Email History, SendGrid displays the date, time, and the URL for the link that was clicked.
* **blocked** - When your IP address has been blocked by an ISP or messaging organization. Blocks are less severe than bounces and do not result in permanent suppressions: subsequent sends to blocked email addresses are not automatically suppressed.
* **bounce** - The receiving server could not or would not accept the message. If a recipient has previously unsubscribed from your emails, your attempt to send to them is bounced.
* **deferred** - The recipient mail server asked SendGrid to stop sending emails so fast.
* **delivered** - The accepted response generated by the recipients' mail server.
* **dropped** - Twilio SendGrid will drop an email when the contact on that email is in one of your suppression groups, the recipient email previously bounced, or that recipient has marked your email as spam.
* **group\_resubscribe** - When a recipient resubscribes themselves to a suppression group.
* **group\_unsubscribe** - Whenever a recipient unsubscribes from a suppression group.
* **open** - The response generated by a recipient opening an email.
* **processed** - Requests from your website, application, or mail client via SMTP Relay or the API that SendGrid processed.
* **spamreport** - Whenever a recipient marks your email as spam and their mail server tells us about it.
* **unsubscribe** - Whenever a recipient unsubscribes from your emails.

### Event\_Data JSON Struct Example

> \[!NOTE]
>
> Combining more than one singlesend\_id and mc\_auto\_id/mc\_auto\_step\_id in a single statement using an AND clause is not allowed. In order to segment contacts that have events corresponding to more than one single sends and automations use the JOIN clause. See [Multi-Engagement Events](/docs/sendgrid/for-developers/sending-email/marketing-campaigns-v2-segmentation-query-reference#engagement-and-multi-engagement-examples) for examples.

```sql
{
  "payload": {
      "unique_args": {
        "mc_auto_id": "02bc41ca-07f9-11eb-9c1f-422f9e0f60d1",
        "mc_auto_step_id": "0478e207-07f9-11eb-9c1f-422f9e0f60d1",
        "singlesend_id": "b1ff0108-0994-11eb-915a-7ab0feb0ce95",
      },
   },
}
```

## Supported Functions and examples

### current\_timestamp

**Example query**

`current_timestamp`

The following query will return all contacts that were created before this run of the segment:

```sql
SELECT contact_id, updated_at
FROM contact_data
WHERE created_at < CURRENT_TIMESTAMP
```

### timestampadd(interval, count, timestamp)

`timestampadd` allows you to specify a time interval, a number of those intervals as a count, and a timestamp that you can use to compare Date fields against.

**Parameters**

| Parameter | Supported values                                                                                                                                |
| --------- | ----------------------------------------------------------------------------------------------------------------------------------------------- |
| interval  | `year`, `quarter`, `month`, `day`, `hour`, `minute`, `second`                                                                                   |
| count     | any integer (negative or positive)                                                                                                              |
| timestamp | A supported date/time format ([RFC3339](https://datatracker.ietf.org/doc/html/rfc3339)), an existing date field, or a custom field of type Date |

**Example Query**

`timestampadd(interval, count, timestamp)`

The following query will return all contacts that were created before one year prior to 2020-10-15 6PM GMT:

```sql
SELECT contact_id, updated_at 
FROM contact_data 
WHERE created_at > timestampadd(day, 1, '2020-10-15T18:00:12Z')
```

### array\_contains(field\_name, array)

`array_contains` checks for at least one of the values provided in an array. It does not hard check for the presence of all the values or an exact match of the values given. It also doesn't support pattern-matches like '`%gmail.com%`' as array values. `array_contains` will instead check for the exact string, including pattern match characters. For example, when searching '`%gmail.com%`' the `%` characters are considered part of the string literal.

**Parameters**

| Parameter   | Supported values                                                                                      |
| ----------- | ----------------------------------------------------------------------------------------------------- |
| field\_name | `"alternate_emails"`, `"list_ids"`, `"email_domains"`                                                 |
| array       | alternate emails, list IDs, and email domains that correspond to the specified field\_name as strings |

**Example Query**

`array_contains(field_name, array)`

The following query will return all contacts with email domains equal to gmail.com or yahoo.com:

```sql
SELECT contact_id, updated_at 
FROM contact_data 
WHERE array_contains(email_domains, ['gmail.com', 'yahoo.com'])
```

### contains\_word(word\_value, field\_name)

`contains_word` is a custom function provided by the Marketing Campaigns V2 Segments API. `contains_word` accepts two arguments: a `word_value` that will be searched for in the `field_name` values stored for each contact.

The `field_name` you specify must be of type Text, and the value stored in `field_name` must be a comma-separated string. For example, if the function evaluates the `word_value` `alice` against a `first_name` field where the value is `bob,alice,sue`, it will find `alice` and return the contact. However, if the `first_name` value is stored as `bobalicesue`, `bob.alice.sue`, or any other non comma separated string, `alice` will not be found, and the contact will not be returned. This is important when thinking about how to enter the string values associated with your contacts. The check is case sensitive.

* All special characters and spaces within the comma separated string will be included when looking for a match. For Example, a field with a value of `bob,(alice),sue` and a match value of `(alice)` will return a contact
* Single quotes in a field value must be escaped in the match value using double single quotes. For Example, a field value of `bo'b,alice` will require a match value of `bo''b`.

**Parameters**

| Parameter   | Supported values                                                                                      |
| ----------- | ----------------------------------------------------------------------------------------------------- |
| word\_value | A string                                                                                              |
| field\_name | Any String type fields in your contact data or custom fields. Stored strings must be comma separated. |

**Example Query**

The following query will return all contacts where `first_name` contains the word `bob` :

```sql
SELECT contact_id, updated_at 
FROM contact_data 
WHERE contains_word('bob', first_name)
```

## Sample Queries for Different Kinds of Segments

### Segmentation Examples From All Contacts

> \[!NOTE]
>
> Segmentation on a list is supported by providing a list ID value to the optional `parent_list_ids` field in the v2 API request. This parameter currently accepts only one list ID. Segmentation of contacts from multiple list ids is supported through the use of `array_contains` function. See example below.

All contacts:

```sql
SELECT contact_id, updated_at 
FROM contact_data
```

All contacts with `first_name` 'Dave':

```sql
SELECT contact_id, updated_at 
FROM contact_data 
WHERE first_name = 'Dave'
```

All contacts where `state_province_region` is 'Colorado':

```sql
SELECT contact_id, updated_at 
FROM contact_data 
WHERE state_province_region = 'CO'
```

All contacts with primary email with the substring 'gmail.com':

```sql
SELECT contact_id, updated_at 
FROM contact_data 
WHERE email like '%gmail.com%'
```

All contacts with a text type custom field `my_text_custom_field` value 'abc':

```sql
SELECT contact_id, updated_at 
FROM contact_data 
WHERE my_text_custom_field = 'abc'
```

All contacts with primary email with 'gmail.com' as domain name, and a text type custom field `my_custom_field` value 'abc':

```sql
SELECT contact_id, updated_at 
FROM contact_data 
WHERE email like '%gmail.com' and my_custom_field = 'abc'
```

All contacts with a number type custom field `my_number_custom_field` value 12:

```sql
SELECT contact_id, updated_at 
FROM contact_data 
WHERE my_number_custom_field = 12
```

All contacts with a date type custom field `my_date_custom_field` value \`2021-01-01T12:46:24Z':

```sql
SELECT contact_id, updated_at 
FROM contact_data 
WHERE my_date_custom_field = '2021-01-01T12:46:24Z'
```

All contacts where alternate email is equal to '[alternate@example.com](mailto:alternate@example.com)':

```sql
SELECT contact_id, updated_at 
FROM contact_data 
WHERE array_contains(alternate_emails, ['alternate@example.com'])
```

All contacts where alternate email is equal to '[alternate@example.com](mailto:alternate@example.com)' or '[alternate2@example.com](mailto:alternate2@example.com)':

```sql
SELECT contact_id, updated_at 
FROM contact_data 
WHERE array_contains(alternate_emails, ['alternate@example.com','alternate2@example.com'])
```

All contacts present in a specific list "list\_id":

```sql
SELECT contact_id, updated_at 
FROM contact_data 
WHERE array_contains(list_ids, ['02bc41ca-07f9-11eb-9c1f-422f9e0f60d1'])
```

All contacts present in either of the list\_ids:

```sql
SELECT contact_id, updated_at 
FROM contact_data 
WHERE array_contains(list_ids, ['02bc41ca-07f9-11eb-9c1f-422f9e0f60d1', '02bc41ca-07f9-11eb-9c1f-422f9e0f62e4', '042a8e48-6e31-11eb-a8bc-7656c249c550'])
```

All contacts with specific email domain(s) `gmail.com`:

```sql
SELECT contact_id, updated_at 
FROM contact_data 
WHERE array_contains(email_domains, ['gmail.com'])
```

All contacts where `created_at` is after 2021-01-01 12 PM GMT:

```sql
SELECT contact_id, updated_at 
FROM contact_data 
WHERE created_at > '2021-01-01T12:00:00Z'
```

All contacts where `created_at` is equal to 2021-01-01 12 PM GMT:

```sql
SELECT contact_id, updated_at 
FROM contact_data 
WHERE created_at = '2021-01-01T12:00:12Z'
```

All contacts with `external id` that starts with '123':

```sql
SELECT contact_id, updated_at
FROM contact_data
WHERE external_id like '123%'
```

### NULL Values

When a contact's reserved fields are not set during creation, they default to a NULL value. Not setting a value for custom fields when creating a contact will save the contact without those custom fields. A segment query using these fields does not return contacts having NULL as a value. Additional conditions may be used to specify the field `is null` so that contacts having a null value will be included in the segment.

All contacts where `first_name` does not equal `Dave`, also including contacts without a value for `first_name`:

```sql
SELECT contact_id, updated_at 
FROM contact_data 
WHERE first_name != 'Dave' OR first_name is null
```

All contacts where `my_custom_field` does not contain `abc`, also including contacts without a value for `my_custom_field`:

```sql
SELECT contact_id, updated_at 
FROM contact_data 
WHERE my_custom_field like '%abc%' OR my_custom_field is null
```

### JOIN Types

Only `JOIN` and `INNER JOIN` are allowed and `INNER JOIN` will be internally converted to `JOIN`. `LEFT JOIN` and `RIGHT JOIN` are restricted for performance, and the same functionality can be achieved using `JOIN`. The examples below show how `LEFT JOIN` / `RIGHT JOIN` can be replaced with `JOIN` and `UNION`.

The following queries return all contacts that have an event "delivered" for a particular Single Send or whose state is Colorado. While both queries return the same set of contacts, using `JOIN` and `UNION` is more performant than using `LEFT JOIN`.

**LEFT JOIN (not supported)**

```sql
SELECT c.contact_id, c.updated_at 
FROM contact_data c
LEFT JOIN event_data e 
ON c.contact_id = e.contact_id
WHERE e.event_source = 'mail'
AND e.event_type = 'delivered' 
AND e.DATA:payload.unique_args.singlesend_id = '042a8e48-6e31-11eb-a8bc-7656c249c550'
OR c.state_province_region = 'CO'
```

**JOIN and UNION**

`UNION` can be used instead of "OR" to return contacts based on distinct criteria that requires use of a `JOIN`.

For example, a query for contacts that were 'delivered' a Single Send OR the contacts whose `state_province_region` is in "CO" can be written as follows:

```sql
SELECT c.contact_id, c.updated_at 
FROM contact_data c
JOIN event_data e 
ON c.contact_id = e.contact_id
WHERE e.event_source = 'mail'
AND e.event_type = 'delivered'
AND e.DATA:payload.unique_args.singlesend_id = '042a8e48-6e31-11eb-a8bc-7656c249c550'
UNION 
SELECT contact_id, updated_at 
FROM contact_data 
WHERE c.state_province_region = 'CO'
```

### Engagement and Multi-engagement Examples

All contacts that have at least one event ( i.e. an attempt was made to send them an email):

```sql
SELECT c.contact_id, c.updated_at 
FROM contact_data c 
JOIN event_data e ON c.contact_id = e.contact_id
```

All contacts that have opened emails from two different single sends:

```sql
SELECT e2.contact_id, c2.updated_at
FROM event_data e2
INNER JOIN (
 SELECT c1.contact_id, c1.updated_at
 FROM contact_data AS c1
 INNER JOIN event_data e1 ON c1.contact_id = e1.contact_id
 WHERE e1.event_source = 'mail'
   AND e1.event_type = 'open'
   AND e1.DATA:payload.unique_args.singlesend_id = '02bc41ca-07f9-11eb-9c1f-422f9e0f60d1'
) AS c2 ON c2.contact_id = e2.contact_id
WHERE e2.event_source = 'mail' 
 AND e2.event_type = 'open'
 AND e2.DATA:payload.unique_args.singlesend_id = '00163f67-7211-4363-ab4e-12dd6f313b3a'
```

All contacts that have opened ANY single sends within 3 days:

```sql
SELECT c.contact_id, c.updated_at
FROM contact_data as c
JOIN event_data as e on c.contact_id = e.contact_id
WHERE e.event_source = 'mail'
    AND e.event_type = 'open'
    AND e.DATA:payload.unique_args.singlesend_id is not null
    AND e.timestamp >= timestampadd(day,  -3,  current_timestamp())
```

All contacts that have both of the two events - click and open:

```sql
SELECT e2.contact_id, c2.updated_at
FROM event_data e2
INNER JOIN (
 SELECT c1.contact_id, c1.updated_at
 FROM contact_data AS c1
 INNER JOIN event_data e1 ON c1.contact_id = e1.contact_id
 WHERE e1.event_source = 'mail' AND e1.event_type = 'open'
) AS c2 ON c2.contact_id = e2.contact_id
WHERE e2.event_source = 'mail' AND e2.event_type = 'click'
```

All contacts that have both of the two events - bounce and deferred for a particular automation:

```sql
SELECT e2.contact_id, c2.updated_at
FROM event_data e2
INNER JOIN (
 SELECT c1.contact_id, c1.updated_at
 FROM contact_data AS c1
 INNER JOIN event_data e1 ON c1.contact_id = e1.contact_id
 WHERE e1.event_source = 'mail' 
  AND e1.event_type = 'bounce'
  AND e1.DATA:payload.unique_args.mc_auto_id = '02bc41ca-07f9-11eb-9c1f-422f9e0f60d1'
) AS c2 ON c2.contact_id = e2.contact_id
WHERE e2.event_source = 'mail' 
 AND e2.event_type = 'deferred'
 AND e2.DATA:payload.unique_args.mc_auto_id = '02bc41ca-07f9-11eb-9c1f-422f9e0f60d1'
```

### Not Event Examples

All contacts that have no event data (i.e. all contacts with where there has been no attempt to send an email to):

```sql
SELECT c.contact_id, c.updated_at
FROM contact_data c
WHERE c.contact_id NOT IN (
 SELECT e.contact_id
 FROM event_data e
)
```

All contacts that have not opened any mail in the last two months worth of seconds from the time when the segment is run:

```sql
SELECT c.contact_id, c.updated_at
FROM contact_data c
WHERE c.contact_id NOT IN (
 SELECT e.contact_id
 FROM event_data e
 WHERE e.event_source = 'mail' 
   AND e.event_type = 'open'
   AND e.timestamp < timestampadd(MONTH, -2, CURRENT_TIMESTAMP)
)

```

All contacts that have been 'delivered' the ANY single sends but have not 'open' ANY single sends within 1 month:

```sql
SELECT c.contact_id, c.updated_at
FROM contact_data c
JOIN event_data AS e1 ON c.contact_id = e1.contact_id
 WHERE e1.event_source = 'mail' 
 AND e1.event_type = 'delivered'
 AND e1.DATA:payload.unique_args.singlesend_id is not null
 AND c.contact_id NOT IN (
   SELECT e2.contact_id
   FROM event_data AS e2
   WHERE e2.event_source = 'mail' 
     AND e2.event_type = 'open'
     AND e2.DATA:payload.unique_args.singlesend_id is not null
     AND e2.timestamp >= timestampadd(month, -1, current_timestamp()))
 )
```

All contacts that have been 'delivered' the second email from automation but have not 'open' the email
(mc\_auto\_id = 'fa3e0f6e-d397-11eb-87ce-22ffc6ed50f5' mc\_auto\_step\_id = 'fc9ead9f-d397-11eb-92fe-02aa17a6534e'):

```sql
SELECT c.contact_id, c.updated_at
FROM contact_data c
JOIN event_data AS e1 ON c.contact_id = e1.contact_id
 WHERE e1.event_source = 'mail' 
 AND e1.event_type = 'delivered'
 AND e1.DATA:payload.unique_args.mc_auto_id = 'fa3e0f6e-d397-11eb-87ce-22ffc6ed50f5'
 AND e1.DATA:payload.unique_args.mc_auto_step_id = 'fc9ead9f-d397-11eb-92fe-02aa17a6534e'
 AND c.contact_id NOT IN (
   SELECT e2.contact_id
   FROM event_data AS e2
   WHERE e2.event_source = 'mail' 
     AND e2.event_type = 'open'
     AND e2.DATA:payload.unique_args.mc_auto_id = 'fa3e0f6e-d397-11eb-87ce-22ffc6ed50f5'
     AND e2.DATA:payload.unique_args.mc_auto_step_id = 'fc9ead9f-d397-11eb-92fe-02aa17a6534e'
 )
```

### Email Activity - Last Event Examples

There are three email activities that support last events: "Last Clicked", "Last Opened", and "Last Emailed". The last event queries are helpful to see the contacts whose last mail activity is in a certain period. The following queries are used to segment contacts based on their last mail activity.

All contacts that have not opened contacts since January of 2021 (last opened activity is before 2021):

```sql
SELECT c1.contact_id, c1.updated_at 
  FROM contact_data as c1 
  JOIN (
    SELECT e1.contact_id, e1.max_timestamp 
    FROM (
      SELECT e.contact_id, MAX(e.timestamp) max_timestamp 
      FROM event_data e 
      WHERE e.event_source = 'mail' and e.event_type = 'open' 
      GROUP BY e.contact_id
    ) as e1
    WHERE e1.max_timestamp < '2021-01-01T00:00:00.000Z'
  ) as e2 on c1.contact_id = e2.contact_id
```

All contacts that last clicked an email within 7 days (last clicked activity is within 7 days):

```sql
SELECT c1.contact_id, c1.updated_at 
  FROM contact_data as c1 
  JOIN (
    SELECT e1.contact_id, e1.max_timestamp 
    FROM (
      SELECT e.contact_id, MAX(e.timestamp) max_timestamp 
      FROM event_data e 
      WHERE e.event_source = 'mail' and e.event_type = 'click' 
      GROUP BY e.contact_id
    ) as e1
    WHERE e1.max_timestamp >= timestampadd(day, -7, current_timestamp())
  ) as e2 on c1.contact_id = e2.contact_id
```

All contacts that were last emailed in the past three months and last opened in the previous month (last emailed is within 3 months and last opened is within 1 month):

```sql
SELECT c1.contact_id, c1.updated_at 
FROM (
    SELECT c.contact_id, c.updated_at 
    FROM contact_data as c 
    JOIN (
        SELECT e1.contact_id, e1.max_timestamp 
        FROM (
            SELECT e.contact_id, MAX(e.timestamp) as max_timestamp 
            FROM event_data e 
            WHERE e.event_source = 'mail' 
             AND e.event_type = 'delivered' group by e.contact_id
        ) as e1 
    WHERE e1.max_timestamp >= timestampadd(month, -3, current_timestamp())
    ) as e2 on c.contact_id = e2.contact_id
) as c1 
JOIN (
    SELECT c.contact_id, c.updated_at 
    FROM contact_data as c 
    JOIN (
         SELECT e1.contact_id, e1.max_timestamp 
         FROM (
              SELECT e.contact_id, MAX(e.timestamp) as max_timestamp 
              FROM event_data e 
              WHERE e.event_source = 'mail' 
               AND e.event_type = 'open' group by e.contact_id
         ) as e1 
    WHERE e1.max_timestamp >= timestampadd(month, -1, current_timestamp())
    ) as e2 on c.contact_id = e2.contact_id
) as c11 on c1.contact_id = c11.contact_id

```

> \[!NOTE]
>
> **NOTE**: Even if the aggregate function MAX is supported along with GROUP BY, the usage is restricted to last event queries only. MAX function accepts only the "timestamp" field from event\_data and GROUP BY clause accepts only the "contact\_id" field. An alias name can be used only for the aggregate function MAX, but not for "contact\_id" or "updated\_at".

### JOINing More Than 2 Data Sets

Multiple data sets are allowed to be joined together if done in a specific manner even though SQL supports both JOINs expressed linearly and JOINs expressed using subqueries. Each data set's criteria must be listed alongside the corresponding table reference as it makes for better readability by having the queries nested explicitly by using subqueries. Following is an example of a segment query for both types. Here, the query defines a segment of all contacts that have the event of type 'processed' from the list of contacts which have the event of type 'delivered'.

#### Concatenated JOINs (not supported)

```sql
SELECT c1.contact_id, c1.updated_at FROM event_data AS e2
JOIN contact_data AS c1 ON e2.contact_id = c1.contact_id
JOIN event_data as e1 ON e1.contact_id = e2.contact_id
WHERE e1.event_source = 'mail' AND e1.event_type = 'delivered' AND e2.event_source = 'mail' AND e2.event_type = 'processed'
```

You can represent this logic in a more readable way using `JOIN`s with subqueries.

#### JOINs with subqueries

```sql
SELECT e2.contact_id, c2.updated_at
FROM event_data AS e2 JOIN (
    SELECT c1.contact_id, c1.updated_at
    FROM contact_data AS c1 JOIN event_data AS e1
    ON c1.contact_id = e1.contact_id
    WHERE e1.event_source = 'mail' AND e1.event_type = 'delivered') AS c2
ON c2.contact_id = e2.contact_id
WHERE  e2.event_source = 'mail' AND e2.event_type = 'processed'
```

> \[!NOTE]
>
> **NOTE**: There is no reduction in functionality as both generate the exact same result.

### Curl Usage

When creating or changing a segment query using curl command, escape any single quotes present in any parameter:

```sql
SELECT contact_id, updated_at
FROM contact_data 
WHERE first_name = '\''Dave'\''
```