Rules for writing SQL queries in the SQL editor in Sitecore CDP

You can write a single query with multiple statements, but you can only execute an individual query.

Use must use a blank line to separate statements, not a semicolon or forward slash.

Use a GROUP BY statement or DISTINCT statement to output unique guest references.

Write the query so after it runs, it results in a single column containing a unique list of guest references. A guest reference is represented by the guest_ref attribute.

Output a list of guest references - Use the GROUP BY statement to output a unique list of guest references from the meta_ref column, as shown in the following SQL query:

SELECT g.meta_ref as guest_ref
  FROM guests g
WHERE upper(g.type) = 'CUSTOMER'
GROUP BY g.meta_ref

Join matching values from the order entity - Use the INNER JOIN statement to combine rows that have matching values from the order entity to the guests entity, as shown in the following SQL query:

SELECT g.meta_ref as guest_ref
  FROM (
            SELECT meta_guest_ref 
            FROM orders
            WHERE core_price > 100
            ) o
INNER JOIN guests g
  ON o.meta_guest_ref = g.meta_ref
GROUP BY g.meta_ref

Join matching values from the sessions entity - Use the INNER JOIN statement to combine rows that have matching values from the sessions entity to the guests entity, as shown in the following SQL query:

SELECT g.meta_ref as guest_ref
  FROM (
            SELECT meta_guest_ref 
            FROM sessions
            WHERE upper(type) = 'WEB'
            ) s
INNER JOIN guests g
  ON s.meta_guest_ref = g.meta_ref
GROUP BY g.meta_ref

Join matching values from the events, sessions, and guests entities - Use the INNER JOIN statement to combine rows that have matching values from the events entity to the sessions entity. You must use the INNER JOIN statement again to combine the sessions entity with the g.meta_ref , as shown in the following SQL query:

SELECT g.meta_ref as guest_ref
  FROM (
            SELECT meta_session_ref 
            FROM events
            WHERE upper(type) = 'VIEW'
            ) e
INNER JOIN sessions s
ON e.meta_session_ref = s.meta_ref
INNER JOIN guests g
  ON s.meta_guest_ref = g.meta_ref
GROUP BY g.meta_ref

Join matching values from the order items, orders, and guests entities - Use the INNER JOIN statement to combine rows that have matching values from the order items entity to the orders entity. You must use the INNER JOIN statement again to combine the orders entity with the g.meta_ref , as shown in the following SQL query:

SELECT g.meta_ref as guest_ref
  FROM (
            SELECT meta_order_ref 
            FROM order_items
            WHERE date(core_ordered_at) > date('2021-01-01')
            ) oi
INNER JOIN orders o
ON oi.meta_order_ref = o.meta_ref
INNER JOIN guests g
  ON o.meta_guest_ref = g.meta_ref
GROUP BY g.meta_ref