Nodes Execution Time and Hits.sql

select
   pil.externalId,
   pil.processId,
    nid,
    nodetype,
    nodename,
    count(nid) as total_hits,
    avg(execution_time) as averageExecutionTime,
    min(execution_time) as minExecutionTime,
    max(execution_time) as maxExecutionTime
from(
        select
                max(log_date) as lastLog,
                processinstanceid as piid,  
                nodeinstanceid as niid, 
                nodeid as nid, 
                nodetype,
                nodename, 
                DATEDIFF(SECOND, min(log_date), max(log_date)) as execution_time     
        from 
                NodeInstanceLog 
        group by
              processinstanceid, 
               nodeinstanceid, 
              nid
       order by lastLog
)
inner join
     ProcessInstanceLog pil on pil.processInstanceId = piid
group by 
     pil.externalId,
     nid,
     nodename