Call Report Calculations with Multiple SQL Queries

Data Connector exposes your Cloud Voice call database so you can build dashboards in business-intelligence tools such as Grafana and Power BI. The report templates supplied for those platforms are each limited to a single query per report, which covers most cases. A few reports, though, are assembled from several result sets that must be combined after the queries run. This page is the reference for those reports: for each one it lists the SQL to execute and the calculations to apply to the returned rows so you can reproduce the report in your own BI tooling.

A few conventions used throughout:

• Queries read from cdr.cdr_cleaned (cleaned Call Detail Records, or CDRs) or queue_log (queue events). pbx.ai_usage_record holds transcription usage.
• Acronyms you will meet below: CDR (Call Detail Record, one row per call leg), QCB (Queue Callback, where a caller keeps their place in line and is called back instead of waiting on hold), and SLA (Service Level Agreement, here the share of calls answered within the target wait time).
• The date ranges and the extension, queue, and trunk identifiers shown below are examples. Substitute the values for your own report.

Caution

Timestamps use different scales in different tables. start_ts and timestamp in cdr_cleaned are microsecond epoch values (16 digits), while timestamp in queue_log and ai_usage_record is a second epoch value (10 digits). Mixing the two scales in a WHERE clause returns no rows, so always match the scale of the table you are querying.

Extension Call Statistics report

This report reflects each extension’s total activity, so you retrieve outbound and inbound statistics separately and then merge the two result sets by extension number.

1. Retrieve outbound call statistics per extension.

   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:

   • ext_num: Extension number.
   • answered: Calls the extension answered.
   • total_no_answered: Calls the extension did not answer.
   • busy: Calls that arrived while the extension was busy.
   • abandoned: Calls the caller dropped before reaching the extension.
   • vm: Calls routed to the extension’s voicemail.
   • failed: Outbound calls the extension was unable to complete.
   • ring_duration: Total time from call start to answer.
   • talk_duration: Total time from answer to call end.

2. Retrieve inbound call statistics per extension.

   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 same set of columns as the outbound query (ext_num, answered, total_no_answered, busy, abandoned, vm, failed, ring_duration, talk_duration), calculated for inbound calls.

3. Merge the outbound and inbound result sets on ext_num to produce the complete per-extension statistics.

Extension Call Activity report

This report breaks each extension’s activity down by time period. As with the statistics report, you retrieve outbound and inbound figures separately and merge them, here by both extension number and time period.

1. Retrieve outbound call statistics per extension.

   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 time (the period), ext_num, and the same activity columns as the Extension Call Statistics report: answered, total_no_answered, busy, abandoned, failed, vm, ring_duration, and talk_duration.

2. Retrieve inbound call statistics per extension.

   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 same columns as the outbound query, calculated for inbound calls.

3. Merge the outbound and inbound result sets by extension number and time period to produce the complete report.

Agent Call Summary report

This report summarizes each agent’s queue activity and average call times. Retrieve inbound and outbound queue statistics separately, merge them per agent, then derive the averages.

1. Retrieve inbound queue call statistics per agent.

   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:

   • agent: Agent number.
   • answered_call: Inbound queue calls the agent answered.
   • duration: Time the agent spent handling inbound queue calls, from ring to hang-up.
   • talking_time: Time the agent spent talking on inbound calls.
   • hold_time: Time the agent held inbound calls.
   • waiting_time: Time inbound queue calls waited before the agent answered.

2. Retrieve outbound queue call statistics per agent.

   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:

   • agent: Agent number.
   • total_call: Outbound calls the agent placed.
   • answered_call: Outbound calls the agent answered.
   • duration: Time the agent spent handling outbound queue calls, from ring to hang-up.
   • talking_time: Time the agent spent talking on outbound calls.
   • hold_time: Time the agent held outbound calls.
   • waiting_time: Time outbound queue calls waited before being answered.

3. Merge the inbound and outbound statistics by agent extension number to build the combined totals:

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

4. Calculate each agent’s average durations:

   • 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)

Agent Login Activity report

This report shows when agents logged in to and out of a queue. Retrieve the login and logout events from the queue log.

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

The query returns:

• agent: Agent number.
• timestamp: When the agent logged in to or out of the queue.
• event:
  • ADDMEMBER: The agent logged in to the queue.
  • REMOVEMEMBER: The agent logged out of the queue.

Note

Total login time is the difference between the REMOVEMEMBER and ADDMEMBER timestamps.

Agent Pause Activity report

This report shows when agents paused and resumed service. Retrieve the pause and unpause events from the queue log.

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

The query returns:

• agent: Agent number.
• timestamp: When the agent paused or resumed service.
• data1: Pause reason.
• event:
  • PAUSE: The agent paused service.
  • UNPAUSE: The agent resumed service.

Note

