Aggregation changes the grain of data: many input rows become one result per group. Trustworthy reports define that grain, denominator, time zone, missing-data behavior, and join cardinality before calculating. PostgreSQL supports conditional aggregates, distinct counts, ordered aggregates, grouping sets, and date functions, but syntax cannot repair an ambiguous business metric.
First declare the output grain: “one row per organization per UTC day.” Then identify the input fact: tickets created. Only then write aggregates. WHERE filters input rows; HAVING filters completed groups.
count(*) counts rows; count(column) ignores nulls. NULLIF prevents division by zero. The half-open time interval avoids boundary overlap.
Joining tickets to both messages and tags can multiply each ticket by messages × tags. Pre-aggregate each child at its own grain, or use separate correlated/lateral queries, before combining results.
“Daily” must name a business time zone. Convert the instant before deriving the date, and test daylight-saving transitions for relevant zones. Store the reporting zone as configuration; do not silently depend on the session.
Evaluation metrics are aggregates. Accuracy, groundedness, authorization failure rate, latency percentiles, and cost per successful answer all require precise populations and versions. A model dashboard that mixes prompt versions or excludes failed requests can improve visually while the system worsens.
Create a tiny reconciliation dataset where the correct answer is calculable by hand. Compare total tickets to the sum of mutually exclusive statuses. Add messages and prove the ticket count remains unchanged. Test a zero-row interval and one boundary timestamp.
Report median-like operational insight without claiming a true percentile: produce count, minimum, maximum, and average messages per ticket per organization, including zero-message tickets.
Acceptance criterion: a hand-built five-ticket fixture reconciles exactly and zero-message tickets affect count and average.