Query Algebra Examples¶
How metrics build SQL through composable QueryFragment objects and Cube definitions.
Every example below runs without a database — to_sql() compiles the fragment into a PostgreSQL SQL string for inspection.
In [1]:
Copied!
import sqlparse
from rich.console import Console
from rich.syntax import Syntax
from tidemill.metrics.base import QuerySpec
from tidemill.metrics.churn.cubes import ChurnEventCube
from tidemill.metrics.mrr.cubes import MRRMovementCube, MRRSnapshotCube
from tidemill.metrics.retention.cubes import RetentionCohortCube
console = Console()
def show_sql(fragment, model=None):
"""Pretty-print the compiled SQL for a query fragment."""
raw = fragment.to_sql(model)
formatted = sqlparse.format(raw, reindent=True, keyword_case="upper")
console.print(Syntax(formatted, "sql", theme="monokai", padding=1))
import sqlparse
from rich.console import Console
from rich.syntax import Syntax
from tidemill.metrics.base import QuerySpec
from tidemill.metrics.churn.cubes import ChurnEventCube
from tidemill.metrics.mrr.cubes import MRRMovementCube, MRRSnapshotCube
from tidemill.metrics.retention.cubes import RetentionCohortCube
console = Console()
def show_sql(fragment, model=None):
"""Pretty-print the compiled SQL for a query fragment."""
raw = fragment.to_sql(model)
formatted = sqlparse.format(raw, reindent=True, keyword_case="upper")
console.print(Syntax(formatted, "sql", theme="monokai", padding=1))
Cube introspection¶
Each Cube knows its available measures, dimensions, and time dimensions. The API uses this for validation and documentation.
In [2]:
Copied!
from rich.table import Table
m = MRRSnapshotCube
table = Table(title="MRRSnapshotCube", show_lines=True)
table.add_column("Category", style="bold cyan")
table.add_column("Available", style="green")
table.add_row("Measures", ", ".join(m.available_measures()))
table.add_row("Dimensions", ", ".join(m.available_dimensions()))
table.add_row("Time dimensions", ", ".join(m.available_time_dimensions()))
console.print(table)
from rich.table import Table
m = MRRSnapshotCube
table = Table(title="MRRSnapshotCube", show_lines=True)
table.add_column("Category", style="bold cyan")
table.add_column("Available", style="green")
table.add_row("Measures", ", ".join(m.available_measures()))
table.add_row("Dimensions", ", ".join(m.available_dimensions()))
table.add_row("Time dimensions", ", ".join(m.available_time_dimensions()))
console.print(table)
MRRSnapshotCube ┏━━━━━━━━━━━━━━━━━┳━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━┓ ┃ Category ┃ Available ┃ ┡━━━━━━━━━━━━━━━━━╇━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━┩ │ Measures │ count, customer_count, mrr, mrr_original │ ├─────────────────┼───────────────────────────────────────────────────────────────────────────────────────────────┤ │ Dimensions │ billing_scheme, cancel_at_period_end, collection_method, currency, customer_country, │ │ │ customer_name, plan_id, plan_interval, plan_name, product_name, source_id, usage_type │ ├─────────────────┼───────────────────────────────────────────────────────────────────────────────────────────────┤ │ Time dimensions │ snapshot_at │ └─────────────────┴───────────────────────────────────────────────────────────────────────────────────────────────┘
MRR: plain aggregate¶
The simplest query — total MRR across all active subscriptions. No joins, no GROUP BY.
In [3]:
Copied!
m = MRRSnapshotCube
q = m.measures.mrr + m.where("s.mrr_base_cents", ">", 0)
show_sql(q, m)
m = MRRSnapshotCube
q = m.measures.mrr + m.where("s.mrr_base_cents", ">", 0)
show_sql(q, m)
SELECT sum(s.mrr_base_cents) AS mrr FROM metric_mrr_snapshot AS s WHERE s.mrr_base_cents > 0
MRR: filter by plan interval¶
Filtering on plan_interval automatically joins subscription and plan (since plan depends on subscription). No GROUP BY because we're filtering, not dimensioning.
In [4]:
Copied!
m = MRRSnapshotCube
q = m.measures.mrr + m.where("s.mrr_base_cents", ">", 0) + m.filter("plan_interval", "=", "yearly")
show_sql(q, m)
m = MRRSnapshotCube
q = m.measures.mrr + m.where("s.mrr_base_cents", ">", 0) + m.filter("plan_interval", "=", "yearly")
show_sql(q, m)
SELECT sum(s.mrr_base_cents) AS mrr FROM metric_mrr_snapshot AS s JOIN subscription AS sub ON sub.source_id = s.source_id AND sub.external_id = s.subscription_id JOIN PLAN AS p ON p.id = sub.plan_id WHERE s.mrr_base_cents > 0 AND p.interval = 'yearly'
MRR: dimensional cut¶
Group MRR by plan interval and customer country. Each dimension adds its required joins and a GROUP BY column.
In [5]:
Copied!
m = MRRSnapshotCube
q = (
m.measures.mrr
+ m.where("s.mrr_base_cents", ">", 0)
+ m.dimension("plan_interval")
+ m.dimension("customer_country")
)
show_sql(q, m)
m = MRRSnapshotCube
q = (
m.measures.mrr
+ m.where("s.mrr_base_cents", ">", 0)
+ m.dimension("plan_interval")
+ m.dimension("customer_country")
)
show_sql(q, m)
SELECT p.interval AS plan_interval, c.country AS customer_country, sum(s.mrr_base_cents) AS mrr FROM metric_mrr_snapshot AS s JOIN customer AS c ON c.source_id = s.source_id AND c.external_id = s.customer_id JOIN subscription AS sub ON sub.source_id = s.source_id AND sub.external_id = s.subscription_id JOIN PLAN AS p ON p.id = sub.plan_id WHERE s.mrr_base_cents > 0 GROUP BY p.interval, c.country
MRR: time series with monthly granularity¶
Time grain adds DATE_TRUNC to both SELECT and GROUP BY. Combined here with a date range filter on movements.
In [6]:
Copied!
mm = MRRMovementCube
q = (
mm.measures.amount
+ mm.time_grain("occurred_at", "month")
+ mm.filter("occurred_at", "between", ("2025-01-01", "2025-12-31"))
)
show_sql(q, mm)
mm = MRRMovementCube
q = (
mm.measures.amount
+ mm.time_grain("occurred_at", "month")
+ mm.filter("occurred_at", "between", ("2025-01-01", "2025-12-31"))
)
show_sql(q, mm)
SELECT date_trunc('month', m.occurred_at) AS period, sum(m.amount_base_cents) AS amount_base FROM metric_mrr_movement AS m WHERE m.occurred_at BETWEEN '2025-01-01' AND '2025-12-31' GROUP BY date_trunc('month', m.occurred_at)
MRR: movement breakdown by type and plan¶
Breakdown query: sum MRR movements grouped by movement type (new, expansion, contraction, churn) and plan. Demonstrates multiple dimensions from different joins.
In [7]:
Copied!
mm = MRRMovementCube
q = (
mm.measures.amount
+ mm.dimension("movement_type")
+ mm.dimension("plan_interval")
+ mm.filter("occurred_at", "between", ("2025-01-01", "2025-06-30"))
)
show_sql(q, mm)
mm = MRRMovementCube
q = (
mm.measures.amount
+ mm.dimension("movement_type")
+ mm.dimension("plan_interval")
+ mm.filter("occurred_at", "between", ("2025-01-01", "2025-06-30"))
)
show_sql(q, mm)
SELECT m.movement_type AS movement_type, p.interval AS plan_interval, sum(m.amount_base_cents) AS amount_base FROM metric_mrr_movement AS m JOIN subscription AS sub ON sub.source_id = m.source_id AND sub.external_id = m.subscription_id JOIN PLAN AS p ON p.id = sub.plan_id WHERE m.occurred_at BETWEEN '2025-01-01' AND '2025-06-30' GROUP BY m.movement_type, p.interval
Churn: logo churn count by country¶
Churn events filtered by type, grouped by customer country. The customer join is added automatically.
In [8]:
Copied!
ce = ChurnEventCube
q = (
ce.measures.count
+ ce.filter("churn_type", "=", "logo")
+ ce.filter("occurred_at", "between", ("2025-01-01", "2025-03-31"))
+ ce.dimension("customer_country")
)
show_sql(q, ce)
ce = ChurnEventCube
q = (
ce.measures.count
+ ce.filter("churn_type", "=", "logo")
+ ce.filter("occurred_at", "between", ("2025-01-01", "2025-03-31"))
+ ce.dimension("customer_country")
)
show_sql(q, ce)
SELECT c.country AS customer_country, count(*) AS churn_count FROM metric_churn_event AS ce JOIN customer AS c ON c.source_id = ce.source_id AND c.external_id = ce.customer_id WHERE ce.churn_type = 'logo' AND ce.occurred_at BETWEEN '2025-01-01' AND '2025-03-31' GROUP BY c.country
Retention: cohort matrix¶
Two measures (cohort size and active count) grouped by cohort month and active month, filtered by cohort range. The activity join is pulled in by the active_month dimension.
In [9]:
Copied!
rc = RetentionCohortCube
q = (
rc.measures.cohort_size
+ rc.measures.active_count
+ rc.dimension("cohort_month")
+ rc.dimension("active_month")
+ rc.filter("cohort_month_time", "between", ("2025-01-01", "2025-06-01"))
)
show_sql(q, rc)
rc = RetentionCohortCube
q = (
rc.measures.cohort_size
+ rc.measures.active_count
+ rc.dimension("cohort_month")
+ rc.dimension("active_month")
+ rc.filter("cohort_month_time", "between", ("2025-01-01", "2025-06-01"))
)
show_sql(q, rc)
SELECT rc.cohort_month AS cohort_month, ra.active_month AS active_month, count(distinct(rc.customer_id)) AS cohort_size, count(distinct(ra.customer_id)) AS active_count FROM metric_retention_cohort AS rc JOIN metric_retention_activity AS ra ON ra.customer_id = rc.customer_id AND ra.source_id = rc.source_id WHERE rc.cohort_month BETWEEN '2025-01-01' AND '2025-06-01' GROUP BY rc.cohort_month, ra.active_month
QuerySpec: API-driven query¶
QuerySpec is what the REST API receives. The model's apply_spec() validates dimension/filter names and translates the spec into composed fragments.
In [10]:
Copied!
# This is what an API request like:
# POST /api/metrics/mrr {"dimensions": ["plan_interval"], "filters": {"customer_country": "US"},
# "granularity": "month"}
# translates to internally:
spec = QuerySpec(
dimensions=["plan_interval"],
filters={"customer_country": "US"},
granularity="month",
)
m = MRRSnapshotCube
q = m.measures.mrr + m.where("s.mrr_base_cents", ">", 0) + m.apply_spec(spec)
show_sql(q, m)
# This is what an API request like:
# POST /api/metrics/mrr {"dimensions": ["plan_interval"], "filters": {"customer_country": "US"},
# "granularity": "month"}
# translates to internally:
spec = QuerySpec(
dimensions=["plan_interval"],
filters={"customer_country": "US"},
granularity="month",
)
m = MRRSnapshotCube
q = m.measures.mrr + m.where("s.mrr_base_cents", ">", 0) + m.apply_spec(spec)
show_sql(q, m)
SELECT date_trunc('month', s.snapshot_at) AS period, p.interval AS plan_interval, sum(s.mrr_base_cents) AS mrr FROM metric_mrr_snapshot AS s JOIN customer AS c ON c.source_id = s.source_id AND c.external_id = s.customer_id JOIN subscription AS sub ON sub.source_id = s.source_id AND sub.external_id = s.subscription_id JOIN PLAN AS p ON p.id = sub.plan_id WHERE s.mrr_base_cents > 0 AND c.country = 'US' GROUP BY date_trunc('month', s.snapshot_at), p.interval
Fragment composition¶
Fragments are immutable and compose freely. You can store common patterns as named fragments and reuse them.
In [11]:
Copied!
m = MRRSnapshotCube
# Reusable fragments
ACTIVE_ONLY = m.where("s.mrr_base_cents", ">", 0)
MONTHLY_PLANS = m.filter("plan_interval", "=", "monthly")
BY_COUNTRY = m.dimension("customer_country")
# Compose: total MRR for monthly plans by country
q = m.measures.mrr + ACTIVE_ONLY + MONTHLY_PLANS + BY_COUNTRY
show_sql(q, m)
# Compose differently: subscription count for monthly plans (no country split)
q2 = m.measures.count + ACTIVE_ONLY + MONTHLY_PLANS
show_sql(q2, m)
m = MRRSnapshotCube
# Reusable fragments
ACTIVE_ONLY = m.where("s.mrr_base_cents", ">", 0)
MONTHLY_PLANS = m.filter("plan_interval", "=", "monthly")
BY_COUNTRY = m.dimension("customer_country")
# Compose: total MRR for monthly plans by country
q = m.measures.mrr + ACTIVE_ONLY + MONTHLY_PLANS + BY_COUNTRY
show_sql(q, m)
# Compose differently: subscription count for monthly plans (no country split)
q2 = m.measures.count + ACTIVE_ONLY + MONTHLY_PLANS
show_sql(q2, m)
SELECT c.country AS customer_country, sum(s.mrr_base_cents) AS mrr FROM metric_mrr_snapshot AS s JOIN customer AS c ON c.source_id = s.source_id AND c.external_id = s.customer_id JOIN subscription AS sub ON sub.source_id = s.source_id AND sub.external_id = s.subscription_id JOIN PLAN AS p ON p.id = sub.plan_id WHERE s.mrr_base_cents > 0 AND p.interval = 'monthly' GROUP BY c.country
SELECT count(distinct(s.subscription_id)) AS subscription_count FROM metric_mrr_snapshot AS s JOIN subscription AS sub ON sub.source_id = s.source_id AND sub.external_id = s.subscription_id JOIN PLAN AS p ON p.id = sub.plan_id WHERE s.mrr_base_cents > 0 AND p.interval = 'monthly'
In [ ]:
Copied!