Call Report Calculations with Multiple SQL Queries

1. Retrieve outbound call statistics for extensions.

   SELECT 
     src as ext_num, 
     SUM(failed) AS failed, 
     SUM(vm) AS vm, 
     SUM(
       CASE WHEN c.scenario = 'mobile' THEN 0 WHEN COALESCE(mobile.mobile_answered, 0) = 1 THEN 1 WHEN c.call_flow = '' THEN c.answered WHEN c.call_flow != '' 
       AND c.is_group = 1 THEN c.answered WHEN c.call_flow = 'Monitor' THEN c.answered ELSE 0 END
     ) AS answered, 
     SUM(
       CASE WHEN c.scenario = 'mobile' THEN 0 WHEN COALESCE(mobile.mobile_answered, 0) = 1 THEN 0 WHEN c.call_flow = '' THEN c.no_answer WHEN c.call_flow != '' 
       AND c.is_group = 1 THEN c.no_answer WHEN c.call_flow = 'Monitor' THEN c.no_answer ELSE 0 END
     ) AS total_no_answered, 
     SUM(
       CASE WHEN c.scenario = 'mobile' THEN 0 WHEN COALESCE(mobile.mobile_answered, 0) = 1 THEN 0 WHEN c.call_flow = '' THEN c.busy WHEN c.call_flow != '' 
       AND c.is_group = 1 THEN c.busy WHEN c.call_flow = 'Monitor' THEN c.busy ELSE 0 END
     ) AS busy, 
     SUM(
       CASE WHEN c.scenario = 'mobile' THEN 0 WHEN COALESCE(mobile.mobile_answered, 0) = 1 THEN 0 WHEN c.call_flow = '' THEN c.abandoned WHEN c.call_flow != '' 
       AND c.is_group = 1 THEN c.abandoned WHEN c.call_flow = 'Monitor' THEN c.abandoned ELSE 0 END
     ) AS abandoned, 
     SUM(
       CASE WHEN c.scenario = 'mobile' THEN 0 WHEN COALESCE(mobile.mobile_answered, 0) = 1 THEN COALESCE(mobile.mobile_ring, 0) WHEN c.call_flow = '' THEN c.ring_duration WHEN c.call_flow != '' 
       AND c.is_group = 1 THEN c.ring_duration WHEN c.call_flow = 'Monitor' THEN c.ring_duration ELSE 0 END
     ) AS ring_duration, 
     SUM(
       CASE WHEN c.scenario = 'mobile' THEN 0 WHEN COALESCE(mobile.mobile_answered, 0) = 1 THEN COALESCE(mobile.mobile_talk, 0) WHEN c.call_flow = '' THEN (
         c.talk_duration + c.hold_duration
       ) WHEN c.call_flow != '' 
       AND c.is_group = 1 THEN (
         c.talk_duration + c.hold_duration
       ) WHEN c.call_flow = 'Monitor' THEN (
         c.talk_duration + c.hold_duration
       ) ELSE 0 END
     ) AS talk_duration 
   FROM 
     cdr.cdr_cleaned AS c 
     LEFT JOIN (
       SELECT 
         m.uid, 
         MAX(m.answered) AS mobile_answered, 
         MAX(
           CASE WHEN m.answered = 1 THEN m.ring_duration ELSE 0 END
         ) AS mobile_ring, 
         MAX(
           CASE WHEN m.answered = 1 THEN m.talk_duration + m.hold_duration ELSE 0 END
         ) AS mobile_talk 
       FROM 
         cdr.cdr_cleaned AS m 
       WHERE 
         m.scenario = 'mobile' 
       GROUP BY 
         `m`.`uid`
     ) AS mobile ON c.uid = mobile.uid 
   WHERE 
     c.src in (
       '1028', '1016', '1003', '1004', '1006', 
       '1014', '1033', '1000', '1034', '1023', 
       '1027', '1021', '1019', '1022', '1020', 
       '1015', '1018', '1025', '1011', '1012', 
       '1029', '1024', '1032', '1008', '1007', 
       '1010', '1013', '1026', '1030', '1017', 
       '1001', '1009', '1031', '1002', '1005'
     ) 
     AND c.start_ts >= 1773590400000000 
     AND c.start_ts <= 1776268799999999 
   GROUP BY 
     `src`

   The query returns the following data:

   • ext_num: Extension number.
   • answered: The number of calls that the extension answered.
   • total_no_answered: The number of calls that the extension didn't answer.
   • busy: The number of calls received when the extension was busy.
   • abandoned: The number of calls abandoned by caller before connecting to the extension.
   • vm: The number of calls routed to the extension's voicemail.
   • failed: The number of calls that the extension failed to make.
   • ring_duration: The total time between calls started and calls answered.
   • talk_duration: The total time between calls answered and calls ended.
