Generate Optimized Redash SQL Queries and Dashboards

This skill writes optimized SQL queries and configures dashboards in Redash. It covers query structure and formatting with consistent aliases and explicit JOINs, Redash parameter configuration (date inputs, dropdowns, conditional Jinja-style blocks, multi-select with sqlstrings), and common dashboard patterns including KPI summary cards, time-series trend analysis with moving averages, and cohort retention analysis using window functions and CTEs. It also covers query performance optimization (indexed filtering, LIMIT for exploration, materialized views), visualization type selection (line, bar, pie, table, counter, map), and data quality check queries. Core principles carried through every query: avoid SELECT *, use explicit JOINs with proper WHERE clause ordering, design the query with its end visualization in mind, and build in data validation and null handling for robust reporting.

Use this skill when building or optimizing a Redash query and dashboard - writing a parameterized query with date-range and dropdown filters, building a KPI summary card query, computing a 7-day moving average with AVG() OVER, writing a cohort retention analysis with CTEs, optimizing a slow query with proper indexed filtering, or adding data-quality check queries (null rates, duplicate detection) to a dashboard. It also covers dashboard layout: placing key metrics at the top as counter visualizations, keeping consistent time periods across related charts, grouping related visualizations logically, using progressive disclosure (summary before detail), and showing data refresh timestamps and source information.

It does not cover other BI tools (Looker, Tableau) or the underlying database schema design - it is specific to Redash's query/parameter syntax and dashboard conventions.

Inputs are typically a business question and the underlying table schema. Outputs include parameterized SQL, for example a sales performance query:

SELECT
  DATE_TRUNC('{{period}}', order_date) AS period,
  product_category,
  COUNT(*) AS order_count,
  SUM(total_amount) AS revenue
FROM orders o
INNER JOIN products p ON o.product_id = p.id
WHERE order_date >= '{{start_date}}' AND order_date <= '{{end_date}}'
  {% if region %}
  AND shipping_region = '{{region}}'
  {% endif %}
GROUP BY 1, 2
ORDER BY period DESC, revenue DESC;

Other outputs include KPI summary card queries (revenue, growth rate), a moving-average time-series query using AVG() OVER (ROWS BETWEEN 6 PRECEDING AND CURRENT ROW), a cohort retention query with first_orders/monthly_activity CTEs, an optimized query using indexed date/status filters with LIMIT, and data-quality check queries reporting null and duplicate rates. It also carries collaboration and documentation practices for maintaining these queries over time: descriptive query names with purpose noted in comments, tags for discoverability, reusable query snippets for common patterns, documented parameter meanings and expected formats, version control for important queries and dashboard configs, appropriate refresh schedules based on data freshness needs, and alerts configured for critical metrics and thresholds.

Analysts and BI developers building Redash dashboards who need interactive, parameterized, performant queries rather than static, hard-coded SQL.