superset-sql-developer

name	superset-sql-developer
description	Expert guidance for writing optimized SQL queries for Apache Superset datasets, virtual datasets, and SQL Lab. This skill helps you create performant queries, design efficient datasets, and leverage PostgreSQL features - with specific patterns for Finance SSC, BIR compliance, and Odoo integration.
license	MIT

Superset SQL Developer Skill

Purpose

Expert guidance for writing optimized SQL queries for Apache Superset datasets, virtual datasets, and SQL Lab. This skill helps you create performant queries, design efficient datasets, and leverage PostgreSQL features - with specific patterns for Finance SSC, BIR compliance, and Odoo integration.

When to Use This Skill

• Creating new Superset datasets
• Writing SQL for charts and dashboards
• Optimizing slow queries
• Designing virtual datasets
• Building metrics and calculated columns
• Querying Odoo database via Supabase
• Implementing row-level security

SQL Development Workflow

1. Develop in SQL Lab

SQL Lab → Test Query → Verify Results → Save as Dataset

SQL Lab Best Practices:

• Start with LIMIT 10 to test quickly
• Use EXPLAIN ANALYZE to check performance
• Save query history for reuse
• Add comments to document logic

2. Create Dataset

Datasets → + Dataset → SQL Query or Table

Dataset Types:

• Physical Table: Direct table reference (fastest)
• Virtual Dataset: SQL query (flexible)
• Jinja Template: Dynamic SQL (advanced)

3. Define Metrics

Dataset → Edit → Metrics Tab → + Add Metric

Metric Examples:

-- Sum of amounts
SUM(amount)

-- Count distinct
COUNT(DISTINCT agency_id)

-- Percentage
SUM(CASE WHEN status = 'Completed' THEN 1 ELSE 0 END) * 100.0 / COUNT(*)

-- Average with filter
AVG(CASE WHEN processing_time_seconds > 0 THEN processing_time_seconds END)

---

Query Optimization Fundamentals

Performance Targets

Fast Query:    < 1 second
Good Query:    1-5 seconds
Acceptable:    5-10 seconds
Needs Work:    10-30 seconds
Too Slow:      > 30 seconds (optimize or async)

Query Optimization Checklist

1. Use WHERE Clauses Wisely

-- ✅ GOOD: Filter on indexed columns
SELECT * FROM bir_filing_tracker
WHERE agency_id = 5
  AND filing_date >= '2025-10-01'

-- ❌ BAD: No filters, scans entire table
SELECT * FROM bir_filing_tracker

-- ❌ BAD: Function on column prevents index use
SELECT * FROM bir_filing_tracker
WHERE EXTRACT(YEAR FROM filing_date) = 2025

-- ✅ GOOD: Filter allows index use
SELECT * FROM bir_filing_tracker
WHERE filing_date >= '2025-01-01'
  AND filing_date < '2026-01-01'

2. Optimize Aggregations

-- ✅ GOOD: Group by indexed columns
SELECT 
  agency_id,
  COUNT(*) as filing_count
FROM bir_filing_tracker
WHERE filing_date >= '2025-01-01'
GROUP BY agency_id

-- ❌ BAD: Group by non-indexed computed value
SELECT 
  TO_CHAR(filing_date, 'Month YYYY'),
  COUNT(*)
FROM bir_filing_tracker
GROUP BY TO_CHAR(filing_date, 'Month YYYY')

-- ✅ BETTER: Group by date-truncated column (can be indexed)
SELECT 
  DATE_TRUNC('month', filing_date) as month,
  COUNT(*)
FROM bir_filing_tracker
GROUP BY DATE_TRUNC('month', filing_date)

3. Join Efficiently

-- ✅ GOOD: Join on indexed foreign keys
SELECT 
  t.task_name,
  a.agency_name,
  COUNT(*) as count
FROM month_end_closing_tasks t
INNER JOIN agencies a ON t.agency_id = a.agency_id
WHERE t.closing_period = '2025-10'
GROUP BY t.task_name, a.agency_name