2. Retrieve inbound call statistics for extensions.

   SELECT 
     dst as ext_num, 
     SUM(failed) AS failed, 
     SUM(vm) AS vm, 
     SUM(
       CASE WHEN c.scenario = 'mobile' THEN 0 WHEN COALESCE(mobile.mobile_answered, 0) = 1 THEN 1 WHEN c.call_flow = '' THEN c.answered WHEN c.call_flow != '' 
       AND c.is_group = 0 THEN c.answered ELSE 0 END
     ) AS answered, 
     SUM(
       CASE WHEN c.scenario = 'mobile' THEN 0 WHEN COALESCE(mobile.mobile_answered, 0) = 1 THEN 0 WHEN c.call_flow = '' THEN (c.no_answer) WHEN c.call_flow != '' 
       AND c.is_group = 0 
       AND c.is_display = 1 THEN (c.no_answer) ELSE 0 END
     ) AS total_no_answered, 
     SUM(
       CASE WHEN c.scenario = 'mobile' THEN 0 WHEN COALESCE(mobile.mobile_answered, 0) = 1 THEN 0 WHEN c.call_flow = '' THEN c.busy WHEN c.call_flow != '' 
       AND c.is_group = 0 
       AND c.is_display = 1 THEN c.busy ELSE 0 END
     ) AS busy, 
     SUM(
       CASE WHEN c.scenario = 'mobile' THEN 0 WHEN COALESCE(mobile.mobile_answered, 0) = 1 THEN 0 WHEN c.call_flow = '' THEN c.abandoned ELSE 0 END
     ) AS abandoned, 
     SUM(
       CASE WHEN c.scenario = 'mobile' THEN 0 WHEN COALESCE(mobile.mobile_answered, 0) = 1 THEN COALESCE(mobile.mobile_ring, 0) WHEN c.call_flow = '' THEN c.ring_duration WHEN c.call_flow != '' 
       AND c.is_group = 0 
       AND c.is_display = 1 THEN c.ring_duration ELSE 0 END
     ) AS ring_duration, 
     SUM(
       CASE WHEN c.scenario = 'mobile' THEN 0 WHEN COALESCE(mobile.mobile_answered, 0) = 1 THEN COALESCE(mobile.mobile_talk, 0) WHEN c.call_flow = '' THEN (
         c.talk_duration + c.hold_duration
       ) WHEN c.call_flow != '' 
       AND c.is_group = 0 THEN (
         c.talk_duration + c.hold_duration
       ) ELSE 0 END
     ) AS talk_duration 
   FROM 
     cdr.cdr_cleaned AS c 
     LEFT JOIN (
       SELECT 
         m.uid, 
         MAX(m.answered) AS mobile_answered, 
         MAX(
           CASE WHEN m.answered = 1 THEN m.ring_duration ELSE 0 END
         ) AS mobile_ring, 
         MAX(
           CASE WHEN m.answered = 1 THEN m.talk_duration + m.hold_duration ELSE 0 END
         ) AS mobile_talk 
       FROM 
         cdr.cdr_cleaned AS m 
       WHERE 
         m.scenario = 'mobile' 
       GROUP BY 
         `m`.`uid`
     ) AS mobile ON c.uid = mobile.uid 
   WHERE 
     c.dst in (
       '1028', '1016', '1003', '1004', '1006', 
       '1014', '1033', '1000', '1034', '1023', 
       '1027', '1021', '1019', '1022', '1020', 
       '1015', '1018', '1025', '1011', '1012', 
       '1029', '1024', '1032', '1008', '1007', 
       '1010', '1013', '1026', '1030', '1017', 
       '1001', '1009', '1031', '1002', '1005'
     ) 
     AND c.start_ts >= 1773590400000000 
     AND c.start_ts <= 1776268799999999 
   GROUP BY 
     `dst`

   The query returns the following data:

   • ext_num: Extension number.
   • answered: The number of calls that the extension answered.
   • total_no_answered: The number of calls that the extension didn't answer.
   • busy: The number of calls received when the extension was busy.
   • abandoned: The number of calls abandoned by caller before connecting to the extension.
   • vm: The number of calls routed to the extension's voicemail.
   • failed: The number of calls that the extension failed to make.
   • ring_duration: The total time between calls started and calls answered.
   • talk_duration: The total time between calls answered and calls ended.

3. Merge the inbound and outbound call statistics by extension number to generate the complete report.

1. Retrieve outbound call statistics for extensions.

   SELECT 
     src as ext_num, 
     MONTH(datetime) AS time, 
     SUM(failed) AS failed, 
     SUM(vm) AS vm, 
     SUM(
       CASE WHEN c.scenario = 'mobile' THEN 0 WHEN COALESCE(mobile.mobile_answered, 0) = 1 THEN 1 WHEN c.call_flow = '' THEN c.answered WHEN c.call_flow != '' 
       AND c.is_group = 1 THEN c.answered WHEN c.call_flow = 'Monitor' THEN c.answered ELSE 0 END
     ) AS answered, 
     SUM(
       CASE WHEN c.scenario = 'mobile' THEN 0 WHEN COALESCE(mobile.mobile_answered, 0) = 1 THEN 0 WHEN c.call_flow = '' THEN c.no_answer WHEN c.call_flow != '' 
       AND c.is_group = 1 THEN c.no_answer WHEN c.call_flow = 'Monitor' THEN c.no_answer ELSE 0 END
     ) AS total_no_answered, 
     SUM(
       CASE WHEN c.scenario = 'mobile' THEN 0 WHEN COALESCE(mobile.mobile_answered, 0) = 1 THEN 0 WHEN c.call_flow = '' THEN c.busy WHEN c.call_flow != '' 
       AND c.is_group = 1 THEN c.busy WHEN c.call_flow = 'Monitor' THEN c.busy ELSE 0 END
     ) AS busy, 
     SUM(
       CASE WHEN c.scenario = 'mobile' THEN 0 WHEN COALESCE(mobile.mobile_answered, 0) = 1 THEN 0 WHEN c.call_flow = '' THEN c.abandoned WHEN c.call_flow != '' 
       AND c.is_group = 1 THEN c.abandoned WHEN c.call_flow = 'Monitor' THEN c.abandoned ELSE 0 END
     ) AS abandoned, 
     SUM(
       CASE WHEN c.scenario = 'mobile' THEN 0 WHEN COALESCE(mobile.mobile_answered, 0) = 1 THEN COALESCE(mobile.mobile_ring, 0) WHEN c.call_flow = '' THEN c.ring_duration WHEN c.call_flow != '' 
       AND c.is_group = 1 THEN c.ring_duration WHEN c.call_flow = 'Monitor' THEN c.ring_duration ELSE 0 END
     ) AS ring_duration, 
     SUM(
       CASE WHEN c.scenario = 'mobile' THEN 0 WHEN COALESCE(mobile.mobile_answered, 0) = 1 THEN COALESCE(mobile.mobile_talk, 0) WHEN c.call_flow = '' THEN (
         c.talk_duration + c.hold_duration
       ) WHEN c.call_flow != '' 
       AND c.is_group = 1 THEN (
         c.talk_duration + c.hold_duration
       ) WHEN c.call_flow = 'Monitor' THEN (
         c.talk_duration + c.hold_duration
       ) ELSE 0 END
     ) AS talk_duration 
   FROM 
     cdr.cdr_cleaned AS c 
     LEFT JOIN (
       SELECT 
         m.uid, 
         MAX(m.answered) AS mobile_answered, 
         MAX(
           CASE WHEN m.answered = 1 THEN m.ring_duration ELSE 0 END
         ) AS mobile_ring, 
         MAX(
           CASE WHEN m.answered = 1 THEN m.talk_duration + m.hold_duration ELSE 0 END
         ) AS mobile_talk 
       FROM 
         cdr.cdr_cleaned AS m 
       WHERE 
         m.scenario = 'mobile' 
       GROUP BY 
         `m`.`uid`
     ) AS mobile ON c.uid = mobile.uid 
   WHERE 
     c.src in (
       '1032', '1008', '1007', '1010', '1013', 
       '1026', '1030', '1017', '1001', '1009', 
       '1031', '1002', '1005', '1028', '1016', 
       '1003', '1004', '1006', '1014', '1033', 
       '1000', '1034', '1023', '1027', '1021', 
       '1019', '1022', '1020', '1015', '1018', 
       '1025', '1011', '1012', '1029', '1024'
     ) 
     AND c.start_ts >= 1767196800000000 
     AND c.start_ts <= 1798732799999999 
   GROUP BY 
     src, 
     time 
   ORDER BY 
     time, 
     ext_num asc

   The query returns the following data:

   • time: Time.
   • ext_num: Extension number.
   • answered: The number of calls that the extension answered.
   • total_no_answered: The number of calls that the extension didn't answer.
   • busy: The number of calls received when the extension was busy.
   • abandoned: The number of calls abandoned by caller before connecting to the extension.
   • failed: The number of calls that the extension failed to make.
   • vm: The number of calls routed to the extension's voicemail.
   • ring_duration: The total time between calls started and calls answered.
   • talk_duration: The total time between calls answered and calls ended.
