metric = population + event + time window + grouping + aggregation + exclusions + freshness + owner

case SLA breach rate =
  population: closed standard-priority cases
  event: time from assignment to closure
  time window: cases closed in calendar month
  grouping: tenant, case_type, assigned_team
  aggregation: breaches / closed cases
  exclusions: test tenants, reopened cases under appeal
  freshness: complete through previous day
  owner: workflow platform team

What does one row represent at this stage of the query?

select
    c.case_type,
    count(*) as closed_cases
from case_file c
join case_note n on n.case_id = c.case_id
join case_event e on e.case_id = c.case_id
where c.status = 'CLOSED'
group by c.case_type;

with closed_cases as (
    select case_id, case_type
    from case_file
    where status = 'CLOSED'
)
select
    case_type,
    count(*) as closed_cases
from closed_cases
group by case_type;

with base_cases as (
    -- grain: one row per case
    select case_id, tenant_id, case_type, created_at, closed_at
    from case_file
    where status = 'CLOSED'
),
case_sla as (
    -- grain: one row per case with SLA duration
    select
        case_id,
        tenant_id,
        case_type,
        extract(epoch from (closed_at - created_at)) / 3600 as hours_to_close
    from base_cases
)
select
    case_type,
    count(*) as cases_closed,
    avg(hours_to_close) as avg_hours_to_close
from case_sla
group by case_type;

select
    count(*) filter (where breached) * 1.0 / count(*) as breach_rate
from case_file;

with eligible_cases as (
    select
        case_id,
        case_type,
        created_at,
        closed_at,
        sla_deadline_at,
        closed_at > sla_deadline_at as breached
    from case_file
    where status = 'CLOSED'
      and is_test = false
      and sla_deadline_at is not null
      and closed_at >= date '2026-06-01'
      and closed_at <  date '2026-07-01'
)
select
    count(*) filter (where breached) as breached_cases,
    count(*) as eligible_cases,
    count(*) filter (where breached) * 1.0 / nullif(count(*), 0) as breach_rate
from eligible_cases;

Breach rate is breaches among eligible closed cases in the reporting window.

When the business event actually happened.

When the system ingested or processed it.

created_at          -- case created in source system
received_at         -- event received by platform
processed_at        -- worker processed event
loaded_at           -- analytics warehouse loaded row

How many cases were closed in June?

where closed_at >= timestamp '2026-06-01 00:00:00'
  and closed_at <  timestamp '2026-07-01 00:00:00'

where created_at between ...

created_at >= :start_at
and created_at < :end_at

created_at between :start_at and :end_at

store timestamps in UTC or timestamp-with-time-zone semantics
convert to local calendar only at reporting boundary

case_created -> evidence_submitted -> reviewed -> approved -> closed

create table case_event (
    event_id bigint generated always as identity primary key,
    case_id bigint not null,
    event_type text not null,
    occurred_at timestamptz not null
);

with step_times as (
    select
        case_id,
        min(occurred_at) filter (where event_type = 'CASE_CREATED') as case_created_at,
        min(occurred_at) filter (where event_type = 'EVIDENCE_SUBMITTED') as evidence_submitted_at,
        min(occurred_at) filter (where event_type = 'REVIEWED') as reviewed_at,
        min(occurred_at) filter (where event_type = 'APPROVED') as approved_at,
        min(occurred_at) filter (where event_type = 'CLOSED') as closed_at
    from case_event
    group by case_id
)
select
    count(*) filter (where case_created_at is not null) as created,
    count(*) filter (where evidence_submitted_at is not null) as evidence_submitted,
    count(*) filter (where reviewed_at is not null) as reviewed,
    count(*) filter (where approved_at is not null) as approved,
    count(*) filter (where closed_at is not null) as closed
from step_times;

