Helpful SQL Queries for Superset

This page contains helpful SQL queries for improving your Superset datasets and charts.

You can either use these to create a virtual dataset which will apply your query to the entire dataset, or you can use them in a single chart.

dica

Creating a single virtual dataset for the entire dataset with all of the columns you need is a recommended best practice. Doing so means you don't have to create charts for different virtual datasets, which may cause problems when configuring filters on a Superset dashboard.

info

These queries are written in SQL, which is a language for querying databases. If you are not familiar with SQL, you can learn more about it here. There is also a "linter" available for SQL that can help you troubleshoot your queries and ensure they are correct: SQLFluff.

Using LLMs to generate SQL queries

Tools like Claude, ChatGPT, GitHub Copilot, and similar assistants can help you draft or debug the SQL expressions on this page — especially when you need a one-off transform for a chart column and do not want to write the CASE / REPLACE / cast logic from scratch.

Give the model the column name(s), a short before → after example, what you want to happen, and that you are using it with PostgreSQL in Superset. Then paste the resulting expression into the chart metric, calculated column, or virtual dataset SQL Lab editor.

Mitigating risks

Do not paste confidential or personally identifiable data into a public LLM chat.

Column headers / field names are usually fine to share (for example "Age", "_submission_time", "Was_the_species_observed").
Real cell values often are not. If the transform depends on specific strings (choice codes, names, free text), use generalized or fake examples that match the shape of your data—not live submissions.
Prefer describing patterns (snake_case codes, YYYY-MM-DD text dates, 0/1 yes-no) over pasting rows from your warehouse.
If you must show value mapping, invent stand-ins ("choice_a" → "Label A") instead of real respondent answers.

Treat anything you type into an external model as potentially retained or visible to the provider. When in doubt, falsify the sample values.

Prompt for Superset chart expressions

Most chart-level helpers on this page are column expressions, not full queries: they reshape one field (or a few) and have no SELECT … FROM, no table names, and no trailing semicolon. Ask the model for that shape unless you are intentionally building a virtual dataset (joins, multi-column SELECT, geospatial splits).

You can paste the block below as the first part of a chat prompt, then add your specific ask. Or keep it in project instructions, a custom GPT/Claude project, or a skill so every request stays on-format.

You help write SQL expressions for Apache Superset charts (PostgreSQL dialect unless I say otherwise).

Rules for your output:
- Return ONLY a single SQL expression (or a short CASE/COALESCE/REGEXP block) that can be pasted into a Superset calculated column, metric, or custom SQL for one chart field.
- Do NOT write a full query: no SELECT, FROM, JOIN, WHERE, GROUP BY, ORDER BY, LIMIT, CTEs, or table/schema names—unless I explicitly ask for a virtual-dataset query.
- Prefer portable Postgres functions used in Superset (CASE, CAST, COALESCE, CONCAT, REPLACE, INITCAP, SUBSTRING, SPLIT_PART, TO_TIMESTAMP, REGEXP_REPLACE, etc.).
- Quote identifier names exactly as I give them (often Pascal_snake or with spaces), e.g. "Column_name".
- If mapping discrete codes to labels, use CASE or COALESCE; do not invent joins to label tables unless I ask.
- Show a tiny before → after table (fake/sample values only) so I can verify the logic.
- If my request is ambiguous, ask one clarifying question; otherwise give the expression first.

I will describe the column(s), desired output, and example values next.

Example follow-up after that preamble:

Column "how_many_meters_is_the_nest_from_bush" is text. Bucket into 0-1m, 1-2m, …, 5m and over, and Not recorded for blank or non-numeric. Give me the expression only.

Compare the result to the distance ranges recipe on this page. They should look similar.

Recipes

String Formatting and Manipulation

Remove underscore and capitalize first letter of the string

CONCAT(UPPER(SUBSTRING(REPLACE("Column_string", '_', ' ') FROM 1 FOR 1)),
       LOWER(SUBSTRING(REPLACE("Column_string", '_', ' ') FROM 2)))

Before	After
"example_string"	"Example string"

Capitalize first letter of each word and replace _ with a space

INITCAP(REPLACE("Column_string", '_', ' '))

Before	After
"example_string"	"Example String"

Unnest values based on regex, and then capitalize each first letter of each word

REGEXP_REPLACE(INITCAP(unnest(ARRAY_REMOVE(string_to_array(Column_string, ' '), ''))), '_', ' ', 'g')

Before	After
"example_string,another_example"	"Example_String", "Another_Example"

Combine the values of two fields, and then apply regex to replace _ with ", " and capitalize the first letter of each word