Total pause time is the difference between the UNPAUSE and PAUSE timestamps.

Agent Performance report

This report aggregates queue call records from several datasets, then merges them into final per-agent metrics.

1. Aggregate queue performance data, excluding q_half_consult records.

   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
   FROM
     cdr.cdr_cleaned as c
   WHERE
     c.is_group = 1
     AND (
       not (
         c.ring_duration <= 1
         and c.abandoned = 1
       )
     )
     AND flag != 'q_half_consult'
     AND c.start_ts >= 1776614400000000
     AND c.start_ts <= 1776700799999999
     AND call_flow = 'Queue'
     AND call_flow_number = '6404'
   GROUP BY
     `dst`

   The query returns:

   • dst: Queue number.
   • total_calls: Calls the queue received.
   • answered_calls: Calls the queue answered.
   • missed_calls: Calls the queue missed.
   • abandoned_calls: Calls the caller abandoned.
   • max_ring_time: Longest time a caller waited in the queue before reaching an agent.
   • total_answered_ring_time: Time from call start to answer.
   • total_ring_time: Time from call start to answer.
   • total_hold_time: Time the call was held.
   • total_talking_time: Time from answer to hang-up.
   • total_member_hold_duration: Time agents held calls.
   • total_member_answered: Calls agents answered.

2. Aggregate the queue performance records that carry the q_half_consult flag.

   SELECT
     c.dst,
     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 >= 1776614400000000
         AND c.start_ts <= 1776700799999999
         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 >= 1776614400000000
     AND c.start_ts <= 1776700799999999
     AND call_flow = 'Queue'
     AND call_flow_number = '6404'
   GROUP BY
     `c`.`dst`

   The query returns:

   • dst: Queue number.
   • total_ring_time: Time from call start to answer.
   • total_answered_ring_time: Time from call start to answer, for answered calls.
   • max_ring_time: Longest time a caller waited in the queue before reaching an agent.

3. Merge the two datasets into queue-level totals:

   • total_answered_ring_time = sum of total_answered_ring_time from both datasets
   • total_ring_time = sum of total_ring_time from both datasets
   • max_ring_time = maximum max_ring_time across both datasets

4. Calculate the queue-level metrics from the aggregated data:

   • average_waiting_time = total_answered_ring_time / answered_calls
   • average_talking_time = (total_talking_time + total_hold_time) / answered_calls
   • average_handle_time = (total_answered_ring_time + total_talking_time + total_hold_time) / answered_calls
   • average_hold_time = total_hold_time / answered_calls
   • answered_rate = answered_calls / total_calls
   • missed_rate = missed_calls / total_calls
   • abandoned_rate = abandoned_calls / total_calls
   • all_call_average_waiting_time = total_ring_time / total_calls

5. Aggregate agent-level records, excluding callback-related calls and invalid abandoned records.

   SELECT
     c.uid,
     c.call_flow_number as queue,
     c.dst as agent,
     sum(c.answered) as answered_calls,
     sum(c.missed) as missed_calls,
     max(c.ring_duration) as max_ring_time,
     sum(c.answered_ring_duration) as total_answered_ring_time,
     sum(c.ring_duration) as total_ring_time,
     sum(c.talk_duration) as total_talking_time,
     sum(c.hold_duration) as total_hold_time
   FROM
     cdr.cdr_cleaned as c
     LEFT JOIN cdr.cdr_cleaned AS h ON h.uid = c.uid
     AND h.is_group = 1
     AND h.ring_duration <= 1
     AND h.disposition = 'ABANDONED'
   WHERE
     c.call_flow IN ('Queue', 'QCB')
     AND c.call_flow_number = '6404'
     AND c.dst IN ('1000', '1001')
     AND c.is_group != 1
     AND c.disposition != 'ABANDONED'
     AND c.src != 'PBX'
     AND h.uid IS NULL
     AND c.start_ts >= 1776614400000000
     AND c.start_ts <= 1776700799999999
   GROUP BY
     c.call_flow_number,
     c.uid,
     c.dst

   The query returns:

   • uid: The CDR’s unique ID.
   • queue: Queue number.
   • agent: Agent number.
   • answered_calls: Calls the agent answered.
   • missed_calls: Calls the agent missed.
   • max_ring_time: Longest ring time before a call reached the agent.
   • total_answered_ring_time: Time from call start to answer, for answered calls.
   • total_ring_time: Time from call start to answer.
   • total_talking_time: Time from answer to hang-up.
   • total_hold_time: Time the agent held calls.