with funnel as (
    select
        count(*) filter (where case_created_at is not null) as created,
        count(*) filter (where evidence_submitted_at is not null) as submitted,
        count(*) filter (where reviewed_at is not null) as reviewed,
        count(*) filter (where approved_at is not null) as approved,
        count(*) filter (where closed_at is not null) as closed
    from step_times
)
select
    submitted * 1.0 / nullif(created, 0) as submit_rate,
    reviewed * 1.0 / nullif(submitted, 0) as review_rate,
    approved * 1.0 / nullif(reviewed, 0) as approval_rate,
    closed * 1.0 / nullif(approved, 0) as closure_rate
from funnel;

Of cases created each week, how many were closed within 7 days?

with case_cohort as (
    -- grain: one row per case
    select
        case_id,
        date_trunc('week', created_at) as created_week,
        created_at,
        closed_at
    from case_file
    where created_at >= date '2026-01-01'
      and is_test = false
),
case_result as (
    select
        case_id,
        created_week,
        closed_at is not null
          and closed_at < created_at + interval '7 days' as closed_within_7d
    from case_cohort
)
select
    created_week,
    count(*) as cases_created,
    count(*) filter (where closed_within_7d) as closed_within_7d,
    count(*) filter (where closed_within_7d) * 1.0 / nullif(count(*), 0) as closed_within_7d_rate
from case_result
group by created_week
order by created_week;

where created_at < now() - interval '7 days'

Does an entity come back or remain active after initial adoption?

with tenant_first_month as (
    select
        tenant_id,
        min(date_trunc('month', occurred_at)) as cohort_month
    from api_request_log
    where status_code between 200 and 299
    group by tenant_id
),
tenant_activity as (
    select distinct
        tenant_id,
        date_trunc('month', occurred_at) as activity_month
    from api_request_log
    where status_code between 200 and 299
),
retention as (
    select
        f.cohort_month,
        extract(year from age(a.activity_month, f.cohort_month)) * 12
          + extract(month from age(a.activity_month, f.cohort_month)) as month_number,
        count(*) as active_tenants
    from tenant_first_month f
    join tenant_activity a
      on a.tenant_id = f.tenant_id
     and a.activity_month >= f.cohort_month
    group by f.cohort_month, month_number
)
select *
from retention
order by cohort_month, month_number;

with cohort_size as (
    select cohort_month, count(*) as tenants
    from tenant_first_month
    group by cohort_month
)
select
    r.cohort_month,
    r.month_number,
    r.active_tenants,
    c.tenants as cohort_tenants,
    r.active_tenants * 1.0 / nullif(c.tenants, 0) as retention_rate
from retention r
join cohort_size c using (cohort_month)
order by r.cohort_month, r.month_number;

99.9% of API requests succeed over 30 days.
95% of standard cases close within 3 business days.
99% of events are processed within 5 minutes.

with requests as (
    select
        request_id,
        occurred_at,
        status_code,
        case
            when status_code between 200 and 399 then true
            else false
        end as successful
    from api_request_log
    where occurred_at >= now() - interval '30 days'
      and route not like '/internal/%'
)
select
    count(*) as total_requests,
    count(*) filter (where successful) as successful_requests,
    count(*) filter (where not successful) as failed_requests,
    count(*) filter (where successful) * 1.0 / nullif(count(*), 0) as availability
from requests;

What percentage of requests completed under 300ms?

select
    count(*) filter (where duration_ms <= 300) as good_events,
    count(*) as total_events,
    count(*) filter (where duration_ms <= 300) * 1.0 / nullif(count(*), 0) as sli
from api_request_log
where occurred_at >= now() - interval '30 days'
  and route = '/cases/search'
  and status_code between 200 and 399;

0.1% = 0.001

with windowed as (
    select
        count(*) as total,
        count(*) filter (where status_code >= 500) as errors
    from api_request_log
    where occurred_at >= now() - interval '1 hour'
      and route not like '/internal/%'
),
budget as (
    select
        total,
        errors,
        0.001 as allowed_error_ratio
    from windowed
)
select
    total,
    errors,
    errors * 1.0 / nullif(total, 0) as actual_error_ratio,
    (errors * 1.0 / nullif(total, 0)) / allowed_error_ratio as burn_rate