CASE
        WHEN "Which_ethnicity_does_the_person_identify_with" IN ('other', 'other, 1') THEN 'Other'
        ELSE REGEXP_REPLACE(INITCAP(REPLACE("Which_ethnicity_does_the_person_identify_with", '_', ', ')), ', ([a-z])', ', ' || UPPER(SUBSTRING('\1' FROM 1 FOR 1)) || SUBSTRING('\1' FROM 2))
    END

Before	After
"dutch_american"	"Dutch, American"
"german_french"	"German, French"
"other"	"Other"
"other, 1"	"Other"

Data Type Conversion

Convert a TEXT date field (with format 2019-02-24) into DATETIME

"date"::timestamp

Before	After
"2019-02-24"	"2019-02-24 00:00:00"
"2020-07-15"	"2020-07-15 00:00:00"
"2021-11-30"	"2021-11-30 00:00:00"

dica

Converting a text date into a TIMESTAMP is especially helpful when you want to:

Build a time-based line chart — for example, plotting date on the X axis and the number of records on the Y axis, such as the number of CoMapeo observations captured per month.
Add a Time Range filter to a dashboard — this filter requires every chart on the dashboard to have a TIMESTAMP field set as a filter.

It is a good idea to do this conversion before you start creating charts, as Superset will auto detect the presence of a TIMESTAMP field and add it to your filter options, so you can easily add a Time Range filter to your dashboard.

Cast a Kobo timestamp to YYYY-MM-DD

MAX(TO_TIMESTAMP(_submission_time, 'YYYY-MM-DD"T"HH24:MI:SS'))

_submission_time (Before)	_submission_time
2024-06-25T15:27:32	2024-06-05

Creating Categories and Ranges

Recast a string column as numeric, and use specific ranges for the values

CASE 
    WHEN CAST("Age" as INTEGER) BETWEEN 0 AND 9 THEN '0-10'
    WHEN CAST("Age" as INTEGER) BETWEEN 10 AND 19 THEN '10-20'
    WHEN CAST("Age" as INTEGER) BETWEEN 20 AND 29 THEN '20-30'
    WHEN CAST("Age" as INTEGER) BETWEEN 30 AND 39 THEN '30-40'
    WHEN CAST("Age" as INTEGER) BETWEEN 40 AND 49 THEN '40-50'
    WHEN CAST("Age" as INTEGER) BETWEEN 50 AND 59 THEN '50-60'
    WHEN CAST("Age" as INTEGER) BETWEEN 60 AND 69 THEN '60-70'
    WHEN CAST("Age" as INTEGER) BETWEEN 70 AND 79 THEN '70-80'
    WHEN CAST("Age" as INTEGER) >= 80 THEN '80 and over'
    ELSE 'Other'
END

Age (Before)	Age Range (After)
"5"	"0-10"
"15"	"10-20"
"27"	"20-30"
"31"	"30-40"
"44"	"40-50"
"58"	"50-60"
"65"	"60-70"
"79"	"70-80"
"85"	"80 and over"
"9000"	"80 and over"
"unknown"	"Other"

Recast a birthdate text field that is "YYYY-MM-DD" to only YYYY

CASE
    WHEN CAST(SUBSTRING("Birthdate", 1, 4) AS INTEGER) < 1940 THEN 'Before 1940'
    WHEN CAST(SUBSTRING("Birthdate", 1, 4) AS INTEGER) BETWEEN 1940 AND 1949 THEN '1940 - 1949'
    WHEN CAST(SUBSTRING("Birthdate", 1, 4) AS INTEGER) BETWEEN 1950 AND 1959 THEN '1950 - 1959'
    WHEN CAST(SUBSTRING("Birthdate", 1, 4) AS INTEGER) BETWEEN 1960 AND 1969 THEN '1960 - 1969'
    WHEN CAST(SUBSTRING("Birthdate", 1, 4) AS INTEGER) BETWEEN 1970 AND 1979 THEN '1970 - 1979'
    WHEN CAST(SUBSTRING("Birthdate", 1, 4) AS INTEGER) BETWEEN 1980 AND 1989 THEN '1980 - 1989'
    WHEN CAST(SUBSTRING("Birthdate", 1, 4) AS INTEGER) BETWEEN 1990 AND 1999 THEN '1990 - 1999'
    WHEN CAST(SUBSTRING("Birthdate", 1, 4) AS INTEGER) BETWEEN 2000 AND 2009 THEN '2000 - 2009'
    WHEN CAST(SUBSTRING("Birthdate", 1, 4) AS INTEGER) BETWEEN 2010 AND 2019 THEN '2010 - 2019'
    WHEN CAST(SUBSTRING("Birthdate", 1, 4) AS INTEGER) BETWEEN 2020 AND 2029 THEN '2020 - 2029'
    ELSE 'After 2029'
