SQL Insights

Execute custom SQL queries for complete flexibility in data analysis and visualization.

Best for:

• Complex custom queries
• Ad-hoc analysis
• Joining multiple data sources
• Advanced calculations

Example use cases:

• "Calculate customer lifetime value with custom cohort logic"
• "Join product events with CRM data for sales analysis"
• "Build custom retention calculations with specific business rules"

Creating SQL Insights

Write DuckDB SQL to query your lakehouse. SQL insights give you complete flexibility to create custom calculations, join multiple data sources, and implement complex business logic. Use the SQL insight builder to write and test your queries, then save them as reusable insights.

Learn more about querying via API or connecting with SQL clients.

SQL Best Practices

1. Use DuckDB dialect: Altertable uses DuckDB SQL syntax
2. Qualify table names: Use connection.schema.table format
3. Filter early: Apply WHERE clauses before joins and aggregations
4. Limit results: Add LIMIT for large datasets
5. Test with query_lakehouse: Preview results before creating insights

Combining Data Sources

SQL insights can query across multiple connections in a single query. Join product analytics events with business data from your Postgres databases, data warehouses, or other connected sources. The federated query engine automatically optimizes cross-database joins and handles query execution across different data sources.

Learn more about the SQL engine and DuckDB's capabilities.

Advanced SQL Techniques

SQL insights support all DuckDB SQL features, including:

• Window Functions: Calculate running totals, moving averages, rankings, and percentiles over time windows
• Common Table Expressions (CTEs): Break complex queries into readable, reusable parts
• Recursive Queries: Handle hierarchical data and complex relationships
• Array and JSON Functions: Work with nested data structures
• Time Series Functions: Generate date series and handle time-based calculations

These advanced features enable sophisticated analytics that go beyond simple aggregations.