from budget;

with case_sla as (
    select
        case_id,
        tenant_id,
        case_type,
        assigned_team_id,
        assigned_at,
        closed_at,
        sla_deadline_at,
        closed_at <= sla_deadline_at as met_sla
    from case_file
    where status = 'CLOSED'
      and closed_at >= date '2026-06-01'
      and closed_at <  date '2026-07-01'
)
select
    assigned_team_id,
    case_type,
    count(*) as closed_cases,
    count(*) filter (where met_sla) as met_sla_cases,
    count(*) filter (where not met_sla) as breached_cases,
    count(*) filter (where met_sla) * 1.0 / nullif(count(*), 0) as sla_attainment
from case_sla
group by assigned_team_id, case_type
order by sla_attainment asc;

99 requests take 50 ms
1 request takes 10,000 ms
average = about 149.5 ms

select
    percentile_cont(0.50) within group (order by duration_ms) as p50_ms,
    percentile_cont(0.90) within group (order by duration_ms) as p90_ms,
    percentile_cont(0.95) within group (order by duration_ms) as p95_ms,
    percentile_cont(0.99) within group (order by duration_ms) as p99_ms
from api_request_log
where occurred_at >= now() - interval '1 day'
  and route = '/cases/search'
  and status_code between 200 and 399;

select
    route,
    percentile_cont(0.95) within group (order by duration_ms) as p95_ms,
    count(*) as requests
from api_request_log
where occurred_at >= now() - interval '1 day'
group by route
having count(*) >= 100
order by p95_ms desc;

select
    date_trunc('minute', occurred_at) as minute,
    count(*) as requests,
    count(*) filter (where status_code >= 500) as errors,
    count(*) filter (where status_code >= 500) * 1.0 / nullif(count(*), 0) as error_rate
from api_request_log
where occurred_at >= timestamp '2026-07-01 10:00:00'
  and occurred_at <  timestamp '2026-07-01 12:00:00'
group by date_trunc('minute', occurred_at)
order by minute;

select
    service_version,
    count(*) as requests,
    count(*) filter (where status_code >= 500) as errors,
    count(*) filter (where status_code >= 500) * 1.0 / nullif(count(*), 0) as error_rate
from api_request_log
where occurred_at >= timestamp '2026-07-01 10:00:00'
  and occurred_at <  timestamp '2026-07-01 12:00:00'
group by service_version
order by error_rate desc, requests desc;

select
    tenant_id,
    count(*) filter (where status_code >= 500) as errors,
    count(*) as total_requests,
    min(occurred_at) filter (where status_code >= 500) as first_error_at,
    max(occurred_at) filter (where status_code >= 500) as last_error_at
from api_request_log
where occurred_at >= :incident_start
  and occurred_at <  :incident_end
group by tenant_id
having count(*) filter (where status_code >= 500) > 0
order by errors desc;

Which business operations failed?

select
    operation_type,
    count(*) as failed_operations,
    count(distinct tenant_id) as affected_tenants,
    count(distinct case_id) as affected_cases
from operation_attempt
where attempted_at >= :incident_start
  and attempted_at <  :incident_end
  and outcome = 'FAILED'
group by operation_type
order by failed_operations desc;

with hourly_counts as (
    select
        date_trunc('hour', occurred_at) as hour,
        count(*) as requests
    from api_request_log
    where occurred_at >= now() - interval '14 days'
    group by date_trunc('hour', occurred_at)
),
baseline as (
    select
        extract(hour from hour) as hour_of_day,
        avg(requests) as avg_requests,
        stddev_samp(requests) as stddev_requests
    from hourly_counts
    where hour < date_trunc('hour', now())
    group by extract(hour from hour)
),
current_hour as (
    select
        extract(hour from date_trunc('hour', now())) as hour_of_day,
        count(*) as requests
    from api_request_log
    where occurred_at >= date_trunc('hour', now())
)
select
    c.requests,
    b.avg_requests,
    b.stddev_requests,
    (c.requests - b.avg_requests) / nullif(b.stddev_requests, 0) as z_score