2. Retrieve inbound call statistics for extensions.

   SELECT 
     dst as ext_num, 
     MONTH(datetime) AS time, 
     SUM(failed) AS failed, 
     SUM(vm) AS vm, 
     SUM(
       CASE WHEN c.scenario = 'mobile' THEN 0 WHEN COALESCE(mobile.mobile_answered, 0) = 1 THEN 1 WHEN c.call_flow = '' THEN c.answered WHEN c.call_flow != '' 
       AND c.is_group = 0 THEN c.answered ELSE 0 END
     ) AS answered, 
     SUM(
       CASE WHEN c.scenario = 'mobile' THEN 0 WHEN COALESCE(mobile.mobile_answered, 0) = 1 THEN 0 WHEN c.call_flow = '' THEN (c.no_answer) WHEN c.call_flow != '' 
       AND c.is_group = 0 
       AND c.is_display = 1 THEN (c.no_answer) ELSE 0 END
     ) AS total_no_answered, 
     SUM(
       CASE WHEN c.scenario = 'mobile' THEN 0 WHEN COALESCE(mobile.mobile_answered, 0) = 1 THEN 0 WHEN c.call_flow = '' THEN c.busy WHEN c.call_flow != '' 
       AND c.is_group = 0 
       AND c.is_display = 1 THEN c.busy ELSE 0 END
     ) AS busy, 
     SUM(
       CASE WHEN c.scenario = 'mobile' THEN 0 WHEN COALESCE(mobile.mobile_answered, 0) = 1 THEN 0 WHEN c.call_flow = '' THEN c.abandoned ELSE 0 END
     ) AS abandoned, 
     SUM(
       CASE WHEN c.scenario = 'mobile' THEN 0 WHEN COALESCE(mobile.mobile_answered, 0) = 1 THEN COALESCE(mobile.mobile_ring, 0) WHEN c.call_flow = '' THEN c.ring_duration WHEN c.call_flow != '' 
       AND c.is_group = 0 
       AND c.is_display = 1 THEN c.ring_duration ELSE 0 END
     ) AS ring_duration, 
     SUM(
       CASE WHEN c.scenario = 'mobile' THEN 0 WHEN COALESCE(mobile.mobile_answered, 0) = 1 THEN COALESCE(mobile.mobile_talk, 0) WHEN c.call_flow = '' THEN (
         c.talk_duration + c.hold_duration
       ) WHEN c.call_flow != '' 
       AND c.is_group = 0 THEN (
         c.talk_duration + c.hold_duration
       ) ELSE 0 END
     ) AS talk_duration 
   FROM 
     cdr.cdr_cleaned AS c 
     LEFT JOIN (
       SELECT 
         m.uid, 
         MAX(m.answered) AS mobile_answered, 
         MAX(
           CASE WHEN m.answered = 1 THEN m.ring_duration ELSE 0 END
         ) AS mobile_ring, 
         MAX(
           CASE WHEN m.answered = 1 THEN m.talk_duration + m.hold_duration ELSE 0 END
         ) AS mobile_talk 
       FROM 
         cdr.cdr_cleaned AS m 
       WHERE 
         m.scenario = 'mobile' 
       GROUP BY 
         `m`.`uid`
     ) AS mobile ON c.uid = mobile.uid 
   WHERE 
     c.dst in (
       '1032', '1008', '1007', '1010', '1013', 
       '1026', '1030', '1017', '1001', '1009', 
       '1031', '1002', '1005', '1028', '1016', 
       '1003', '1004', '1006', '1014', '1033', 
       '1000', '1034', '1023', '1027', '1021', 
       '1019', '1022', '1020', '1015', '1018', 
       '1025', '1011', '1012', '1029', '1024'
     ) 
     AND c.start_ts >= 1767196800000000 
     AND c.start_ts <= 1798732799999999 
   GROUP BY 
     dst, 
     time 
   ORDER BY 
     time, 
     ext_num asc

   The query returns the following data:

   • time: Time.
   • ext_num: Extension number.
   • answered: The number of calls that the extension answered.
   • total_no_answered: The number of calls that the extension didn't answer.
   • busy: The number of calls received when the extension was busy.
   • abandoned: The number of calls abandoned by caller before connecting to the extension.
   • failed: The number of calls that the extension failed to make.
   • vm: The number of calls routed to the extension's voicemail.
   • ring_duration: The total time between calls started and calls answered.
   • talk_duration: The total time between calls answered and calls ended.

