Average Resolution Time

-- ==========================================
-- 1. GOOGLE BIGQUERY SQL
-- ==========================================
DECLARE start_date DATE DEFAULT DATE_SUB(CURRENT_DATE(), INTERVAL 90 DAY);
DECLARE end_date DATE DEFAULT CURRENT_DATE();

-- Final solve per ticket: the last transition to solved, so a reopened ticket is measured to its final solve
WITH final_solve AS (
  SELECT
    ticket_id,
    MAX(changed_at) AS final_solved_at
  FROM `your-project.crm.ticket_status_history`
  WHERE new_status = 'solved'
  GROUP BY ticket_id
),
-- Cohort: tickets still solved/closed whose final solve falls in the period; spam, deleted and merged-away tickets excluded; calendar hours, pending time kept in the clock
resolved AS (
  SELECT
    TIMESTAMP_DIFF(f.final_solved_at, t.created_at, SECOND) / 3600.0 AS resolution_hours
  FROM `your-project.crm.support_tickets` AS t
  JOIN final_solve AS f USING (ticket_id)
  WHERE t.current_status IN ('solved', 'closed')
    AND t.is_spam = FALSE
    AND t.is_deleted = FALSE
    AND t.merged_into_ticket_id IS NULL
    AND DATE(f.final_solved_at) BETWEEN start_date AND end_date
)
-- Mean resolution hours, with the median and 90th percentile the definition requires alongside it
SELECT
  COUNT(*) AS tickets_solved,
  SAFE_DIVIDE(SUM(resolution_hours), COUNT(*)) AS average_resolution_hours,
  APPROX_QUANTILES(resolution_hours, 100)[OFFSET(50)] AS median_resolution_hours,
  APPROX_QUANTILES(resolution_hours, 100)[OFFSET(90)] AS p90_resolution_hours
FROM resolved;

-- ==========================================
-- 2. ADOBE AEP QUERY SERVICE SQL
-- ==========================================
-- Reporting window: last 90 days to today
WITH params AS (
  SELECT
    DATE_SUB(CURRENT_DATE, 90) AS start_date,
    CURRENT_DATE AS end_date
),
-- Final solve per ticket: the last transition to solved, so a reopened ticket is measured to its final solve
final_solve AS (
  SELECT
    ticket_id,
    MAX(changed_at) AS final_solved_at
  FROM crm_ticket_status_history
  WHERE new_status = 'solved'
  GROUP BY ticket_id
),
-- Cohort: tickets still solved/closed whose final solve falls in the period; spam, deleted and merged-away tickets excluded; calendar hours, pending time kept in the clock
resolved AS (
  SELECT
    (UNIX_TIMESTAMP(f.final_solved_at) - UNIX_TIMESTAMP(t.created_at)) / 3600.0 AS resolution_hours
  FROM crm_support_tickets AS t
  JOIN final_solve AS f ON f.ticket_id = t.ticket_id
  CROSS JOIN params AS p
  WHERE t.current_status IN ('solved', 'closed')
    AND t.is_spam = FALSE
    AND t.is_deleted = FALSE
    AND t.merged_into_ticket_id IS NULL
    AND TO_DATE(f.final_solved_at) BETWEEN p.start_date AND p.end_date
)
-- Mean resolution hours, with the median and 90th percentile the definition requires alongside it
SELECT
  COUNT(*) AS tickets_solved,
  SUM(resolution_hours) / NULLIF(COUNT(*), 0) AS average_resolution_hours,
  PERCENTILE_APPROX(resolution_hours, 0.5) AS median_resolution_hours,
  PERCENTILE_APPROX(resolution_hours, 0.9) AS p90_resolution_hours
FROM resolved;