-- ❌ BAD: Cartesian product (missing join condition)
SELECT t.task_name, a.agency_name
FROM month_end_closing_tasks t, agencies a

-- ⚠️ CAUTION: LEFT JOIN might be slower
-- Only use if you need NULL results
SELECT t.*, a.agency_name
FROM month_end_closing_tasks t
LEFT JOIN agencies a ON t.agency_id = a.agency_id

4. Limit Result Sets

-- ✅ GOOD: Use LIMIT for large results
SELECT * FROM document_processing_logs
WHERE processed_at >= CURRENT_DATE
ORDER BY processed_at DESC
LIMIT 1000

-- ✅ GOOD: Use TOP N with window functions
SELECT * FROM (
  SELECT 
    *,
    ROW_NUMBER() OVER (PARTITION BY agency_id ORDER BY filing_date DESC) as rn
  FROM bir_filing_tracker
) sub
WHERE rn <= 10  -- Top 10 per agency

-- ❌ BAD: No limit, returns millions of rows
SELECT * FROM document_processing_logs

5. Use Appropriate Data Types

-- ✅ GOOD: Specific data types
SELECT 
  amount::NUMERIC(10,2),          -- For money
  filing_date::DATE,               -- For dates
  agency_id::INTEGER               -- For IDs

-- ❌ BAD: Casting everything as text
SELECT 
  amount::TEXT,
  filing_date::TEXT,
  agency_id::TEXT

---

PostgreSQL Optimization Features

1. Indexes

-- Check if index exists
SELECT indexname, indexdef
FROM pg_indexes
WHERE tablename = 'bir_filing_tracker';

-- Suggest indexes based on common queries
-- Index on frequently filtered columns
CREATE INDEX idx_bir_filing_agency_date 
ON bir_filing_tracker(agency_id, filing_date);

-- Index for status queries
CREATE INDEX idx_bir_filing_status 
ON bir_filing_tracker(filing_status)
WHERE filing_status IN ('Pending', 'Overdue');  -- Partial index

-- Composite index for joins
CREATE INDEX idx_closing_tasks_lookup
ON month_end_closing_tasks(agency_id, closing_period, task_status);

2. Materialized Views

-- Create materialized view for expensive aggregations
CREATE MATERIALIZED VIEW mv_bir_filing_summary AS
SELECT 
  agency_id,
  filing_period,
  form_type,
  COUNT(*) as total_filings,
  SUM(CASE WHEN filing_status = 'Completed' THEN 1 ELSE 0 END) as completed,
  SUM(CASE WHEN filing_status = 'Overdue' THEN 1 ELSE 0 END) as overdue,
  MAX(filing_date) as last_filing_date
FROM bir_filing_tracker
GROUP BY agency_id, filing_period, form_type;

-- Create indexes on materialized view
CREATE INDEX idx_mv_bir_agency ON mv_bir_filing_summary(agency_id);
CREATE INDEX idx_mv_bir_period ON mv_bir_filing_summary(filing_period);

-- Refresh strategy
REFRESH MATERIALIZED VIEW CONCURRENTLY mv_bir_filing_summary;
-- Schedule: Every 15 minutes or on-demand

3. Window Functions

-- Running totals
SELECT 
  filing_date,
  COUNT(*) as daily_count,
  SUM(COUNT(*)) OVER (ORDER BY filing_date) as cumulative_count
FROM bir_filing_tracker
WHERE filing_period = '2025-Q4'
GROUP BY filing_date
ORDER BY filing_date;

-- Ranking
SELECT 
  agency_name,
  completion_rate,
  RANK() OVER (ORDER BY completion_rate DESC) as ranking
FROM agency_performance;

-- Previous period comparison
SELECT 
  month,
  revenue,
  LAG(revenue, 1) OVER (ORDER BY month) as prev_month_revenue,
  revenue - LAG(revenue, 1) OVER (ORDER BY month) as month_over_month_change
FROM monthly_revenue;

