Help Scout

WITH customer_messages AS (
    SELECT
        conversation_id,
        created_at AS customer_message_at
    FROM helpscout.conversation_thread_history
    WHERE type = "customer"
),
user_messages AS (
    SELECT
        conversation_id,
        created_at AS user_message_at
    FROM helpscout.conversation_thread_history
    WHERE type = "message" AND state = "published"
),
last_customer_messages AS (
    SELECT
        customer_messages.conversation_id,
        MAX(customer_message_at) AS last_customer_message_that_received_a_response_at
    FROM customer_messages
    JOIN user_messages ON user_messages.conversation_id = customer_messages.conversation_id
        AND user_message_at > customer_message_at
),
resolutions AS (
    SELECT
        user_messages.conversation_id,
        MIN(user_message_at) AS resolved_at
    FROM last_customer_messages
    JOIN user_messages ON user_messages.conversation_id = last_customer_messages.conversation_id
        AND user_message_at > last_customer_message_that_received_a_response_at
    GROUP BY 1
),
conversation_resolution_times AS (
    SELECT
    conversation_id,
    conversation.created_at,
    resolved_at,
    TIMESTAMP_DIFF(resolved_at, conversation.created_at, SECOND) AS resolution_time
    FROM helpscout.conversation_history
    JOIN resolutions ON resolutions.conversation_id = conversation.id
    WHERE conversation.closed_at IS NOT NULL
)
SELECT
    TIMESTAMP_TRUNC(resolved_at, MONTH) AS resolution_month,
    AVG(resolution_time) / 3600 AS avg_resolution_time_in_hours
FROM conversation_resolution_times
GROUP BY 1
ORDER BY 1 DESC;