---
title: "Rules for writing SQL queries in the SQL editor in Sitecore CDP"
slug: "rules-for-writing-sql-queries-in-the-sql-editor-in-sitecore-cdp"
abstract: "Provides rules and examples for writing SQL queries in advanced mode (Sitecore CDP)."
---
# Rules for writing SQL queries in the SQL editor in Sitecore CDP

We recommend that you familiarize yourself with a number of rules before you start writing SQL queries in advanced mode.

Rules for [writing SQL queries](https://doc.sitecore.com/cdp/en/users/sitecore-cdp/introduction-to-batch-segmentation/using-advanced-mode-in-batch-segmentation-in-sitecore-cdp/write-an-sql-query-in-sitecore-cdp.html "Write an SQL query in Sitecore CDP") in the SQL editor:

- 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.

To ensure the most recent and accurate guest references are returned, you can use the following statements to join entities:

- 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
    ```