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.

Click on Home —> Datasets.
Click on the “+ Add New Dataset” button, and select “Query Datasets”.
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.
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;