Writing SQL queries

Dashboard Studio uses SQL to retrieve data from IoT Query schemas. You write SQL in two contexts: panel editors, where statements power visualizations, and the standalone SQL Editor for data exploration. This page explains how to write effective SQL for both contexts, with emphasis on visualization requirements since they have specific structural constraints.

Where SQL is used

Dashboard Studio provides two SQL environments for different purposes. Understanding when to use each helps you work more efficiently.

Visualization queries power individual panels in reports. You write these statements in the panel editor's SQL Query tab. Each panel runs one statement that must return data in a specific structure matching the visualization type. These statements execute when reports load or refresh, so performance matters for user experience. Visualization SQL cannot modify data; all statements run as read-only SELECT operations against IoT Query schemas.

SQL Editor supports data exploration and export. Access the SQL Editor from the left sidebar under Tools. Write any SELECT statement to examine data structure, validate assumptions, or export results as CSV. The SQL Editor shows full result tables with column sorting and provides execution metrics. Use this for testing logic before adding SQL to visualization panels, or for ad-hoc data extraction that doesn't need visualization.

The key difference: visualization SQL must match exact column structures, while SQL Editor statements can return any result format. Test complex logic in SQL Editor first, then adapt it for visualizations.

How to write SQL for visualizations

Visualization SQL must return specific column counts and data types. Dashboard Studio cannot render a bar chart from three columns or a stat tile from text data. Check the Dataset Requirements section in the SQL Query tab to see exactly what your chosen visualization expects before writing the statement. The table below contains supported visualization types:

Visualization	Query requirement	Example
Stat tile	Single numeric value	SELECT COUNT(*) FROM schema.table
Bar chart	Two columns: category, value	SELECT column1, COUNT(*) FROM schema.table GROUP BY column1
Pie chart	Two columns: label, value	SELECT category, SUM(value) FROM schema.table GROUP BY category
Table	Any columns	SELECT column1, column2, column3 FROM schema.table
Text	No query required	Markdown content only

Stat tiles

Stat tiles display single numeric values. Statements must return exactly one row with one numeric column:

Total trips in current month

SELECT COUNT(*) as value
FROM silver.trips
WHERE start_time >= DATE_TRUNC('month', CURRENT_DATE);

Total distance traveled (km)

SELECT SUM(distance_km) as value
FROM silver.trips
WHERE start_time >= CURRENT_DATE - INTERVAL '7 days';

The column name doesn't matter, only that the result is a single numeric value. Dashboard Studio displays this value with formatting you configure in Visualization Settings.

Bar charts

Bar charts require exactly two columns: category (text or date) and value (numeric). The first column becomes the X-axis, the second becomes bar heights:

Trips per vehicle type

SELECT 
  vehicle_type as category,
  COUNT(*) as value
FROM silver.trips
WHERE start_time >= DATE_TRUNC('month', CURRENT_DATE)
GROUP BY vehicle_type
ORDER BY value DESC;

Daily trip counts

SELECT 
  DATE_TRUNC('day', start_time)::date as category,
  COUNT(*) as value
FROM silver.trips
WHERE start_time >= CURRENT_DATE - INTERVAL '30 days'
GROUP BY DATE_TRUNC('day', start_time)
ORDER BY category;

Use ORDER BY to control the bar sequence. Sort by value for ranked comparisons or by category for time-series progressions.

Pie charts

Pie charts require exactly two columns: label (text) and value (numeric). The first column becomes slice labels, the second determines slice sizes:

Trip distribution by zone

SELECT 
  zone_name as label,
  COUNT(*) as value
FROM silver.zone_visits
WHERE enter_time >= DATE_TRUNC('month', CURRENT_DATE)
GROUP BY zone_name
ORDER BY value DESC
LIMIT 10;

Add LIMIT clauses for categories with many values. Pie charts with 20+ slices become unreadable; limit to top 10-15 categories.

Tables

Tables accept any number of columns with any data types. Select the columns you want to display:

Recent trip details

SELECT 
  device_id,
  start_time,
  end_time,
  distance_km,
  duration_minutes,
  max_speed_kmh
FROM silver.trips
WHERE start_time >= CURRENT_DATE - INTERVAL '7 days'
ORDER BY start_time DESC
LIMIT 100;

Column names become table headers. Use aliases with spaces for readable headers: distance_km as "Distance (km)".

Text panels

Text panels display single text values or formatted strings. Statements must return one text column:

