Versions Compared

Key

  • This line was added.
  • This line was removed.
  • Formatting was changed.

Table of Contents

OperatorDescription
equal toInclude profiles that are exactly equal to the selected value.
Not equal toInclude profiles that are not equal to the selected value.


Review the following Operator information to help you create accurate SQL code for your segment attribute conditions.

...

Code Block
linenumberstrue
select count(*) from (
select
a."cdp_customer_id"
from "cdp_audience_1249"."customers" a
where not coalesce(a."gender" = 'Male', false)
-- set session distributed_join = 'true'
) cs

...

Operator

...

Include Profiles where the selected attribute is

...

Example Use Case

...

SQL Example

...

equal to

...

exactly equal to the selected value

...

match all profiles in which the gender is Female

...

select count(*) from (
select
a."cdp_customer_id"
from "cdp_audience_1249"."customers" a
where a."gender" = 'Female'
-- set session distributed_join = 'true'
) cs

...

not equal to

...

not equal to the selected value

...

match all profiles in which gender is not Male

...

greater than

Use the greater than operator when you want to match all profiles that are greater than the specified value.

Example

Match all profiles with a likely_to_shop score greater than zero.

SQL Example

Code Block
linenumberstrue
select count(*) from (
select
a."cdp_customer_id"

...


from "cdp_audience_1249"."customers" a

...


where

...

 a."

...

td_predictive_score_177" > 0
-- set session distributed_join = 'true'

...


) cs

greater than or equal to

Use the greater than or equal to operator when you want to match all profiles that are greater or equal to the selected value.

Example

...

Match all profiles with a likely_to_shop score greater than OR equal to zero.

SQL Example

Code Block
linenumberstrue
select count(*) from (

...


select

...


a."cdp_customer_id"

...


from "cdp_audience_1249"."customers" a

...


where a."td_predictive_score_177" >= 0

...


-- set session distributed_join = 'true'

...


) cs

...

greater than or equal to

...

less than

Use the less than operator when you want to match all profiles that are less than the selected value.

...

Example

Match all profiles with a likely_to_shop score

...

less than 10.

SQL Example

Code Block
linenumberstrue
select count(*) from (

...


select

...


a."cdp_customer_id"

...


from "cdp_audience_1249"."customers" a

...


where a."td_predictive_score_177"

...

 < 10
-- set session distributed_join = 'true'

...


) cs

less than or equal to

Use the less than or equal to operator when you want to match all profiles that are less than or equal to the selected value.

...

Example

Match all profiles with a likely_to_shop score less

...

or equal to 10.

SQL Example

Code Block
linenumberstrue
select count(*) from (

...


select

...


a."cdp_customer_id"

...


from "cdp_audience_1249"."customers" a

...


where a."td_predictive_score_177" <= 10

...


-- set session distributed_join = 'true'

...


) cs

...










less than or equal to

less than or equal to the selected value

match all profiles with a likely_to_shop score less or equal to 10.

select count(*) from (
select
a."cdp_customer_id"
from "cdp_audience_1249"."customers" a
where a."td_predictive_score_177" <= 10
-- set session distributed_join = 'true'
) cs

contains

contains the selected value

match all profiles where the company contains Johnston LLC.


select count(*) from (
select
a."cdp_customer_id"
from "cdp_audience_6148"."customers" a
where position('Johnston LLC' in a."company") > 0
-- set session distributed_join = 'true'
) cs


starts with

begins with the selected value

match all profiles where the company begins with Johnston LLC.


select count(*) from (
select
a."cdp_customer_id"
from "cdp_audience_6148"."customers" a
where position('Johnston LLC' in a."company") = 1
-- set session distributed_join = 'true'
) cs


ends with

ends with the selected value

match all profiles where the company ends with Johnston LLC


select count(*) from (
select
a."cdp_customer_id"
from "cdp_audience_409"."customers" a
where substr(a."company", -length('Johnston LLC'), length('Johnston LLC')) = 'Johnston LLC'
-- set session distributed_join = 'true'
) cs


regex

matches based on a regular expressions pattern (regex). You must provide the regex pattern that works for your use case.

match all profiles where the job_title begins with 'Manager', based on the regex expression: ^Manager.


select count(*) from (
select
a."cdp_customer_id"
from "cdp_audience_409"."customers" a
where regexp_like(a."job_title", '^Manager')
-- set session distributed_join = 'true'
) cs


not regex

does NOT match based on a regular expressions pattern (regex). You must provide the regex pattern that works for your use case.

match all profiles where the company does not end with the word google based on the regex pattern google$.