4. Common Table Expressions (CTEs)

-- Break complex queries into readable steps
WITH completed_tasks AS (
  SELECT 
    agency_id,
    closing_period,
    COUNT(*) as completed_count
  FROM month_end_closing_tasks
  WHERE task_status = 'Done'
  GROUP BY agency_id, closing_period
),
total_tasks AS (
  SELECT 
    agency_id,
    closing_period,
    COUNT(*) as total_count
  FROM month_end_closing_tasks
  GROUP BY agency_id, closing_period
)
SELECT 
  a.agency_name,
  c.closing_period,
  c.completed_count,
  t.total_count,
  (c.completed_count::FLOAT / t.total_count::FLOAT * 100) as completion_percentage
FROM completed_tasks c
JOIN total_tasks t ON c.agency_id = t.agency_id AND c.closing_period = t.closing_period
JOIN agencies a ON c.agency_id = a.agency_id
ORDER BY completion_percentage DESC;

---

Finance SSC SQL Patterns

Pattern 1: BIR Filing Status Query

-- Virtual Dataset: BIR Filing Status Dashboard
SELECT 
  bft.filing_id,
  bft.filing_period,
  bft.form_type,
  bft.filing_date,
  bft.due_date,
  bft.filing_status,
  bft.filing_amount,
  a.agency_id,
  a.agency_code,
  a.agency_name,
  u.full_name as assignee_name,
  -- Calculated Fields
  CASE 
    WHEN bft.filing_status = 'Completed' THEN 'Completed'
    WHEN bft.filing_status = 'Pending' AND bft.due_date >= CURRENT_DATE THEN 'Pending'
    WHEN bft.filing_status = 'Pending' AND bft.due_date < CURRENT_DATE THEN 'Overdue'
    ELSE bft.filing_status
  END as display_status,
  -- Days variance
  CASE 
    WHEN bft.filing_date IS NOT NULL 
    THEN DATE_PART('day', bft.filing_date - bft.due_date)
    ELSE DATE_PART('day', CURRENT_DATE - bft.due_date)
  END as days_variance,
  -- Quarter helper
  'Q' || DATE_PART('quarter', bft.filing_date) || ' ' || DATE_PART('year', bft.filing_date) as filing_quarter
FROM bir_filing_tracker bft
JOIN agencies a ON bft.agency_id = a.agency_id
LEFT JOIN users u ON bft.assignee_id = u.user_id
WHERE bft.filing_date >= '2024-01-01'  -- Last 2 years
ORDER BY bft.filing_date DESC;

Metrics to Define:

Total Filings: COUNT(*)
Completed: SUM(CASE WHEN filing_status = 'Completed' THEN 1 ELSE 0 END)
Overdue: SUM(CASE WHEN display_status = 'Overdue' THEN 1 ELSE 0 END)
Completion Rate: SUM(CASE WHEN filing_status = 'Completed' THEN 1 ELSE 0 END) * 100.0 / COUNT(*)
Total Amount Filed: SUM(filing_amount)

Pattern 2: Month-End Closing Progress

-- Virtual Dataset: Month-End Closing Tracker
SELECT 
  mect.task_id,
  mect.closing_period,
  mect.task_category,
  mect.task_name,
  mect.task_description,
  mect.due_date,
  mect.completion_date,
  mect.task_status,
  mect.priority_level,
  a.agency_id,
  a.agency_code,
  a.agency_name,
  u.full_name as assignee_name,
  -- Calculated Fields
  EXTRACT(EPOCH FROM (mect.completion_date - mect.due_date)) / 86400 as days_variance,
  CASE 
    WHEN mect.task_status = 'Done' THEN 'Completed'
    WHEN mect.task_status IN ('In Progress', 'Started') THEN 'In Progress'
    WHEN mect.due_date < CURRENT_DATE THEN 'Overdue'
    WHEN mect.due_date <= CURRENT_DATE + INTERVAL '3 days' THEN 'Due Soon'
    ELSE 'On Track'
  END as task_health,
  -- Aging
  CASE 
    WHEN mect.task_status != 'Done' 
    THEN DATE_PART('day', CURRENT_DATE - mect.due_date)
    ELSE NULL
  END as days_overdue