from current_hour c
join baseline b using (hour_of_day);

select
    date_trunc('day', created_at) as day,
    count(*) as cases_created,
    count(*) filter (where assigned_team_id is null) as unassigned_cases,
    count(*) filter (where assigned_team_id is null) * 1.0 / nullif(count(*), 0) as unassigned_rate
from case_file
where created_at >= now() - interval '30 days'
group by date_trunc('day', created_at)
order by day;

with requests as (
    select
        case
            when occurred_at < timestamp '2026-06-15 10:00:00' then 'before'
            else 'after'
        end as period,
        duration_ms,
        status_code
    from api_request_log
    where occurred_at >= timestamp '2026-06-15 09:00:00'
      and occurred_at <  timestamp '2026-06-15 11:00:00'
      and route = '/cases/search'
)
select
    period,
    count(*) as requests,
    avg(duration_ms) as avg_ms,
    percentile_cont(0.95) within group (order by duration_ms) as p95_ms,
    count(*) filter (where status_code >= 500) as errors
from requests
group by period;

select
    period,
    tenant_tier,
    route,
    count(*) as requests,
    percentile_cont(0.95) within group (order by duration_ms) as p95_ms
from requests
group by period, tenant_tier, route;

select
    queue_name,
    count(*) as open_items,
    percentile_cont(0.50) within group (order by extract(epoch from (now() - created_at)) / 3600) as p50_age_hours,
    percentile_cont(0.95) within group (order by extract(epoch from (now() - created_at)) / 3600) as p95_age_hours,
    max(extract(epoch from (now() - created_at)) / 3600) as max_age_hours
from work_item
where status in ('READY', 'CLAIMED')
group by queue_name
order by p95_age_hours desc;

select
    work_item_id,
    queue_name,
    status,
    assigned_to,
    created_at,
    claimed_at,
    now() - coalesce(claimed_at, created_at) as age
from work_item
where status in ('READY', 'CLAIMED')
  and coalesce(claimed_at, created_at) < now() - interval '4 hours'
order by age desc;

with review_cycles as (
    select
        case_id,
        count(*) filter (where event_type = 'RETURNED_FOR_REWORK') as rework_count
    from case_event
    group by case_id
)
select
    count(*) filter (where rework_count > 0) as cases_with_rework,
    count(*) as total_cases,
    count(*) filter (where rework_count > 0) * 1.0 / nullif(count(*), 0) as rework_rate,
    avg(rework_count) as avg_rework_count
from review_cycles;

with source_a as (
    select case_id
    from core.case_file
    where status = 'CLOSED'
      and closed_at >= date '2026-06-01'
      and closed_at <  date '2026-07-01'
),
source_b as (
    select case_id
    from mart.closed_case_fact
    where closed_month = date '2026-06-01'
)
select 'in_a_not_b' as diff_type, case_id from source_a
except
select 'in_a_not_b', case_id from source_b
union all
select 'in_b_not_a', case_id from source_b
except
select 'in_b_not_a', case_id from source_a;

create table metric_assertion_result (
    assertion_id text not null,
    checked_at timestamptz not null default now(),
    status text not null check (status in ('PASS', 'FAIL')),
    observed_value numeric,
    threshold_value numeric,
    details text
);

closed case count in mart must equal core count within freshness boundary
no negative durations
no duplicate fact rows
no impossible state transitions
no future event timestamps

Team A excludes cancelled cases.
Team B includes cancelled cases.
Team C filters by created_at.
Team D filters by closed_at.

with base as (
    select ...
    from ...
    where ... -- population definition
)

case_level as (
    select case_id, max(...), min(...)
    from event_level
    group by case_id
)

metric as (
    select group_key, numerator, denominator
    from case_level
)