3. Merge the inbound and outbound call statistics by extension number and time period to generate the complete report.

1. Retrieve inbound queue call statistics for agents.

   SELECT 
     c.dst AS agent, 
     count(answered) AS answered_call, 
     SUM(
       CASE WHEN sub.duration != NULL THEN c.duration + sub.duration ELSE c.duration END
     ) AS duration, 
     SUM(talk_duration) AS talking_time, 
     SUM(hold_duration) AS hold_time, 
     SUM(
       CASE WHEN sub.ring_duration != NULL THEN c.ring_duration + sub.ring_duration ELSE c.ring_duration END
     ) AS waiting_time 
   FROM 
     cdr.cdr_cleaned as c 
     LEFT JOIN (
       SELECT 
         uid, 
         dst, 
         sum(duration) as duration, 
         sum(ring_duration) AS ring_duration 
       FROM 
         cdr.cdr_cleaned as c 
       WHERE 
         start_ts >= 1773590400000000 
         AND start_ts <= 1776268799999999 
         AND (
           c.dst IN ('1000', '1001')
         ) 
         AND src != 'PBX' 
         AND call_flow_number = '6404' 
         AND call_flow = 'Queue' 
         AND disposition != 'ANSWERED' 
         AND is_group = '0' 
       GROUP BY 
         uid, 
         dst
     ) AS sub ON c.uid = sub.uid 
     and c.dst = sub.dst 
   WHERE 
     start_ts >= 1773590400000000 
     AND start_ts <= 1776268799999999 
     AND (
       c.dst IN ('1000', '1001')
     ) 
     AND src != 'PBX' 
     AND call_flow_number = '6404' 
     AND call_flow = 'Queue' 
     AND disposition = 'ANSWERED' 
     AND is_group = '0' 
   GROUP BY 
     `c`.`dst`

   The query returns the following data:

   • agent: Agent number.
   • answered_call: The number of incoming queue calls answered by the agent.
   • duration: The amount of time that agents spent handling incoming queue calls, from ringing to call end.
   • talking_time: The amount of time that agents spent in incoming calls.
   • hold_time: The amount of time that agents held incoming calls.
   • waiting_time: The waiting time before incoming queue calls were answered.
2. Retrieve outbound queue call statistics for agents.

   SELECT 
     src AS agent, 
     COUNT(uid) AS total_call, 
     SUM(answered) AS answered_call, 
     SUM(
       CASE WHEN disposition = 'ANSWERED' THEN duration ELSE 0 END
     ) AS duration, 
     SUM(
       CASE WHEN disposition = 'ANSWERED' THEN talk_duration ELSE 0 END
     ) AS talking_time, 
     SUM(
       CASE WHEN disposition = 'ANSWERED' THEN hold_duration ELSE 0 END
     ) AS hold_time, 
     SUM(
       CASE WHEN disposition = 'ANSWERED' THEN ring_duration ELSE 0 END
     ) AS waiting_time 
   FROM 
     `cdr`.`cdr_cleaned` 
   WHERE 
     start_ts >= 1773590400000000 
     AND start_ts <= 1776268799999999 
     AND call_type = 'Outbound' 
     AND src in ('1000', '1001') 
     AND NOT(srcname = 'Conference Call') 
     AND is_group = '0' 
   GROUP BY 
     `src`

   The query returns the following data:
   • agent: Agent number.
   • total_call: The number of outgoing calls placed by the agent.
   • answered_call: The number of outgoing calls that were answered by the agent.
   • duration: The amount of time that agents spent handling outbound queue calls, from ringing to call end.
   • talking_time: The amount of time that agents spent in outgoing calls.
   • hold_time: The amount of time that agents held outbound queue calls.
   • waiting_time: The waiting time before outbound queue calls were answered.
3. Merge the inbound and outbound queue call statistics by agent's extension number to generate the overall statistics.

   • total_duration: Sum of duration for agents' inbound and outbound queue calls.
   • total_talk_time: Sum of talking_time and hold_time for agents' inbound and outbound queue calls.
   • total_hold_time: Sum of hold_time for agents' inbound and outbound queue calls.
   • total_waiting_time: Sum of waiting_time for agents' inbound and outbound queue calls.
   • total_call: Sum of answered_call for agents' inbound queue calls and total_call for agents' outbound queue calls..
   • inbound_answered_call: Sum of answered_call for agents' inbound queue calls.
   • inbound_duration: Sum of talking_time and hold_time for agents' inbound queue calls.
   • outbound_total_call: Sum of total_call for agents' outbound queue calls.
   • outbound_answered_call: Sum of answered_call for agents' outbound queue calls.
   • outbound_duration: Sum of talking_time and hold_time for agents' outbound queue calls.

4. Calculate average call durations for each agent.

   • avg_server_time = total_duration / (inbound_answered_call + outbound_answered_call)
   • avg_talking_time = total_talk_time / (inbound_answered_call + outbound_answered_call)
   • avg_hold_time = total_hold_time / (inbound_answered_call + outbound_answered_call)
   • avg_waiting_time = total_waiting_time /(inbound_answered_call+ outbound_answered_call)

SELECT 
  agent, 
  timestamp, 
  event 
FROM 
  `queue_log` 
WHERE 
  (
    queuename = 'queue-6402' 
    and event in('ADDMEMBER', 'REMOVEMEMBER')
  ) 
  AND (timestamp >= 1773590400) 
  AND (timestamp <= 1776268799) 
GROUP BY 
  agent, 
  timestamp, 
  event 
ORDER BY 
  timestamp asc

• agent: Agent number.
• timestamp: The date and time when the agent logged in to or logged out of a queue.
• event:
  • ADDMEMBER: Agent logged in to the queue.
  • REMOVEMEMBER: Agent logged out of the queue.

SELECT 
  agent, 
  timestamp, 
  event, 
  data1 
FROM 
  `queue_log` 
