Cracking SQL Interview Questions: User System Response Times Explained

[@iqjayfeng]

Want to ace your data science interview? 🚀 85% of successful candidates practice with real interview questions. Explore the ones companies actually ask: www.interviewquery.com/questions?

[@tahakhalid324]

Hi Jay thanks for posting, Here is my solution to the question: with conversation_messages_users as ( SELECT cm.id as id_1, cm.created_at as created_at, cm.updated_at as updated_at, cm.text, cm.message_type, cm.interview_id, coalesce(i.user_id,0) as user_id FROM conversation_messages cm left join interviews i on cm.interview_id = i.id ) , prev_responses as ( SELECT user_id, message_type, created_at, lag(created_at,1,0)over(partition by user_id order by created_at) prev_creat, lag(message_type,1,0)over(partition by user_id order by created_at) prev_mesg from conversation_messages_users order by created_at ) SELECT user_id, avg(TIMESTAMPDIFF(SECOND, prev_creat, created_at)) AS response_times FROM prev_responses WHERE message_type = 'user' AND prev_mesg= 'system' group by user_id