Created
May 14, 2024 15:54
-
-
Save naranjja/5aaaa4bac161d32da046c14a9dd317da to your computer and use it in GitHub Desktop.
This file contains bidirectional Unicode text that may be interpreted or compiled differently than what appears below. To review, open the file in an editor that reveals hidden Unicode characters.
Learn more about bidirectional Unicode characters
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'; |
Sign up for free
to join this conversation on GitHub.
Already have an account?
Sign in to comment