Kottster Docs

Appearance

Raw SQL queries ​

Dashboard pages in Kottster support custom SQL queries to fetch and aggregate data directly from your database for stats and visualizations.

Stats ​

Stats display single numeric values. Your SQL query must return exactly one row with one column containing the metric value.

Basic queries ​

Examples of SQL queries for stats:

sql
SELECT COUNT(*) FROM orders
sql
SELECT COUNT(*) FROM orders
sql
SELECT COUNT(*) FROM orders
sql
SELECT COUNT(*) FROM orders

Date range filtering ​

When date range filtering is enabled, use :period_start_date and :period_end_date parameters to filter results dynamically. They come with the datetime in YYYY-MM-DD format. Example of a filtered query:

sql
SELECT COUNT(*)
FROM orders 
WHERE 
  created_at >= :period_start_date AND 
  created_at <= :period_end_date
sql
SELECT COUNT(*)
FROM orders 
WHERE 
  created_at >= :period_start_date AND 
  created_at <= :period_end_date
sql
SELECT COUNT(*)
FROM orders 
WHERE 
  created_at >= :period_start_date AND 
  created_at <= :period_end_date
sql
SELECT COUNT(*)
FROM orders 
WHERE 
  created_at >= :period_start_date AND 
  created_at <= :period_end_date

Line and area charts ​

Line and area charts require queries that return multiple rows with:

  • X-axis column: Typically a date/time column (e.g., date, created_at)
  • Y-axis column(s): Numeric values to plot (supports multiple columns for multi-series charts)

Examples of chart data queries:

sql
SELECT 
  DATE(created_at) as date, 
  COUNT(*) as order_count,
  SUM(total) as revenue
FROM orders
GROUP BY DATE(created_at) 
ORDER BY date
sql
SELECT 
  DATE(created_at) as date, 
  COUNT(*) as order_count,
  SUM(total) as revenue
FROM orders
GROUP BY DATE(created_at) 
ORDER BY date
sql
SELECT 
  DATE(created_at) as date, 
  COUNT(*) as order_count,
  SUM(total) as revenue
FROM orders
GROUP BY DATE(created_at) 
ORDER BY date
sql
SELECT 
  CAST(created_at AS DATE) as date, 
  COUNT(*) as order_count,
  SUM(total) as revenue
FROM orders
GROUP BY CAST(created_at AS DATE)
ORDER BY date

Date range filtering ​

When date range filtering is enabled, use :period_start_date and :period_end_date parameters to filter results dynamically. They come with the datetime in YYYY-MM-DD format. Example of a filtered query:

sql
SELECT 
  DATE(created_at) as date, 
  COUNT(*) as order_count,
  SUM(total) as revenue
FROM orders 
WHERE 
  created_at >= :period_start_date AND 
  created_at <= :period_end_date
GROUP BY DATE(created_at) 
ORDER BY date
sql
SELECT 
  DATE(created_at) as date, 
  COUNT(*) as order_count,
  SUM(total) as revenue
FROM orders 
WHERE 
  created_at >= :period_start_date AND 
  created_at <= :period_end_date
GROUP BY DATE(created_at) 
ORDER BY date
sql
SELECT 
  DATE(created_at) as date, 
  COUNT(*) as order_count,
  SUM(total) as revenue
FROM orders 
WHERE 
  created_at >= :period_start_date AND 
  created_at <= :period_end_date
GROUP BY DATE(created_at) 
ORDER BY date
sql
SELECT 
  CAST(created_at AS DATE) as date, 
  COUNT(*) as order_count,
  SUM(total) as revenue
FROM orders 
WHERE 
  created_at >= :period_start_date AND 
  created_at <= :period_end_date
GROUP BY CAST(created_at AS DATE)
ORDER BY date

Bar charts ​

Bar charts require queries that return multiple rows with:

  • X-axis column: Categorical data (e.g., status, category, region, product_type)
  • Y-axis column(s): Numeric values to plot (supports multiple columns for grouped bar charts)

Examples of bar chart data queries:

sql
SELECT 
  status as category,
  COUNT(*) as order_count,
  AVG(total) as avg_order_value
FROM orders
GROUP BY status
ORDER BY order_count DESC
sql
SELECT 
  status as category,
  COUNT(*) as order_count,
  AVG(total) as avg_order_value
FROM orders
GROUP BY status
ORDER BY order_count DESC
sql
SELECT 
  status as category,
  COUNT(*) as order_count,
  AVG(total) as avg_order_value
FROM orders
GROUP BY status
ORDER BY order_count DESC
sql
SELECT 
  status as category,
  COUNT(*) as order_count,
  AVG(total) as avg_order_value
FROM orders
GROUP BY status
ORDER BY order_count DESC

Date range filtering ​

When date range filtering is enabled, use :period_start_date and :period_end_date parameters to filter results dynamically. They come with the datetime in YYYY-MM-DD format. Example of a filtered query::

sql
SELECT 
  status as category,
  COUNT(*) as order_count,
  SUM(total) as total_revenue
FROM orders 
WHERE 
  created_at >= :period_start_date AND 
  created_at <= :period_end_date
GROUP BY status
ORDER BY order_count DESC
sql
SELECT 
  status as category,
  COUNT(*) as order_count,
  SUM(total) as total_revenue
FROM orders 
WHERE 
  created_at >= :period_start_date AND 
  created_at <= :period_end_date
GROUP BY status
ORDER BY order_count DESC
sql
SELECT 
  status as category,
  COUNT(*) as order_count,
  SUM(total) as total_revenue
FROM orders 
WHERE 
  created_at >= :period_start_date AND 
  created_at <= :period_end_date
GROUP BY status
ORDER BY order_count DESC
sql
SELECT 
  status as category,
  COUNT(*) as order_count,
  SUM(total) as total_revenue
FROM orders 
WHERE 
  created_at >= :period_start_date AND 
  created_at <= :period_end_date
GROUP BY status
ORDER BY order_count DESC