Query Datasets

Dataset Type - Query Datasets

You may create a new dataset by querying existing datasets. This feature is particularly useful if you would like to modify your exisitng data or join data from two datasets.

1. Click on Home —> Datasets.

2. Click on the “+ Add New Dataset” button, and select “Query Datasets”.

3. Enter the SQL query you would like to perform, and press the Run Query button. There are a few examples of queries to follow below.

4. Alternatively, you may use the built-in help function to generate a SQL query. Select which dataset you’d like to query, and write the question or command you are thinking of. Cast will generate a query based on your input.
   In this example, we selected Sample Contacts - Copy 19 as the dataset. Our command was, “List all the contacts with renewal authority.”

   Cast returned the query SELECT * FROM dataset_15067 WHERE renewal_authority = 1;
   

5. Check the Query Execution Preview. When you are satisfied, give your new dataset a name and press the Save button.

Example Queries

Clean up data

SELECT
    -- Replace with the columns from your dataset
    id,
    name,
    CASE
        WHEN gender = 'Male' THEN 'M'
        WHEN gender IS NULL THEN 'U'
        WHEN gender = '' THEN 'U'
        ELSE 'F'
    END AS gender_no_empty
FROM
    -- Replace with your dataset
    dataset_XXXXX as "Dataset_Name";

Rename columns

SELECT
    -- Replace with the columns to rename from your dataset
    "__internal.__id" as id
FROM
    -- Replace with your dataset
    dataset_XXXXX as "Dataset_Name";

Pivot dataset

SELECT
    category, -- get the category column
    SUM(CASE WHEN quarter = 'Q1' THEN amount ELSE 0 END) AS 'Q1', -- pivot Q1 sales
    SUM(CASE WHEN quarter = 'Q2' THEN amount ELSE 0 END) AS 'Q2', -- pivot Q2 sales
    SUM(CASE WHEN quarter = 'Q3' THEN amount ELSE 0 END) AS 'Q3', -- pivot Q3 sales
    SUM(CASE WHEN quarter = 'Q4' THEN amount ELSE 0 END) AS 'Q4' -- pivot Q4 sales
FROM
    -- Replace with your sales dataset
    dataset_XXXXX as "Sales_Dataset_Name"
GROUP BY
    category;

Join data from two datasets

-- Get the customer's name and the count of their support tickets
SELECT
    "Customers_Dataset".name,
    COUNT("Tickets_Dataset".id) as ticket_count
FROM
    -- Replace with your customers dataset
    dataset_XXXXX as "Customers_Dataset"
JOIN
    -- Replace with your tickets dataset
    dataset_XXXXX as "Tickets_Dataset"
ON
    "Customers_Dataset".id = "Tickets_Dataset".customer_id
GROUP BY
    "Customers_Dataset".id;