SQL Interview Questions – Users That Actively Request Confirmation Messages

Problem Description –

Write an SQL query to find the IDs of the users that requested a confirmation message twice within a 24-hour window. Two messages exactly 24 hours apart are considered to be within the window. The action does not affect the answer, only the request time.

Return the result table in any order.

The query result format is in the following example.

Problem Link – Users

Difficulty Level – Easy

Solution –

SELECT
    DISTINCT
	user_id
FROM (
SELECT
    t1.user_id,
    TIMESTAMPDIFF(second, t1.time_stamp, t2.time_stamp) as time_diff
FROM Confirmations as t1 , Confirmations as t2
WHERE t1.user_id = t2.user_id AND t1.time_stamp < t2.time_stamp
) x
WHERE time_diff <= 86400