Last data update timestamp

SELECT 
  'Last updated: ' || MAX(record_added_at)::text as value
FROM bronze.tracking_data_core;

How to use global variables

Global variables provide reusable values across multiple SQL statements. Define variables in Settings > Configuration > Global Variables, then reference them using ${variable_name} syntax.

Define variables for values that change periodically but remain consistent across multiple panels: analysis date ranges, vehicle type filters, or threshold values. When these values change, update the variable definition once instead of editing individual SQL statements.

Using date range variables

SELECT 
  DATE_TRUNC('day', start_time)::date as category,
  COUNT(*) as value
FROM silver.trips
WHERE start_time >= '${analysis_start_date}'::date
  AND start_time < '${analysis_end_date}'::date
GROUP BY DATE_TRUNC('day', start_time)
ORDER BY category;

Variables store text values. Cast them to appropriate types in SQL: '${variable_name}'::date for dates, '${variable_name}'::integer for numbers.

For statement-specific parameters that change frequently, you can use CTE parameter blocks at the start:

WITH params AS (
  SELECT 
    5 as min_idle_minutes,
    10 as max_idle_speed_kmh,
    '${analysis_start_date}'::date as date_from,
    '${analysis_end_date}'::date as date_to
)

SELECT 
  device_id,
  COUNT(*) as idle_count,
  SUM(duration_minutes) as total_idle_minutes
FROM silver.idle_events e
CROSS JOIN params p
WHERE e.event_time >= p.date_from
  AND e.event_time < p.date_to
  AND e.speed_kmh <= p.max_idle_speed_kmh
  AND e.duration_minutes >= p.min_idle_minutes
GROUP BY device_id
ORDER BY total_idle_minutes DESC;

This pattern combines global variables (date ranges) with statement-specific parameters (thresholds), keeping all adjustable values at the top for easy maintenance.

How to access IoT Query schemas

IoT Query organizes data in Bronze, Silver, and Gold layers. Understanding which layer to use saves time and improves SQL clarity. For complete schema details, see the IoT Query Schema Overview.

Bronze layer contains raw tracking points from devices: bronze.tracking_data_core stores every GPS position with timestamps, coordinates, and sensor readings. Use Bronze for point-level analysis or when you need raw sensor values not processed into higher layers.

Silver layer provides processed entities: silver.trips aggregates tracking points into trip records with start/end times, distance, and duration. silver.zone_visits records when devices enter and exit geofences. silver.idle_events identifies periods when vehicles remain stationary with engines running. Use Silver for most visualization needs since it provides analysis-ready structures.

Gold layer offers pre-aggregated metrics and dimensional models for complex analytics. Use Gold for fleet-wide statistics or multi-dimensional analysis that would require complex joins against Silver tables.

Reference tables using schema.table format: silver.trips, not just trips. Include date range filters in WHERE clauses to limit data scanned:

Always filter by time ranges

SELECT device_id, COUNT(*) as trip_count
FROM silver.trips
WHERE start_time >= CURRENT_DATE - INTERVAL '30 days'
GROUP BY device_id;

Most SQL statements filter by device, time range, or both. Add these filters early in WHERE clauses to reduce data volume processed.

How to use the SQL Editor

Access SQL Editor from the left sidebar under Tools. Use it for three main purposes: testing logic before adding to panels, exploring data schemas to understand available columns, and exporting data that doesn't need visualization.

The SQL Editor supports multiple tabs for different statements. Write SQL in tabs, execute with the "Execute Query" button, and view results in the table below. Results show execution metrics (execution time, rows returned) and support column sorting for quick data examination.

Export results as CSV using the "Export CSV" button. This works for ad-hoc reports or data extracts for external analysis. The SQL Editor has no result row limit, unlike visualization SQL which should return focused datasets.

Test visualization SQL in the SQL Editor before adding to panels. Write the statement, verify it returns expected columns and data types, then copy it to the panel editor's SQL Query tab. This workflow catches structural issues before you configure visualization settings.

Exploration pattern for new data:

-- 1. Examine table structure
SELECT * FROM silver.trips LIMIT 10;

-- 2. Check date range coverage
SELECT 
  MIN(start_time) as earliest,
  MAX(start_time) as latest,
  COUNT(*) as total_trips
FROM silver.trips;

-- 3. Test filtering logic
SELECT 
  device_id,
  start_time,
  distance_km