WHERE 
  (
    queuename = 'queue-6404' 
    and event in(
      'PAUSE', 'UNPAUSE', 'REMOVEMEMBER'
    )
  ) 
  AND (timestamp >= 1774022400) 
  AND (timestamp <= 1776700799) 
GROUP BY 
  agent, 
  timestamp, 
  event, 
  data1 
ORDER BY 
  timestamp asc

• agent: Agent number.
• timestamp: The date and time when agents paused or resumed service.
• data1: Pause reason.
• event:
  • PAUSE: Agent paused service.
  • UNPAUSE: Agent resumed service.

SELECT 
  dst, 
  COUNT(*) AS total_count, 
  SUM(CASE WHEN is_qcb = 1 THEN 1 ELSE 0 END) AS qcb_count, 
  SUM(
    CASE WHEN is_qcb = 1 
    AND qcb_status = 'success' THEN 1 ELSE 0 END
  ) AS qcb_success_count,
  SUM(
    CASE WHEN is_qcb = 1 
    AND qcb_status != 'success' THEN 1 ELSE 0 END
  ) AS qcb_failed_count 
FROM 
  `cdr`.`cdr_cleaned` 
WHERE 
  start_ts >= 1774108800000000 
  AND start_ts <= 1776787199999999 
  AND call_flow_number in ('6404') 
  AND call_flow = 'Queue' 
  AND is_group = '1' 
GROUP BY 
  `dst`

• dst: Queue number.
• qcb_failed_count: The number of failed callbacks.
• qcb_success_count: The number of successful callbacks.
• qcb_count: The total number of callbacks for which callers requested successfully.
• total_count: The total number of calls that the queue received.

1. Aggregate queue call data excluding q_half_consult records to build the dataset normal_data.

   SELECT 
     `dst`, 
     Count(*) as total_calls, 
     sum(answered) as answered_calls, 
     sum(missed) as missed_calls, 
     sum(abandoned) as abandoned_calls, 
     max(ring_duration) as max_ring_time, 
     sum(answered_ring_duration) as total_answered_ring_time, 
     sum(ring_duration) as total_ring_time, 
     sum(hold_duration) as total_hold_time, 
     sum(talk_duration) as total_talking_time, 
     sum(member_hold_duration) as total_member_hold_duration, 
     sum(member_answered_count) as total_member_answered, 
     SUM(
       CASE WHEN dst = '6404' 
       AND disposition = 'ANSWERED' 
       AND (
         ring_duration - COALESCE(join_announcement_duration, 0)
       )< 60 THEN 1 ELSE 0 END
     ) AS total_sla_call 
   FROM 
     cdr.cdr_cleaned as c 
   WHERE 
     c.is_group = 1 
     AND (
       not (
         c.ring_duration <= 1 
         and c.abandoned = 1
       )
     ) 
     AND (
       not (
         c.talk_duration < 1 
         and c.answered = 1
       )
     ) 
     AND flag != 'q_half_consult' 
     AND c.start_ts >= 1774108800000000 
     AND c.start_ts <= 1776787199999999 
     AND call_flow = 'Queue' 
     AND call_flow_number = '6404' 
   GROUP BY 
     `dst`

2. Aggregate queue call data with the q_half_consult records to build the dataset q_half_consult_data.

   SELECT 
     c.dst, 
     sum(c.abandoned) as abandoned_calls, 
     sum(c.ring_duration) as total_ring_time, 
     sum(
       CASE WHEN h.answered > 0 THEN c.ring_duration ELSE 0 END
     ) as total_answered_ring_time, 
     max(
       c.ring_duration + h.ring_duration
     ) as max_ring_time 
   FROM 
     cdr.cdr_cleaned as c 
     LEFT JOIN (
       SELECT 
         uid, 
         answered, 
         ring_duration 
       FROM 
         cdr.cdr_cleaned as c 
       WHERE 
         c.is_group = 1 
         AND flag != 'q_half_consult' 
         AND c.start_ts >= 1774108800000000 
         AND c.start_ts <= 1776787199999999 
         AND call_flow = 'Queue' 
         AND call_flow_number = '6404'
     ) AS h ON h.uid = c.another_uid 
   WHERE 
     c.is_group = 1 
     AND flag = 'q_half_consult' 
     AND c.start_ts >= 1774108800000000 
     AND c.start_ts <= 1776787199999999 
     AND call_flow = 'Queue' 
     AND call_flow_number = '6404' 
   GROUP BY 
     `c`.`dst`

3. Merge normal_data and q_half_consult_data to generate the final metrics data.

   nornal_data[dst].queue_num = nornal_data[dst].dst
   nornal_data[dst].total_calls = nornal_data[dst].total_calls
   nornal_data[dst].answered_calls = nornal_data[dst].answered_calls 
   nornal_data[dst].abandoned_calls = nornal_data[dst].abandoned_calls 
   nornal_data[dst].missed_calls = nornal_data[dst].missed_calls 
   nornal_data[dst].total_sla_call = nornal_data[dst].total_sla_call 
   nornal_data[dst].total_answered_ring_time += q_half_consult_data[dst].total_answered_ring_time 
   nornal_data[dst].total_ring_time += q_half_consult_data[dst].total_ring_time
   if q_half_consult_data[dst].max_ring_time > nornal_data[dst].max_ring_time {
       nornal_data[dst].max_waiting_time = q_half_consult_data[dst].max_ring_time
   }
   nornal_data[dst].average_waiting_time=nornal_data[dst].total_answered_ring_time/nornal_data[dst].answered_calls
   nornal_data[dst].average_talking_time=(nornal_data[dst].total_talking_time+nornal_data[dst].total_hold_time)/nornal_data[dst].answered_calls
   nornal_data[dst].average_handle_time=(nornal_data[dst].total_answered_ring_time+nornal_data[dst].total_talking_time+nornal_data[dst].total_hold_time)/nornal_data[dst].answered_calls
   nornal_data[dst].average_hold_time=(nornal_data[dst].total_hold_time)/nornal_data[dst].answered_calls
   
   nornal_data[dst].anwsered_rate=nornal_data[dst].answered_calls/nornal_data[dst].total_calls
   nornal_data[dst].missed_rate=nornal_data[dst].missed_calls/nornal_data[dst].total_calls
   nornal_data[dst].abandoned_rate=nornal_data[dst].abandoned_calls/nornal_data[dst].total_calls
   nornal_data[dst].all_call_average_waiting_time=nornal_data[dst].total_ring_time/nornal_data[dst].total_calls

