Complex SQL Aggregation with Window Functions

Description

This query demonstrates an advanced SQL pattern that combines multiple JOIN operations, window functions, and GROUP BY aggregation to produce dashboard metrics. It showcases Stack9's capability to execute raw SQL queries directly against the database through the stack9_db connector, providing full control over complex data aggregation scenarios that would be difficult or impossible to achieve through ORM-style queries alone.

The query employs several sophisticated SQL techniques including a self-contained subquery with ROW_NUMBER() window function to identify the most recent workflow records for each entity, LEFT JOINs to handle optional relationships, and GROUP BY aggregation to count records per entity type. The window function pattern shown here (partitioning by entity and ordering by creation date) is a powerful technique for getting the "latest" record from a one-to-many relationship without complex correlated subqueries.

This pattern exemplifies how Stack9 handles workflow tracking across different entity types in a generic, extensible way. The query joins the workflow dashboard configuration with entity type metadata and the actual workflow history, producing counts that can be displayed on administrative dashboards. The careful use of JOIN conditions and WHERE clauses ensures proper filtering of soft-deleted records and active entities only.

Use Case

Used in administrative dashboards to display the count of records at each workflow step, helping managers understand bottlenecks and track progress across different business processes like sales orders, subscriptions, or customer service tickets.

Key Features

• Direct SQL execution through stack9_db connector for maximum flexibility
• ROW_NUMBER() window function to get latest records from history table
• Complex JOIN operations including self-joins and LEFT JOINs
• GROUP BY aggregation with COUNT() for metric calculation
• Proper handling of soft deletes and active flags throughout the query
• Generic entity type system allowing workflow tracking across different business objects

Query Type

COMPLEX

JSON Definition

{
  "key": "getworkflowdashboardcountofrecords",
  "name": "getWorkflowDashboardCountOfRecords",
  "connector": "stack9_db",
  "queryTemplate": {
    "query": "SELECT\n\tet.id AS entity_type_id,\n\tet.key AS entity_key,\n\tet.name AS entity_name,\n\tCOUNT(ew.id) AS number_of_records\nFROM\n\tworkflow_dashboard_setup wds\n\tINNER JOIN \"_entity_type\" et ON et.\"key\" = wds.entity_key AND et.\"_active\" IS TRUE AND et.has_workflow IS TRUE\n\tLEFT JOIN (\n\t\tSELECT\n\t\t\tid,\n\t\t\tentity_type_id,\n\t\t\tentity_id,\n\t\t\tworkflow_step,\n\t\t\tROW_NUMBER() OVER (PARTITION BY entity_type_id, entity_id ORDER BY \"_created_at\" DESC) AS rn\n\t\tFROM\n\t\t\t\"_entity_workflow\" ew\n\t\tWHERE\n\t\t\t1 = 1\n\t\t\tAND ew.\"_is_deleted\" IS FALSE\n\t\t\tAND ew.outcome IS NULL\n\t) AS ew ON ew.rn = 1 AND ew.entity_type_id = et.id AND ew.workflow_step = wds.workflow_step\nWHERE\n\t1 = 1\n\tAND wds.\"_is_deleted\" IS FALSE\n\tAND et.\"_is_deleted\" IS FALSE\nGROUP BY\n\tet.id,\n\tet.key,\n\tet.name"
  }
}

Notes

• The 1 = 1 pattern in WHERE clauses is a common SQL technique that makes it easier to add/remove conditions programmatically
• Window functions like ROW_NUMBER() are executed before the JOIN, allowing efficient filtering of the latest records
• The query uses quoted identifiers (e.g., "_entity_type") to handle PostgreSQL reserved words and case-sensitive names
• The LEFT JOIN ensures entity types with no workflow records still appear in results with a count of 0
• Performance consideration: The window function subquery should have appropriate indexes on (entity_type_id, entity_id, _created_at)
• This query pattern can be extended to include additional metrics or filtering by date ranges
• The generic entity_type system allows this single query to work across all workflow-enabled entities in the system