FROM month_end_closing_tasks mect
JOIN agencies a ON mect.agency_id = a.agency_id
LEFT JOIN users u ON mect.assignee_id = u.user_id
WHERE mect.closing_period >= TO_CHAR(CURRENT_DATE - INTERVAL '6 months', 'YYYY-MM')
ORDER BY mect.due_date;

Metrics to Define:

Total Tasks: COUNT(*)
Tasks Completed: SUM(CASE WHEN task_status = 'Done' THEN 1 ELSE 0 END)
Tasks Overdue: SUM(CASE WHEN task_health = 'Overdue' THEN 1 ELSE 0 END)
Completion Percentage: SUM(CASE WHEN task_status = 'Done' THEN 1 ELSE 0 END) * 100.0 / COUNT(*)
Avg Days to Complete: AVG(days_variance) FILTER (WHERE task_status = 'Done')

Pattern 3: InsightPulse AI Processing Metrics

-- Virtual Dataset: Document Processing Analytics
SELECT 
  dpl.log_id,
  dpl.document_id,
  dpl.document_type,
  dpl.processing_status,
  dpl.processed_at,
  dpl.processing_time_seconds,
  dpl.ocr_confidence_score,
  dpl.page_count,
  dpl.file_size_bytes,
  dpl.error_message,
  -- Calculated Fields
  DATE_TRUNC('hour', dpl.processed_at) as processing_hour,
  DATE_TRUNC('day', dpl.processed_at) as processing_date,
  CASE 
    WHEN dpl.processing_status = 'Completed' AND dpl.ocr_confidence_score >= 0.95 THEN 'High Quality'
    WHEN dpl.processing_status = 'Completed' AND dpl.ocr_confidence_score >= 0.85 THEN 'Good Quality'
    WHEN dpl.processing_status = 'Completed' THEN 'Review Needed'
    WHEN dpl.processing_status = 'Failed' THEN 'Failed'
    ELSE 'Processing'
  END as quality_status,
  -- Speed category
  CASE 
    WHEN dpl.processing_time_seconds <= 2 THEN 'Fast'
    WHEN dpl.processing_time_seconds <= 5 THEN 'Normal'
    ELSE 'Slow'
  END as processing_speed_category,
  -- File size category
  CASE 
    WHEN dpl.file_size_bytes < 1000000 THEN 'Small (<1MB)'
    WHEN dpl.file_size_bytes < 5000000 THEN 'Medium (1-5MB)'
    ELSE 'Large (>5MB)'
  END as file_size_category
FROM document_processing_logs dpl
WHERE dpl.processed_at >= CURRENT_DATE - INTERVAL '30 days'
ORDER BY dpl.processed_at DESC;

Metrics to Define:

Documents Processed: COUNT(*)
Success Rate: SUM(CASE WHEN processing_status = 'Completed' THEN 1 ELSE 0 END) * 100.0 / COUNT(*)
Avg Processing Time: AVG(processing_time_seconds)
Avg OCR Confidence: AVG(ocr_confidence_score) FILTER (WHERE ocr_confidence_score > 0)
Error Rate: SUM(CASE WHEN processing_status = 'Failed' THEN 1 ELSE 0 END) * 100.0 / COUNT(*)

---

Time-Series Query Patterns

Daily/Weekly/Monthly Aggregation

-- Pattern: Flexible time grain aggregation
SELECT 
  DATE_TRUNC('day', filing_date) as period,      -- Change to 'week', 'month', 'quarter'
  agency_id,
  COUNT(*) as filing_count,
  SUM(filing_amount) as total_amount
FROM bir_filing_tracker
WHERE filing_date >= '2025-01-01'
GROUP BY DATE_TRUNC('day', filing_date), agency_id
ORDER BY period, agency_id;

Period-over-Period Comparison