6. Aggregate the agent-level records that relate to callback calls.

   SELECT
     c.uid,
     c.call_flow_number as queue,
     c.dst as agent,
     sum(c.answered) as answered_calls,
     sum(c.missed) as missed_calls,
     max(c.ring_duration) as max_ring_time,
     sum(c.answered_ring_duration) as total_answered_ring_time,
     sum(c.ring_duration) as total_ring_time,
     sum(c.talk_duration) as total_talking_time,
     sum(c.hold_duration) as total_hold_time
   FROM
     cdr.cdr_cleaned as c
     LEFT JOIN cdr.cdr_cleaned AS h ON h.uid = c.uid
     AND h.is_group = 1
     AND h.ring_duration <= 1
     AND h.disposition = 'ABANDONED'
   WHERE
     c.call_flow IN ('Queue', 'QCB')
     AND c.call_flow_number = '6404'
     AND c.dst IN ('1000', '1001')
     AND c.is_group != 1
     AND c.disposition != 'ABANDONED'
     AND c.src = 'PBX'
     AND h.uid IS NULL
     AND c.start_ts >= 1776614400000000
     AND c.start_ts <= 1776700799999999
   GROUP BY
     c.call_flow_number,
     c.uid,
     c.dst

   The query returns the same columns as the previous step.

7. Retrieve agent-level callback (QCB) statistics.

   SELECT
     call_flow_number as queue,
     src as agent,
     count(*) as total_calls,
     sum(answered) as answered_calls,
     sum(missed) as missed_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(duration) as total_talking_time,
     sum(hold_duration) as total_hold_time
   FROM
     `cdr`.`cdr_cleaned`
   WHERE
     call_flow_number = '6404'
     AND is_qcb = 1
     AND src IN ('1000', '1001')
     AND start_ts >= 1776614400000000
     AND start_ts <= 1776700799999999
     AND call_flow = ('Queue')
   GROUP BY
     call_flow_number,
     src

   The query returns queue, agent, total_calls, answered_calls, missed_calls, max_ring_time, total_answered_ring_time, total_ring_time, total_talking_time, and total_hold_time for the callback calls.

8. Add the callback talking time into the agent statistics.

   agent_talk_time_map[queue_agent] = total_talking_time

9. Aggregate the statistics at queue and agent level.

   agentMap[queue_agent].dst = agent
   agentMap[queue_agent].total_calls += answered_calls + missed_calls
   agentMap[queue_agent].answered_calls += answered_calls
   agentMap[queue_agent].missed_calls += missed_calls
   agentMap[queue_agent].total_ring_time += total_ring_time
   
   agentMap[queue_agent].total_talking_time += agent_talk_time_map[queue_agent]  // add only once
   agent_answered_ring_duration[queue_uid_agent] = total_answered_ring_time
   if answered_calls > 0 {
       agent_answered[queue_uid_agent] = 1
   }

10. Calculate the agent-level metrics.

    answered_ring_time = total_answered_ring_time
    if answered_calls > 0 || agent_answered[queue_uid_agent] == 1 {
        answered_ring_time = total_ring_time
    }
    agentMap.total_calls += answered_calls + missed_calls
    agentMap.answered_calls += answered_calls
    agentMap.missed_calls += missed_calls
    agentMap.total_answered_ring_time += answered_ring_time
    agentMap.total_ring_time += total_ring_time
    agentMap.total_talking_time += total_talking_time + total_hold_time
    
    agent_answered_ring_duration[queue_uid_agent] += answered_ring_time
    
    agentMap.max_ring_time = max(agent_answered_ring_duration[queue_agent]...)
    
    agentMap.avg_talking_time = agentMap.total_talking_time / agentMap.answered_calls
    agentMap.avg_waiting_time = agentMap.total_answered_ring_time / agentMap.answered_calls
    agentMap.miss_rate = agentMap.missed_calls / agentMap.total_calls

Queue AVG Wait and Talk Time report

This report tracks average wait and talk times per period. Aggregate the queue call data and calculate the derived metrics.

1. Aggregate the queue call data, excluding q_half_consult records, into the normal_data dataset.

   SELECT
     DAY(datetime) AS time,
     Count(*) as total_calls,
     sum(answered) as answered_calls,
     sum(answered_ring_duration) as total_answered_ring_time,
     sum(ring_duration) as total_ring_time,
     sum(talk_duration + hold_duration) as total_talking_time
   FROM
     `cdr`.`cdr_cleaned`
   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`

   The query returns:

   • time: When the call was received.
   • total_calls: Calls the queue received.
   • answered_calls: Calls the queue answered.
   • total_answered_ring_time: Time from call start to answer.
   • total_ring_time: Time from call start to answer.
   • total_talking_time: Time from answer to hang-up.

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

   SELECT
     day AS time,
     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`

   The query returns:

   • time: When the call was received.
   • total_ring_time: Time from call start to answer.
   • total_answered_ring_time: Time from call start to answer.
   • max_ring_time: Longest time a caller waited in the queue.