FROM silver.trips
WHERE start_time >= '2024-01-01'
  AND device_id = 12345
ORDER BY start_time;

-- 4. Adapt for visualization (2 columns for bar chart)
SELECT 
  DATE_TRUNC('day', start_time)::date as day,
  COUNT(*) as trips
FROM silver.trips
WHERE start_time >= '2024-01-01'
  AND device_id = 12345
GROUP BY DATE_TRUNC('day', start_time)
ORDER BY day;

Common SQL patterns

Most visualization SQL follows similar patterns. Copy these structures and adjust filters, columns, and aggregations for your specific needs.

Time-series counts for tracking trends

SELECT 
  DATE_TRUNC('hour', start_time) as time_bucket,
  COUNT(*) as event_count
FROM silver.trips
WHERE start_time >= CURRENT_DATE - INTERVAL '24 hours'
GROUP BY DATE_TRUNC('hour', start_time)
ORDER BY time_bucket;

Category rankings for comparing groups

SELECT 
  category_column,
  COUNT(*) as count
FROM schema.table
WHERE filter_conditions
GROUP BY category_column
ORDER BY count DESC
LIMIT 15;

Metric calculations for aggregated statistics

SELECT 
  SUM(distance_km) as total_distance,
  AVG(duration_minutes) as avg_duration,
  COUNT(*) as trip_count
FROM silver.trips
WHERE start_time >= DATE_TRUNC('week', CURRENT_DATE);

Filtered summaries with multiple conditions

SELECT 
  device_id,
  COUNT(*) as trips,
  SUM(distance_km) as total_km
FROM silver.trips
WHERE start_time >= '${period_start}'::date
  AND start_time < '${period_end}'::date
  AND distance_km >= 5
  AND duration_minutes >= 10
GROUP BY device_id
HAVING COUNT(*) >= 5
ORDER BY total_km DESC;

What to do when SQL fails

Execution failures fall into three categories: structural mismatches with visualization requirements, SQL syntax errors, or filters that return no data.

Column structure mismatches

Occur when results don't match visualization expectations. If you selected a bar chart but your SQL returns three columns, Dashboard Studio cannot render it. Check Dataset Requirements in the SQL Query tab. The bar chart needs exactly two columns (category, value), so adjust your SELECT clause:

-- Wrong: three columns
SELECT device_id, start_time, COUNT(*) FROM silver.trips GROUP BY device_id, start_time;

-- Correct: two columns
SELECT device_id, COUNT(*) as trips FROM silver.trips GROUP BY device_id;

SQL syntax errors

Show specific error messages. Common issues include missing schema prefixes (trips instead of silver.trips), typos in column names, or incorrect date casting. Test statements in SQL Editor to see detailed error messages with line numbers.

Empty results

Despite successful execution indicate filters exclude all data. Test the SQL without WHERE clauses in SQL Editor to verify the table contains data, then add filters incrementally to identify which condition excludes your expected results.

Performance issues

If statements execute slowly or timeout, add date range filters to WHERE clauses. Operations scanning entire tables process millions of rows unnecessarily:

-- Slow: no date filter
SELECT device_id, COUNT(*) FROM silver.trips GROUP BY device_id;

-- Fast: date range filter
SELECT device_id, COUNT(*) 
FROM silver.trips 
WHERE start_time >= CURRENT_DATE - INTERVAL '30 days'
GROUP BY device_id;

For additional performance guidance, see How to access IoT Query schemas for best practices on filtering and schema selection.

Where to find SQL examples

The SQL Recipe Book provides complete examples for common telematic analyses. These recipes demonstrate patterns for trip analysis, zone visit calculations, idle detection, and fleet metrics. Each recipe includes the complete SQL statement, explanation of logic, and sample results.

Adapt Recipe Book examples for visualizations by adjusting the SELECT clause to match visualization requirements. A recipe that returns detailed trip records can become a bar chart by adding GROUP BY and COUNT aggregation. A statement calculating per-vehicle metrics can become a stat tile by adding SUM across all vehicles.

You just need to:

1. Copy examples from Recipe Book to the Dashboard Studio'sEditor.

2. Test with your actual data.

3. Verify results, then modify the SELECT clause for your target visualization.

The core WHERE and JOIN logic remains the same; you adjust only the output structure.

For schema details, see the IoT Query Schema Overview. This reference explains available tables, column definitions, and relationships between Bronze, Silver, and Gold layers.