Leetcode Problem 1939. Users That Actively Request Confirmation Messages

1939. Users That Actively Request Confirmation Messages

Leetcode Solutions

Self Join with Time Difference Check

  1. Perform a self join on the Confirmations table where user_id is the same and the time_stamp of one confirmation is strictly less than the time_stamp of the other.
  2. Use the TIMESTAMPDIFF function to calculate the difference in seconds between the two timestamps.
  3. Filter the results to only include pairs where the time difference is less than or equal to 86400 seconds (24 hours).
  4. Select distinct user_ids from the filtered results.

erDiagram
    Signups {
        int user_id PK
        datetime time_stamp
    }
    Confirmations {
        int user_id PK
        datetime time_stamp PK
        ENUM action
        Signups user_id FK
    }

Using LAG Window Function

Ask Question

Programming Language
image/screenshot of info(optional)
Full Screen
Loading...

Suggested Answer

Answer
Full Screen
Copy Answer Code
Loading...