quality_check as (
    select count(*) as bad_rows
    from case_level
    where duration_seconds < 0
)

select avg(team_avg_latency)
from team_daily_latency;

select sum(avg_latency * request_count) / nullif(sum(request_count), 0)
from team_daily_latency;

where created_at between report_start and report_end

complete through T-1 day

with cases as (
    select
        case_id,
        case_type,
        assigned_team_id,
        created_at,
        closed_at,
        sla_deadline_at,
        status
    from case_file
    where is_test = false
),
opened as (
    select
        date_trunc('week', created_at) as week,
        case_type,
        count(*) as opened_cases
    from cases
    group by date_trunc('week', created_at), case_type
),
closed as (
    select
        date_trunc('week', closed_at) as week,
        case_type,
        count(*) as closed_cases,
        percentile_cont(0.95) within group (
            order by extract(epoch from (closed_at - created_at)) / 3600
        ) as p95_hours_to_close,
        count(*) filter (where closed_at > sla_deadline_at) as sla_breaches
    from cases
    where closed_at is not null
    group by date_trunc('week', closed_at), case_type
)
select
    coalesce(o.week, c.week) as week,
    coalesce(o.case_type, c.case_type) as case_type,
    coalesce(o.opened_cases, 0) as opened_cases,
    coalesce(c.closed_cases, 0) as closed_cases,
    c.p95_hours_to_close,
    c.sla_breaches,
    c.sla_breaches * 1.0 / nullif(c.closed_cases, 0) as sla_breach_rate
from opened o
full outer join closed c
  on c.week = o.week
 and c.case_type = o.case_type
order by week, case_type;

select
    queue_name,
    count(*) as open_items,
    count(*) filter (where due_at < now()) as overdue_items,
    percentile_cont(0.95) within group (
        order by extract(epoch from (now() - created_at)) / 3600
    ) as p95_age_hours
from work_item
where status in ('READY', 'CLAIMED')
group by queue_name
order by overdue_items desc, p95_age_hours desc;

with case_rework as (
    select
        c.case_id,
        c.assigned_team_id,
        count(e.*) filter (where e.event_type = 'RETURNED_FOR_REWORK') as rework_count
    from case_file c
    left join case_event e on e.case_id = c.case_id
    where c.created_at >= now() - interval '90 days'
    group by c.case_id, c.assigned_team_id
)
select
    assigned_team_id,
    count(*) as cases,
    count(*) filter (where rework_count > 0) as cases_with_rework,
    count(*) filter (where rework_count > 0) * 1.0 / nullif(count(*), 0) as rework_rate,
    avg(rework_count) as avg_rework_events
from case_rework
group by assigned_team_id
order by rework_rate desc;

create table mart.daily_route_metric (
    metric_day date not null,
    route text not null,
    requests bigint not null,
    errors bigint not null,
    p95_ms numeric,
    primary key (metric_day, route)
);

insert into mart.daily_route_metric (metric_day, route, requests, errors, p95_ms)
select
    occurred_at::date as metric_day,
    route,
    count(*) as requests,
    count(*) filter (where status_code >= 500) as errors,
    percentile_cont(0.95) within group (order by duration_ms) as p95_ms
from api_request_log
where occurred_at >= :day
  and occurred_at <  :day + interval '1 day'
group by occurred_at::date, route
on conflict (metric_day, route)
do update set
    requests = excluded.requests,
    errors = excluded.errors,
    p95_ms = excluded.p95_ms;

created -> assigned -> reviewed -> approved -> closed

grain before aggregation
population before metric
denominator before rate
time semantics before trend
sample size before percentile
reconciliation before trust
decision before dashboard

The system feels slower.
The workflow is blocked.
The release caused issues.
SLA is getting worse.

p95 for /cases/search increased from 180ms to 780ms for enterprise tenants after version 2026.06.15, error rate remained flat, and query plan changed from index seek to sequential scan due to parameter distribution.