3. Merge normal_data and q_half_consult_data into the final metrics.

   normal_data[time].time
   normal_data[time].total_calls
   normal_data[time].answered_calls
   normal_data[time].total_talking_time
   normal_data[time].total_answered_ring_time += q_half_consult_data[time].total_answered_ring_time
   normal_data[time].total_ring_time += q_half_consult_data[time].total_ring_time
   normal_data[time].average_waiting_time = normal_data[time].total_answered_ring_time / normal_data[time].answered_calls
   normal_data[time].average_talking_time = normal_data[time].total_talking_time / normal_data[time].answered_calls
   normal_data[time].all_call_average_waiting_time = normal_data[time].total_ring_time / normal_data[time].total_calls

Queue Callback Summary report

This report summarizes queue callbacks. Aggregate the queue call records by the callback-related fields.

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`

The query returns:

• dst: Queue number.
• qcb_failed_count: Failed callbacks.
• qcb_success_count: Successful callbacks.
• qcb_count: Callbacks that callers requested successfully.
• total_count: Calls the queue received.

Queue Performance report

This report aggregates queue call data and derives the performance metrics.

1. Aggregate the queue call data, excluding q_half_consult records, into the normal_data dataset.

   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 the queue call data with the q_half_consult records into the q_half_consult_data dataset.

   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 into the final metrics.

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

Queue Performance Activity report

This report aggregates queue call data across call scenarios and computes performance metrics per period.

1. Aggregate the queue call records, excluding q_half_consult records, into the base normal_data dataset.

   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 the queue call records with the q_half_consult flag into the q_half_consult_data dataset.

   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 into the final queue performance metrics.

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

Unreturned Missed Call report

This report identifies missed calls that have not been returned. Query the missed-call records, then correlate them with answered and callback records to decide whether each missed call was returned.

1. Retrieve the 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 that share a uid, keeping only the record with the latest timestamp for each uid, and build the base list_map dataset.

   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 the answered call records related to the 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 answered-call timestamp 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 the 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 the unreturned 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]
   }

Ring Group Statistics report

This report aggregates ring group activity at both the group level and the member level, then combines the two into the final structured result.

1. Aggregate call statistics at the ring group level to build 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 at the 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 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}...]}

PBX Call Activity report

This report aggregates call activity from three sources, trunk calls, device calls, and internal calls. Each dataset is calculated separately, then used for the overall analysis.

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

   a. Retrieve trunk call activity, 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 (
         'Trunk A', 'Trunk B'
       )
       or dsttrunk in (
         'Trunk A', 'Trunk B'
       )
     )
     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`;

   b. Calculate the call volume, total talk duration, and maximum concurrent calls for the same trunk within the same period, excluding multi-party calls.

   c. Retrieve trunk call activity, 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 (
         'Trunk A', 'Trunk B'
       )
       or dsttrunk in (
         'Trunk A', 'Trunk B'
       )
     )
     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;

   d. Calculate the call volume, total talk duration, and maximum concurrent calls for the same trunk within the same period, including multi-party calls.

2. Retrieve system-wide call activity, covering internal, inbound, and outbound calls across all trunks.

   a. Retrieve device call activity, 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`;

   b. Calculate the call volume, total talk duration, and maximum concurrent calls within the same period, excluding multi-party calls.

   c. Query the call data for audio conference 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;

   d. Apply these aggregation rules:

   • Paging calls: Treat records that share a uid as one call; add 1 to device call volume per record; take the call duration only from the Paging record (talk_duration + hold_duration where dstnameprefix = Paging); base the maximum concurrent calls on the paging record.
   • Audio conference calls: Treat records that share a uid_src_dst as one call; add 1 to device call volume per record; take the call duration as the sum of talk_duration and hold_duration; base the maximum concurrent calls on the aggregated audio conference records.

   e. Calculate the call volume, total talk duration, and maximum concurrent calls for device calls within the same period, including multi-party calls.

3. Retrieve data for internal calls.

   a. Retrieve internal call data, excluding audio conference 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`;

   b. Calculate the call volume, total talk duration, and maximum concurrent calls for internal calls within the same period, excluding multi-party calls.

   c. Query the call data for audio conference 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;

   d. Apply these aggregation rules:

   • Paging calls: Treat records that share a uid as one call; add 1 to internal call volume per record; take the call duration only from the Paging record (talk_duration + hold_duration where dstnameprefix = Paging); base the maximum concurrent calls on the paging record.
   • Audio conference calls: Treat records that share a uid_src_dst as one call; add 1 to internal call volume per record; take the call duration as the sum of talk_duration and hold_duration; base the maximum concurrent calls on the aggregated audio conference records.

   e. Calculate the call volume, total talk duration, and maximum concurrent calls for internal calls within the same period, including multi-party calls.

Transcription Usage Details report

This report reports total transcription usage grouped by usage type. Retrieve the usage totals from pbx.ai_usage_record.

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