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

Hi,

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.

Thanks,

Bohdan