select count(*) from (
select
a."cdp_customer_id"
from "cdp_audience_409"."customers" a
where not coalesce(regexp_like(a."company", 'google$'), false)
-- set session distributed_join = 'true'
) cs


is between

between two provided values

match all profiles with a likely_to_shop score between 10 and 20.

select count(*) from (
select
a."cdp_customer_id"
from "cdp_audience_409"."customers" a
where (a."likely_to_shop" between 10 and 20)
-- set session distributed_join = 'true'
) cs

does not contain

does not contain the selected value

include all profiles where the company does not contain Johnston LLC.

select count(*) from (
select
a."cdp_customer_id"
from "cdp_audience_409"."customers" a
where not coalesce(position('Johnston LLC' in a."company") > 0
-- set session distributed_join = 'true'
) cs

does not start with

does not begin with the selected value

include all profiles where company does not start with Johnston LLC

select count(*) from (
select
a."cdp_customer_id"
from "cdp_audience_409"."customers" a
where not coalesce(position('Johnston LLC' in a."company") = 1
-- set session distributed_join = 'true'
) cs

does not end with

does not end with the selected value

include all profiles where the last_name does not end with Saint John.

select count(*) from (
select
a."cdp_customer_id"
from "cdp_audience_409"."customers" a
where not coalesce(substr(a."last_name", -length('Saint John'), length('Saint John')) = 'Saint John'
-- set session distributed_join = 'true'
) cs

is one of

one of the selected options

include all profiles where the job_title is either 'VP Marketing' or 'VP sales' or 'VP Product Management'.

select count(*) from (
select
a."cdp_customer_id"
from "cdp_audience_409"."customers" a
where a."job_title" in ('VP Marketing','VP sales','VP Product Management')
-- set session distributed_join = 'true'
) cs

is not one of

not one of the selected options

include all profiles where the job_title is NOT Teacher or Physical Therapist or Chemical Engineer.

select count(*) from (
select
a."cdp_customer_id"
from "cdp_audience_409"."customers" a
where not coalesce(a."job_title" in ('Teacher','Physical Therapist','Chemical Engineer'), false)
-- set session distributed_join = 'true'
) cs

is not between

not between the selected values

include all profiles where the likely_to_shop_score is not between 31 and 40.

select count(*) from (
select
a."cdp_customer_id"
from "cdp_audience_409"."customers" a
where not coalesce((a."likely_to_shop_score" between 31 and 40), false)
-- set session distributed_join = 'true'
) cs

exists

where the selected attribute exists

match all profiles that have an affinity_sub_category score.

select count(*) from (
select
a."cdp_customer_id"
from "cdp_audience_409"."customers" a
where a."affinity_sub_category" is not null
-- set session distributed_join = 'true'
) cs

does not exist

does not exist

match all profiles that do not have a td_ip_city_postal_code value.

select count(*) from (
select
a."cdp_customer_id"
from "cdp_audience_409"."customers" a
where a."td_ip_city_postal_code" is null
-- set session distributed_join = 'true'
) cs

does not exist or greater than

does not exist or is greater than a certain value

match all profiles that do not have a td_ip_city_postal_code_value or have a td_ip_city_postal_code_valuegreater than 94119.

select count(*) from (
select
a."cdp_customer_id"
from "cdp_audience_409"."customers" a
where not coalesce(a."td_ip_city_postal_code" <= '94119', false)
-- set session distributed_join = 'true'
) cs

does not exist or greater than or equal to

does not exist or is greater than or equal to a certain value

match all profiles that do not have a td_ip_city_postal_code_value or have a td_ip_city_postal_code_valuegreater than or equal to 94119

select count(*) from (
select
a."cdp_customer_id"
from "cdp_audience_409"."customers" a
where not coalesce(a."td_ip_city_postal_code" < '94119', false)
-- set session distributed_join = 'true'
) cs

does not exist or less than

does not exist or is less than a certain value

match all profiles that do not have a td_ip_city_postal_code_value or have a td_ip_city_postal_code_value less than 94119

select count(*) from (
select
a."cdp_customer_id"
from "cdp_audience_409"."customers" a
where not coalesce(a."td_ip_city_postal_code" >= '94119', false)
-- set session distributed_join = 'true'
) cs

does not exist or less than or equal to

does not exist or is less than or equal to a certain value

match all profiles that do not have a td_ip_city_postal_code_value or have a td_ip_city_postal_code_value less than or equal to 94119.

select count(*) from (
select
a."cdp_customer_id"
from "cdp_audience_409"."customers" a
where not coalesce(a."td_ip_city_postal_code" > '94119', false)
-- set session distributed_join = 'true'
) cs

...