# 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.

   ```sql
   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.

   ```sql
   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.

   ```sql
   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.

   ```sql
   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.

   ```sql
   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.

   ```sql
   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.

```sql
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.

```sql
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.

   ```sql
   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.

   ```sql
   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.

   ```sql
   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.

   ```sql
   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.

   ```sql
   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.

   ```text
   agent_talk_time_map[queue_agent] = total_talking_time
   ```

9. Aggregate the statistics at queue and agent level.

   ```text
   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.

    ```text
    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.

   ```sql
   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.

   ```sql
   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.

   ```text
   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.

```sql
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.

   ```sql
   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.

   ```sql
   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.

   ```text
   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.

   ```sql
   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.

   ```sql
   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.

   ```text
   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.

   ```sql
   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.

   ```text
   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.

   ```sql
   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.

   ```text
   // 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.

   ```sql
   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.

   ```text
   // 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.

   ```text
   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.

   ```sql
   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.

   ```sql
   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.

   ```text
   {"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.

      ```sql
      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.

      ```sql
      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.

      ```sql
      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.

      ```sql
      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.

      ```sql
      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.

      ```sql
      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`.

```sql
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
```