Дашборды, Telegram-боты, сайты и графика для игр

-- Доля обращений, закрытых в срок, по филиалам за период
WITH scoped AS (
  SELECT t.branch_id, t.created_at, t.closed_at
  FROM tickets t
  JOIN branches b ON b.id = t.branch_id
  WHERE t.created_at >= now() - interval '365 days'
    AND b.division_id = coalesce(:division_id, b.division_id)
    AND t.status_id IN (5, 6)              -- закрыто или отклонено
),
sla AS (
  SELECT branch_id,
         count(*)                                              AS total,
         count(*) FILTER (WHERE closed_at - created_at
                               <= interval '24 hours') AS in_time
  FROM scoped
  GROUP BY branch_id
)
SELECT b.name,
       s.total,
       round(s.in_time::numeric / nullif(s.total, 0), 3)   AS sla_share,
       rank() OVER (ORDER BY s.in_time::numeric / s.total) AS worst_rank
FROM sla s
JOIN branches b ON b.id = s.branch_id
ORDER BY worst_rank
LIMIT 10;