1. Aggregate queue call records excluding q_half_consult records to build the base dataset normal_data.

   SELECT 
     day AS time, 
     Count(*) as total_calls, 
     sum(answered) as answered_calls, 
     sum(missed) as missed_calls, 
     sum(abandoned) as abandoned_calls, 
     max(ring_duration) as max_ring_time, 
     sum(answered_ring_duration) as total_answered_ring_time, 
     sum(ring_duration) as total_ring_time, 
     sum(hold_duration) as total_hold_time, 
     sum(talk_duration) as total_talking_time, 
     sum(member_hold_duration) as total_member_hold_duration, 
     sum(member_answered_count) as total_member_answered, 
     SUM(
       CASE WHEN dst = '6404' 
       AND disposition = 'ANSWERED' 
       AND (
         ring_duration - COALESCE(join_announcement_duration, 0)
       )< 60 THEN 1 ELSE 0 END
     ) AS total_sla_call 
   FROM 
     `cdr`.`cdr_cleaned` 
   WHERE 
     is_group = 1 
     AND flag != 'q_half_consult' 
     AND (
       not (
         ring_duration <= 1 
         and abandoned = 1
       )
     ) 
     AND start_ts >= 1774972800000000 
     AND start_ts <= 1777564799999999 
     AND call_flow = 'Queue' 
     AND call_flow_number = '6404' 
   GROUP BY 
     `time`

2. Aggregate queue call records with the q_half_consult flag to build the dataset q_half_consult_data.

   SELECT 
     day AS time, 
     sum(c.abandoned) as abandoned_calls, 
     sum(c.ring_duration) as total_ring_time, 
     sum(
       CASE WHEN h.answered > 0 THEN c.ring_duration ELSE 0 END
     ) as total_answered_ring_time, 
     max(
       c.ring_duration + h.ring_duration
     ) as max_ring_time 
   FROM 
     cdr.cdr_cleaned as c 
     LEFT JOIN (
       SELECT 
         uid, 
         answered, 
         ring_duration 
       FROM 
         cdr.cdr_cleaned as c 
       WHERE 
         c.is_group = 1 
         AND flag != 'q_half_consult' 
         AND c.start_ts >= 1774972800000000 
         AND c.start_ts <= 1777564799999999 
         AND call_flow = 'Queue' 
         AND call_flow_number = '6404'
     ) AS h ON h.uid = c.another_uid 
   WHERE 
     is_group = 1 
     AND flag = 'q_half_consult' 
     AND start_ts >= 1774972800000000 
     AND start_ts <= 1777564799999999 
     AND call_flow = 'Queue' 
     AND call_flow_number = '6404' 
   GROUP BY 
     `time`

3. Combine normal_data and q_half_consult_data to generate final queue performance metrics.

   nornal_data[time].time = nornal_data[dst].time
   nornal_data[time].total_calls = nornal_data[time].total_calls
   nornal_data[time].answered_calls = nornal_data[time].answered_calls 
   nornal_data[time].abandoned_calls = nornal_data[time].abandoned_calls 
   nornal_data[time].missed_calls = nornal_data[time].missed_calls 
   nornal_data[time].total_sla_call = nornal_data[time].total_sla_call 
   nornal_data[time].total_answered_ring_time += q_half_consult_data[time].total_answered_ring_time 
   nornal_data[time].total_ring_time += q_half_consult_data[time].total_ring_time
   if q_half_consult_data[time].max_ring_time > nornal_data[time].max_ring_time {
       nornal_data[time].max_waiting_time = q_half_consult_data[time].max_ring_time
   }
   nornal_data[time].answered_hold_time = nornal_data[time].total_hold_time
   nornal_data[time].answered_call_time = nornal_data[time].total_answered_ring_time+nornal_data[time].total_talking_time+nornal_data[time].total_hold_time
   nornal_data[time].total_talk_time = nornal_data[time].total_talking_time+nornal_data[time].total_hold_time
   nornal_data[time].answered_waiting_time = nornal_data[time].total_answered_ring_time
   nornal_data[time].average_waiting_time=nornal_data[time].total_answered_ring_time/nornal_data[time].answered_calls
   nornal_data[time].average_talking_time=(nornal_data[time].total_talking_time+nornal_data[time].total_hold_time)/nornal_data[time].answered_calls
   nornal_data[time].average_handle_time=(nornal_data[time].total_answered_ring_time+nornal_data[time].total_talking_time+nornal_data[time].total_hold_time)/nornal_data[time].answered_calls
   nornal_data[time].average_hold_time=(nornal_data[time].total_hold_time)/nornal_data[time].answered_calls
   nornal_data[time].total_waiting_time = nornal_data[time].total_ring_time
   nornal_data[time].sla = nornal_data[time].total_sla_call / nornal_data[time].total_calls
   nornal_data[time].anwsered_rate=nornal_data[time].answered_calls/nornal_data[time].total_calls
   nornal_data[time].missed_rate=nornal_data[time].missed_calls/nornal_data[time].total_calls
   nornal_data[time].abandoned_rate=nornal_data[time].abandoned_calls/nornal_data[time].total_calls
   nornal_data[time].all_call_average_waiting_time=nornal_data[time].total_ring_time/nornal_data[time].total_calls

1. Retrieve missed call records.

   SELECT 
     id, 
     uid, 
     timestamp, 
     src, 
     srcname, 
     dst, 
     dstname, 
     ring_duration, 
     dstnameprefix, 
     disposition, 
     call_flow, 
     call_type 
   FROM 
     `cdr`.`cdr_cleaned` 
   WHERE 
     call_type = 'Inbound' 
     AND is_display = 1 
     AND disposition in ('ABANDONED', 'NO ANSWER', 'BUSY') 
     AND (
       NOT(
         disposition = 'ABANDONED' 
         and ring_duration < 5
       )
     ) 
     AND start_ts >= 1774108800000000 
     AND start_ts <= 1776787199999999 
   ORDER BY 
     uid, 
     timestamp;

