avgresponse.sql

WITH OrderedMessages AS (
    SELECT *,
           LAG(`from`) OVER (ORDER BY `timestamp`) AS PrevFrom
    FROM Conversations
),
AlternatingMessages AS (
    SELECT `timestamp`, `from`, `message`
    FROM OrderedMessages
    WHERE NOT (`from` = 'chatbot' AND PrevFrom = 'chatbot')
),
PairedMessages AS (
    SELECT 
        `timestamp`,
        `from`,
        `message`,
        LEAD(`timestamp`) OVER (ORDER BY `timestamp`) AS NextTimestamp,
        LEAD(`from`) OVER (ORDER BY `timestamp`) AS NextFrom
    FROM AlternatingMessages
)

SELECT 
    AVG(TIMESTAMPDIFF(SECOND, `timestamp`, NextTimestamp)) AS AvgResponseTimeInSeconds
FROM PairedMessages
WHERE `from` = 'user' AND NextFrom = 'chatbot';