Modeling: Date-diff metrics and many-to-many relations


I need some directions on how to properly model a dataset similar to Jira ticketing system, of audit log table type. See several columns/rows of interested in attached screen.

priority, assigned_user, status, and other attributes vary over time per cases. Here are two common query types to support:

  1. See current state at some point of time, for example, conditional counts over dimension:

of Open Cases Last Month,

of Closed Cases,

of Open Cases % Change from previous period, etc.

Basically I need a dynamic derived attribute

Current State = MAX(case_num) or LAST(case_num)

So I could build a metric:

of Open Cases Last Month = SELECT COUNT([Current State]) WHERE status = ‘open’ AND ‘2016-06-01 T00:00:00’ <= __time AND __time < ‘2016-07-01 T00:00:00’

  1. Calculate date diff metrics like:

AVG time to resolve a ticket, pseudo code:

SELECT SUM( DATEDIFF( (SELECT MAX(__time) WHERE status = ‘resolved’ BY case), (SELECT MAX(__time) WHERE status = ‘open’ BY case) ) ) / # of Cases

Is this possible?

Otherwise, is the only alternative to pre-calculate/aggregate all metrics with stateful data processing, and brake target dataset into small data sources (assignee metrics, priority metrics, state metrics)? There is requirement to keep data details too.