2. Remove duplicate records with the same uid from missed call records, keeping only the record with the latest timestamp for each uid, and build the base dataset list_map.

   list_map[uid].uid = miss_call_data.uid
   list_map[uid].timestamp = miss_call_data.timestamp
   list_map[uid].src = miss_call_data.src
   list_map[uid].srcname = miss_call_data.srcname
   list_map[uid].dst= miss_call_data.dst
   list_map[uid].dstname = miss_call_data.dstname 
   list_map[uid].call_to_type = miss_call_data.call_flow
   list_map[uid].unreturn_status = 0
   list_map[uid].return_time = 0
   list_map[uid].miss_call_type = miss_call_data.disposition
   list_map[uid].call_type = miss_call_data.call_type
   list_map[uid].ring_duration = miss_call_data.ring_duration

3. Retrieve answered call records related to caller number.

   SELECT 
     max(timestamp) as timestamp, 
     src, 
     dst, 
     disposition 
   FROM 
     `cdr`.`cdr_cleaned` 
   WHERE 
     timestamp > 1775534367893043 
     AND is_display = 1 
     AND disposition = 'ANSWERED' 
     AND (
       src = '2233123456' 
       or dst = '2233123456'
     ) 
   GROUP BY 
     src, 
     dst, 
     disposition

4. Determine the latest timestamp of answered calls for each caller number.

   //has_return_time_map
   if answered_call_data[src].timestamp > has_return_time_map[src] {
       has_return_time_map[src] = answered_call_data[src].timestamp
   }

5. Retrieve callback records associated with the missed caller number.

   SELECT 
     max(timestamp) as timestamp, 
     src, 
     dst, 
     disposition 
   FROM 
     `cdr`.`cdr_cleaned` 
   WHERE 
     timestamp >= 1775534367893043 
     AND is_display = 1 
     AND (
       (dst = '2233123456') 
       or (
         src = '2233123456' 
         and disposition = 'ANSWERED'
       )
     ) 
   GROUP BY 
     src, 
     dst, 
     disposition

6. Determine the latest callback timestamp for each missed caller number.

   //last_return_time_map
   if last_answered_call_data[src].timestamp > last_return_time_map[src] {
       last_return_time_map[src] = last_answered_call_data[src].timestamp
   }

7. Determine unreturned call status for each missed call record.

   list_map[uid].unreturn_status=2
   src = list_map[uid].src
   //
   if has_return_time_map[src] > list_map[uid].timestamp {
       list_map[uid].unreturn_status = 1
   }
   //
   if last_return_time_map[src] > list_map[uid].timestamp {
       list_map[uid].return_time = last_return_time_map[src]
   }

1. Aggregate call statistics from ring group level to generate the list dataset.

   SELECT 
     dst as ring_group_num, 
     SUM(
       CASE WHEN disposition = 'ANSWERED' THEN 1 ELSE 0 END
     ) AS answered_calls, 
     count(*) as total_calls 
   FROM 
     `cdr`.`cdr_cleaned` 
   WHERE 
     is_group = 1 
     AND start_ts >= 1776441600000000 
     AND start_ts <= 1779119999999999 
     AND call_flow = 'RingGroup' 
     AND call_flow_number in ('6300', '6301', '6302') 
   GROUP BY 
     `dst`

2. Aggregate call statistics from member-level to build the member_list dataset.

   SELECT 
     `dst`, 
     call_flow_number as ring_group_num, 
     SUM(
       CASE WHEN disposition = 'ANSWERED' THEN 1 ELSE 0 END
     ) AS answered_calls, 
     count(*) as total_calls 
   FROM 
     `cdr`.`cdr_cleaned` 
   WHERE 
     is_group = 0 
     AND start_ts >= 1776441600000000 
     AND start_ts <= 1779119999999999 
     AND call_flow = 'RingGroup' 
     AND call_flow_number in ('6300', '6301', '6302') 
   GROUP BY 
     dst, 
     call_flow_number

3. Sort both the ring group list and the member_list by answered rate in descending order, then generate the final structured output.

   {"ring_group_num":"6200","answered_calls":10,"total_calls":20,"member_list":[{"ext_num":"1000","answered_calls":10,"total_calls":20}...]}

1. Retrieve data for calls made or received via trunks.

   1. Retrieve trunk call activity data, excluding multi-party calls.

      SELECT 
        max(day) AS time, 
        max(srctrunk) as srctrunk, 
        max(dsttrunk) as dsttrunk, 
        SUM(talk_duration + hold_duration) as total_talk_duration, 
        MAX(concurrent) as max_concurrent_call, 
        uid, 
        0 as total_calls 
      FROM 
        `cdr`.`cdr_cleaned` 
      WHERE 
        (
          srctrunk in (
            '97账号中继注册段238', '235点对点kk'
          ) 
          or dsttrunk in (
            '97账号中继注册段238', '235点对点kk'
          )
        ) 
        AND is_display = 1 
        AND (
          NOT(
            dstnameprefix in ('Queue', 'RingGroup') 
            and disposition = 'ANSWERED'
          )
        ) 
        AND NOT(srcname = 'Conference Call') 
        AND start_ts >= 1774972800000000 
        AND start_ts <= 1777564799999999 
      GROUP BY 
        `uid`;

   2. Calculate call volume, total talk duration, and maximum concurrent calls for the same trunk within the same time period, excluding multi-party calls.
   3. Retrieve trunk call activity data, including multi-party calls.

      SELECT 
        max(day) AS time, 
        srctrunk, 
        dsttrunk, 
        SUM(talk_duration + hold_duration) as total_talk_duration, 
        MAX(concurrent) as max_concurrent_call, 
        uid, 
        COUNT(*) as total_calls 
      FROM 
        `cdr`.`cdr_cleaned` 
      WHERE 
        (
          srctrunk in (
            '97账号中继注册段238', '235点对点kk'
          ) 
          or dsttrunk in (
            '97账号中继注册段238', '235点对点kk'
          )
        ) 
        AND is_display = 1 
        AND (
          NOT(
            dstnameprefix in ('Queue', 'RingGroup') 
            and disposition = 'ANSWERED'
          )
        ) 
        AND srcname = 'Conference Call' 
        AND start_ts >= 1774972800000000 
        AND start_ts <= 1777564799999999 
      GROUP BY 
        srctrunk, 
        dsttrunk, 
        uid;

   4. Calculate call volume, total talk duration, and maximum concurrent calls for the same trunk within the same time period, including multi-party calls.