END

Birthdate (Before)	Birthyear (After)
"1935-06-15"	"Before 1940"
"1945-08-20"	"1940 - 1949"
"1955-12-01"	"1950 - 1959"
"1965-03-22"	"1960 - 1969"
"1975-07-30"	"1970 - 1979"
"1985-11-05"	"1980 - 1989"
"1995-04-18"	"1990 - 1999"
"2005-09-25"	"2000 - 2009"
"2015-02-14"	"2010 - 2019"
"2025-10-10"	"2020 - 2029"

Categorize distance based on meter ranges

CASE 
    WHEN "how_many_meters_is_the_nest_from_bush" = '' THEN 'Not recorded'
    WHEN CAST("how_many_meters_is_the_nest_from_bush" as REAL) BETWEEN 0 AND 0.9 THEN '0-1m'
    WHEN CAST("how_many_meters_is_the_nest_from_bush" as REAL) BETWEEN 1 AND 1.9 THEN '1-2m'
    WHEN CAST("how_many_meters_is_the_nest_from_bush" as REAL) BETWEEN 2 AND 2.9 THEN '2-3m'
    WHEN CAST("how_many_meters_is_the_nest_from_bush" as REAL) BETWEEN 3 AND 3.9 THEN '3-4m'
    WHEN CAST("how_many_meters_is_the_nest_from_bush" as REAL) BETWEEN 4 AND 4.9 THEN '4-5m'
    WHEN CAST("how_many_meters_is_the_nest_from_bush" as REAL) >= 5 THEN '5m and over'
    ELSE 'Not recorded'
END

Distance (Before)	Distance Range (After)
""	"Not recorded"
"0.5"	"0-1m"
"1.2"	"1-2m"
"2.8"	"2-3m"
"3.5"	"3-4m"
"4.7"	"4-5m"
"5.0"	"5m and over"
"6.3"	"5m and over"
"unknown"	"Not recorded"

Data Cleaning and Value Transformation

Convert 0 and 1 form responses to "No" and "Yes"

CASE
    WHEN "Is_the_person_a_member_of_the_community" = '0' THEN 'No'
    WHEN "Is_the_person_a_member_of_the_community" = '1' THEN 'Yes'
  END

Before	After
"0"	"No"
"1"	"Yes"

Convert gender form responses to strings in the Indigenous language, with "no answer" fallback

CASE
    WHEN "Gender" = 'man__eluwa' THEN 'Man (Eluwa)'
    WHEN "Gender" = 'vrouw__w_lei' THEN 'Vrouw (Wëlei)'
    ELSE 'No answer'
END

Before	After
"man__eluwa"	"Man (Eluwa)"
"vrouw__w_lei"	"Vrouw (Wëlei)"
NULL	"No answer"

Replace raw Kobo choice values with labels from the `__labels` table

Kobo submissions store raw choice codes (for example n_o instead of não). Each Kobo responses table has a companion <table_name>__labels lookup with the human-readable labels from the form definition. Join on both question_name and name so reused codes (such as shared 0–5 scales) resolve to the correct label for that question.

Prefer this over hardcoding CASE rewrites when the form already provides labels. See the KoboToolbox __labels docs for the table schema.

SELECT
    s.*,

    COALESCE(
        l.label,
        CAST(s."Was_the_species_observed" AS TEXT)
    ) AS "Was_the_species_observed__LABEL"

FROM my_kobo_form AS s

LEFT JOIN my_kobo_form__labels AS l
    ON l.question_name = 'Was_the_species_observed'
    AND l.name = CAST(s."Was_the_species_observed" AS TEXT)
    AND l.type = 'choices';

`Was_the_species_observed` (Before)	`Was_the_species_observed__LABEL` (After)
`"sim"`	`"sim"`
`"n_o"`	`"não"`
`NULL` / unmatched code	raw value (via `COALESCE`)

To label several choice columns at once, add one LEFT JOIN per question (each with its own alias). That keeps each lookup scoped to the right question_name:

SELECT
    s.*,

    COALESCE(
        obs.label,
        CAST(s."Was_the_species_observed" AS TEXT)
    ) AS "Was_the_species_observed__LABEL",

    COALESCE(
        threat.label,
        CAST(s."Main_threat_to_the_forest" AS TEXT)
    ) AS "Main_threat_to_the_forest__LABEL",

    COALESCE(
        condition.label,
        CAST(s."Forest_condition" AS TEXT)
    ) AS "Forest_condition__LABEL"

