SELECT
    agent_name,
    count() AS total_conversations,
    countIf(status = 'CLOSED') AS closed,
    round(avg(if(finished_at IS NOT NULL,
        dateDiff('minute', created_at, finished_at), NULL)), 1) AS avg_resolution_time_min
FROM client_analytics.fact_conversations
WHERE created_at >= now() - INTERVAL 30 DAY
GROUP BY agent_name
ORDER BY total_conversations DESC

SELECT
    team_name,
    count() AS conversations,
    -- You can change 120 to whatever threshold you need (in seconds)
    round(countIf(first_response_sec <= 120) * 100.0 / count(), 1) AS sla_120s_pct,
    round(countIf(first_response_sec <= 60) * 100.0 / count(), 1) AS sla_60s_pct,
    round(avg(first_response_sec), 0) AS avg_response_sec
FROM client_analytics.fact_conversations
WHERE created_at >= now() - INTERVAL 7 DAY
  AND first_response_sec IS NOT NULL
GROUP BY team_name
ORDER BY sla_120s_pct DESC

SELECT
    toDate(created_at) AS day,
    inbound_outbound,
    count() AS total
FROM client_analytics.fact_conversations
WHERE created_at >= now() - INTERVAL 30 DAY
GROUP BY day, inbound_outbound
ORDER BY day DESC