Условие

Дана нормализованная схема данных контактного центра:

task(task_rk, task_stage_id, source_system_cd, create_dttm, finish_dttm) — задания.
call(wo_hit_rk, wo_task_rk, wo_queue_id, agent_login, wo_employee_rk, finish_dttm, duration_sec) — коммуникации.
action(hit_rk, hit_status_result_id) — результаты коммуникаций.
product(hit_rk, hid, using_flg) — продукт по коммуникации.
result(hit_status_result_id, hit_status_result_desc) — расшифровки результатов.
queue(queue_id, queue_desc) — очереди.
emp_x_org_gr(employee_rk, org_group_rk) — сотрудник в группе.
group(org_group_rk, org_management_rk, group_nm), mngmnt(org_management_rk, management_nm) — оргструктура.
horoscope(agent_login, horoscope) — пасхалка для junior-задач.

Задачи (типичные):

Time-to-completion: распределение finish_dttm − create_dttm для заданий по source_system_cd.
Эффективность операторов: успешные звонки (с using_flg = 1) на оператора.
Очереди с самым долгим средним временем обработки.
Конверсия из задания в продаж.

Решение

1. Распределение времени обработки задания

SELECT source_system_cd,
       PERCENTILE_CONT(0.5) WITHIN GROUP
         (ORDER BY EXTRACT(EPOCH FROM (finish_dttm - create_dttm)) / 3600) AS median_hours,
       AVG(EXTRACT(EPOCH FROM (finish_dttm - create_dttm)) / 3600) AS mean_hours,
       COUNT(*) AS total
FROM task
WHERE finish_dttm IS NOT NULL
GROUP BY source_system_cd
ORDER BY total DESC;

2. Эффективность операторов

WITH calls AS (
  SELECT c.agent_login,
         c.wo_employee_rk,
         c.wo_hit_rk,
         c.duration_sec,
         a.hit_status_result_id,
         p.using_flg
  FROM call c
  LEFT JOIN action a USING (hit_rk -> wo_hit_rk_join)
  LEFT JOIN product p ON p.hit_rk = c.wo_hit_rk
)
-- В реальности: c.wo_hit_rk = a.hit_rk
SELECT agent_login,
       COUNT(*)                                             AS calls,
       SUM(CASE WHEN using_flg = 1 THEN 1 ELSE 0 END)        AS sales,
       AVG(duration_sec)                                     AS avg_duration,
       SUM(CASE WHEN using_flg = 1 THEN 1 ELSE 0 END) * 1.0
         / NULLIF(COUNT(*), 0)                              AS conv_rate
FROM call c
LEFT JOIN product p ON p.hit_rk = c.wo_hit_rk
GROUP BY agent_login
HAVING COUNT(*) >= 50
ORDER BY conv_rate DESC;

3. Очереди с долгим временем

SELECT q.queue_id, q.queue_desc,
       AVG(c.duration_sec)                                AS avg_dur,
       COUNT(*)                                            AS calls
FROM call c
JOIN queue q ON q.queue_id = c.wo_queue_id
GROUP BY q.queue_id, q.queue_desc
ORDER BY avg_dur DESC
LIMIT 10;

4. Конверсия задание → продажа

Воронка task → call → action → product:

WITH t_call AS (
  SELECT t.task_rk,
         COUNT(c.wo_hit_rk) AS n_calls,
         SUM(CASE WHEN p.using_flg = 1 THEN 1 ELSE 0 END) AS n_sales
  FROM task t
  LEFT JOIN call    c ON c.wo_task_rk = t.task_rk
  LEFT JOIN product p ON p.hit_rk = c.wo_hit_rk
  GROUP BY t.task_rk
)
SELECT
  COUNT(*) AS total_tasks,
  SUM(CASE WHEN n_calls > 0 THEN 1 ELSE 0 END) AS tasks_with_call,
  SUM(CASE WHEN n_sales > 0 THEN 1 ELSE 0 END) AS tasks_with_sale,
  SUM(CASE WHEN n_sales > 0 THEN 1 ELSE 0 END) * 100.0 / COUNT(*) AS conv_pct
FROM t_call;

5. По группам / управлениям

SELECT m.management_nm, g.group_nm,
       COUNT(*) AS calls,
       SUM(CASE WHEN p.using_flg = 1 THEN 1 ELSE 0 END) AS sales
FROM call c
JOIN emp_x_org_gr eg ON eg.employee_rk = c.wo_employee_rk
JOIN "group"      g  ON g.org_group_rk  = eg.org_group_rk
JOIN mngmnt       m  ON m.org_management_rk = g.org_management_rk
LEFT JOIN product p  ON p.hit_rk = c.wo_hit_rk
GROUP BY ROLLUP(m.management_nm, g.group_nm);

Подводные камни

finish_dttm IS NULL в task — задание не закончено. Не include в medians, иначе systematic bias к коротким.
Несколько call на task: один task может иметь много call — LEFT JOIN raises rows; помечайте через DISTINCT или агрегируйте перед join.
using_flg: «начал пользоваться продуктом» != «купил». Уточняйте.
duration_sec = 0: пропущенный звонок или отказ. Часто исключают из средних.
source_system_cd имеет малую кардинальность (FW, GI, CM) → можно строить отдельно.
group keyword: в Postgres — reserved word, обязательно "group".

Эталонный ответ

Каркас: task → call → product/action с агрегацией по разным группировкам (system, agent, queue, management). Проверки: IS NOT NULL, LEFT JOIN, NULLIF для деления.

Иннополис — анализ задач и коммуникаций контактного центра