FROM my_kobo_form AS s

LEFT JOIN my_kobo_form__labels AS obs
    ON obs.question_name = 'Was_the_species_observed'
    AND obs.name = CAST(s."Was_the_species_observed" AS TEXT)
    AND obs.type = 'choices'
    AND obs.language = 'pt'

LEFT JOIN my_kobo_form__labels AS threat
    ON threat.question_name = 'Main_threat_to_the_forest'
    AND threat.name = CAST(s."Main_threat_to_the_forest" AS TEXT)
    AND threat.type = 'choices'
    AND threat.language = 'pt'

LEFT JOIN my_kobo_form__labels AS condition
    ON condition.question_name = 'Forest_condition'
    AND condition.name = CAST(s."Forest_condition" AS TEXT)
    AND condition.type = 'choices'
    AND condition.language = 'pt';

Column	Before	After
`Was_the_species_observed`	`"n_o"`	`"não"`
`Main_threat_to_the_forest`	`"logging"`	`"exploração madeireira"`
`Main_threat_to_the_forest`	`"fire"`	`"fogo"`
`Forest_condition`	`"degraded"`	`"degradada"`
`Forest_condition`	`"intact"`	`"intacta"`

dica

For multilingual forms, add AND l.language = 'pt' (or another language code) to each join so you pick one translation. Use this query as a virtual dataset if several charts need the labeled columns.

Set null values to "No answer"

COALESCE("Did_you_take_a_course", 'No answer')

Before	After
NULL	"No answer"

Geospatial Data Processing

Create a virtual database with latitude and longitude (for Mapbox Map chart)

For a field Record_your_current_location with values like "-1.234567 8.901234 124.4 15.899999618530273" (latitude, longitude, altitude, accuracy),

SELECT 
  CAST(SPLIT_PART("Record_your_current_location", ' ', 1) AS FLOAT) AS latitude,
  CAST(SPLIT_PART("Record_your_current_location", ' ', 2) AS FLOAT) AS longitude
FROM my_kobo_form

For a field g__coordinates with values like "[123.456, 78.901]" (longitude, latitude),

SELECT 
  CAST(SPLIT_PART("g__coordinates", '[', 2) AS FLOAT) AS latitude,
  CAST(SPLIT_PART("g__coordinates", ']', 1) AS FLOAT) AS longitude
FROM my_mapping_data

Using LLMs to generate SQL queries​

Mitigating risks​

Prompt for Superset chart expressions​

Recipes​

String Formatting and Manipulation​

Remove underscore and capitalize first letter of the string​

Capitalize first letter of each word and replace _ with a space​

Unnest values based on regex, and then capitalize each first letter of each word​

Combine the values of two fields, and then apply regex to replace _ with ", " and capitalize the first letter of each word​

Data Type Conversion​

Convert a TEXT date field (with format 2019-02-24) into DATETIME​

Cast a Kobo timestamp to YYYY-MM-DD​

Creating Categories and Ranges​

Recast a string column as numeric, and use specific ranges for the values​

Recast a birthdate text field that is "YYYY-MM-DD" to only YYYY​

Categorize distance based on meter ranges​

Data Cleaning and Value Transformation​

Convert 0 and 1 form responses to "No" and "Yes"​

Convert gender form responses to strings in the Indigenous language, with "no answer" fallback​

Replace raw Kobo choice values with labels from the __labels table​

Set null values to "No answer"​

Geospatial Data Processing​

Create a virtual database with latitude and longitude (for Mapbox Map chart)​

Using LLMs to generate SQL queries

Mitigating risks

Prompt for Superset chart expressions

Recipes

String Formatting and Manipulation

Remove underscore and capitalize first letter of the string

Capitalize first letter of each word and replace _ with a space

Unnest values based on regex, and then capitalize each first letter of each word

Combine the values of two fields, and then apply regex to replace _ with ", " and capitalize the first letter of each word

Data Type Conversion

Convert a TEXT date field (with format 2019-02-24) into DATETIME

Cast a Kobo timestamp to YYYY-MM-DD

Creating Categories and Ranges

Recast a string column as numeric, and use specific ranges for the values

Recast a birthdate text field that is "YYYY-MM-DD" to only YYYY

Categorize distance based on meter ranges

Data Cleaning and Value Transformation

Convert 0 and 1 form responses to "No" and "Yes"

Convert gender form responses to strings in the Indigenous language, with "no answer" fallback

Replace raw Kobo choice values with labels from the `__labels` table

Set null values to "No answer"

Geospatial Data Processing

Create a virtual database with latitude and longitude (for Mapbox Map chart)