2. Retrieve call activity data for the entire system, including internal calls, inbound calls, and outbound calls across all trunks.
   1. Retrieve device call activity data, excluding audio conference and paging.

      SELECT 
        max(day) AS time, 
        uid, 
        SUM(talk_duration + hold_duration) as total_talk_duration, 
        MAX(concurrent) as max_concurrent_call 
      FROM 
        `cdr`.`cdr_cleaned` 
      WHERE 
        (
          NOT(
            dstnameprefix in ('Queue', 'RingGroup') 
            and disposition = 'ANSWERED'
          )
        ) 
        AND call_flow != 'Audio Conference' 
        AND call_flow != 'Paging' 
        AND dstnameprefix != 'Paging' 
        AND start_ts >= 1774972800000000 
        AND start_ts <= 1777564799999999 
      GROUP BY 
        `uid`;

   2. Calculate call volume, total talk duration, and maximum concurrent calls within the same time period, excluding multi-party calls.
   3. Query call data for audio conference calls and paging calls.

      SELECT 
        uid, 
        src, 
        dst, 
        timestamp, 
        talk_duration, 
        hold_duration, 
        concurrent, 
        dstnameprefix, 
        call_flow, 
        call_type, 
        month, 
        day, 
        hour 
      FROM 
        `cdr`.`cdr_cleaned` 
      WHERE 
        (
          NOT(
            dstnameprefix in ('Queue', 'RingGroup') 
            and disposition = 'ANSWERED'
          )
        ) 
        AND (
          call_flow = 'Audio Conference' 
          OR call_flow = 'Paging' 
          OR dstnameprefix = 'Paging'
        ) 
        AND start_ts >= 1774972800000000 
        AND start_ts <= 1777564799999999 
      ORDER BY 
        timestamp asc, 
        dstnameprefix desc 
      LIMIT 
        5000;

   4. Apply the following aggregation rules:

      • Paging Calls: Records with the same uid are treated as a single call; device call volume is incremented by 1 per record; call duration is taken only from the Paging record (talk_duration + hold_duration where dstnameprefix = Paging); maximum concurrent calls are calculated based on the paging record.
      • Audio Conference Calls: Records with the same uid_src_dst are treated as a single call; device call volume is incremented by 1 per record; call duration is the sum of talk_duration and hold_duration; maximum concurrent calls are calculated based on the aggregated audio conference call records.
   5. Calculate call volume, total talk duration, and maximum concurrent calls for device calls within the same time period, including multi-party calls.
3. Retrieve data for internal calls.
   1. Retrieve internal calls data, excluding audio conference calls and paging calls.

      SELECT 
        max(day) AS time, 
        uid, 
        SUM(talk_duration + hold_duration) as total_talk_duration, 
        MAX(concurrent) as max_concurrent_call 
      FROM 
        `cdr`.`cdr_cleaned` 
      WHERE 
        call_type = 'Internal' 
        AND src != 'PBX' 
        AND (
          NOT(
            dstnameprefix in ('Queue', 'RingGroup') 
            and disposition = 'ANSWERED'
          )
        ) 
        AND call_flow != 'Audio Conference' 
        AND call_flow != 'Paging' 
        AND dstnameprefix != 'Paging' 
        AND start_ts >= 1774972800000000 
        AND start_ts <= 1777564799999999 
      GROUP BY 
        `uid`;

   2. Calculate call volume, total talk duration, and maximum concurrent calls for internal calls within the same time period, excluding multi-party calls.
   3. Query call data for audio conference calls and paging calls.

      SELECT 
        uid, 
        src, 
        dst, 
        timestamp, 
        talk_duration, 
        hold_duration, 
        concurrent, 
        dstnameprefix, 
        call_flow, 
        call_type, 
        month, 
        day, 
        hour 
      FROM 
        `cdr`.`cdr_cleaned` 
      WHERE 
        call_type = 'Internal' 
        AND (
          NOT(
            dstnameprefix in ('Queue', 'RingGroup') 
            and disposition = 'ANSWERED'
          )
        ) 
        AND (
          call_flow = 'Audio Conference' 
          OR call_flow = 'Paging' 
          OR dstnameprefix = 'Paging'
        ) 
        AND start_ts >= 1774972800000000 
        AND start_ts <= 1777564799999999 
      ORDER BY 
        timestamp asc, 
        dstnameprefix desc 
      LIMIT 
        5000;

   4. Apply the following aggregation rules:

      • Paging Calls: Records with the same uid are treated as a single call; internal call volume is incremented by 1 per record; call duration is taken only from the Paging record (talk_duration + hold_duration where dstnameprefix = Paging); maximum concurrent calls are calculated based on the paging record.
      • Audio Conference Calls: Records with the same uid_src_dst are treated as a single call; internal call volume is incremented by 1 per record; call duration is the sum of talk_duration and hold_duration; maximum concurrent calls are calculated based on the aggregated audio conference call records.
   5. Calculate call volume, total talk duration, and maximum concurrent calls for internal calls within the same time period, including multi-party calls.

SELECT 
  usage_type, 
  SUM(duration) as total_duration 
FROM 
  `pbx`.`ai_usage_record` 
WHERE 
  (
    usage_type in (
      'call_transcription', 'voicemail_transcription'
    )
  ) 
  AND (timestamp >= 1767196800) 
  AND (timestamp < 1779206399) 
GROUP BY 
  usage_type