Agent / Team

-- ==========================================
-- 1. GOOGLE BIGQUERY SQL
-- ==========================================
DECLARE start_date DATE DEFAULT DATE_SUB(CURRENT_DATE(), INTERVAL 90 DAY);
DECLARE end_date DATE DEFAULT CURRENT_DATE();
DECLARE account_tz STRING DEFAULT 'America/New_York';  -- helpdesk account time zone

WITH solved AS (
  -- Tickets solved in the window, dated in the helpdesk account time zone
  SELECT ticket_id, solved_at, DATE(solved_at, account_tz) AS solved_date
  FROM `your-project.crm.support_tickets`
  WHERE solved_at IS NOT NULL
    AND DATE(solved_at, account_tz) BETWEEN start_date AND end_date
),
assignment_at_solve AS (
  -- Assignment held at solve time: last audit entry at or before solved_at
  SELECT s.ticket_id, s.solved_date, a.agent_id, a.group_id, a.owner_type
  FROM solved s
  LEFT JOIN `your-project.crm.ticket_assignment_audit` a
    ON a.ticket_id = s.ticket_id AND a.assigned_at <= s.solved_at
  QUALIFY ROW_NUMBER() OVER (PARTITION BY s.ticket_id ORDER BY a.assigned_at DESC) = 1
)
-- Team and agent keyed on stable ids, labels joined at report time; bots map to automation, queue-owned cases carry a null agent
SELECT
  t.ticket_id,
  t.solved_date,
  COALESCE(t.group_id, 'unassigned') AS team_id,
  COALESCE(g.group_name, 'unassigned') AS team_name,
  CASE WHEN ag.is_bot THEN 'automation'
       WHEN t.owner_type = 'queue' THEN NULL
       ELSE COALESCE(t.agent_id, 'unassigned') END AS agent_id,
  CASE WHEN ag.is_bot THEN 'automation'
       WHEN t.owner_type = 'queue' THEN NULL
       ELSE COALESCE(ag.agent_name, 'unassigned') END AS agent_name,
  CASE WHEN ag.is_bot THEN NULL ELSE NOT ag.is_active END AS agent_inactive
FROM assignment_at_solve t
LEFT JOIN `your-project.crm.support_agents` ag ON ag.agent_id = t.agent_id
LEFT JOIN `your-project.crm.support_groups` g ON g.group_id = t.group_id;

-- ==========================================
-- 2. ADOBE AEP QUERY SERVICE SQL
-- ==========================================
WITH params AS (
  -- Relative window and helpdesk account time zone
  SELECT date_sub(current_date(), 90) AS start_date, current_date() AS end_date, 'America/New_York' AS account_tz
),
solved AS (
  -- Tickets solved in the window, dated in the helpdesk account time zone
  SELECT k.ticket_id, k.solved_at, to_date(from_utc_timestamp(k.solved_at, p.account_tz)) AS solved_date
  FROM crm_support_tickets k
  CROSS JOIN params p
  WHERE k.solved_at IS NOT NULL
    AND to_date(from_utc_timestamp(k.solved_at, p.account_tz)) BETWEEN p.start_date AND p.end_date
),
assignment_at_solve AS (
  -- Assignment held at solve time: last audit entry at or before solved_at
  SELECT ticket_id, solved_date, agent_id, group_id, owner_type
  FROM (
    SELECT s.ticket_id, s.solved_date, a.agent_id, a.group_id, a.owner_type,
           ROW_NUMBER() OVER (PARTITION BY s.ticket_id ORDER BY a.assigned_at DESC) AS rn
    FROM solved s
    LEFT JOIN crm_ticket_assignment_audit a
      ON a.ticket_id = s.ticket_id AND a.assigned_at <= s.solved_at
  ) x
  WHERE rn = 1
)
-- Team and agent keyed on stable ids, labels joined at report time; bots map to automation, queue-owned cases carry a null agent
SELECT
  t.ticket_id,
  t.solved_date,
  COALESCE(t.group_id, 'unassigned') AS team_id,
  COALESCE(g.group_name, 'unassigned') AS team_name,
  CASE WHEN ag.is_bot THEN 'automation'
       WHEN t.owner_type = 'queue' THEN NULL
       ELSE COALESCE(t.agent_id, 'unassigned') END AS agent_id,
  CASE WHEN ag.is_bot THEN 'automation'
       WHEN t.owner_type = 'queue' THEN NULL
       ELSE COALESCE(ag.agent_name, 'unassigned') END AS agent_name,
  CASE WHEN ag.is_bot THEN NULL ELSE NOT ag.is_active END AS agent_inactive
FROM assignment_at_solve t
LEFT JOIN crm_support_agents ag ON ag.agent_id = t.agent_id
LEFT JOIN crm_support_groups g ON g.group_id = t.group_id;