Welcome to Subscribe On Youtube
3172. Second Day Verification 🔒
Description
Table: emails
+-+-+ \| email_id \| int \| \| user_id \| int \| \| signup_date \| datetime \| +-++++-++ \| email_id \| user_id \| signup_date \| ++-++
texts table:
+-+--+++ \| 1 \| 125 \| Verified \| 2022-06-15 08:30:00\| \| 2 \| 433 \| Not Verified \| 2022-07-10 10:45:00\| \| 4 \| 234 \| Verified \| 2022-08-21 09:30:00\| +-+--++ \| user_id \| ++
Explanation:
- User with email_id 7005 signed up on 2022-08-20 10:00:00 and verified on second day of the signup.
- User with email_id 7771 signed up on 2022-06-14 09:30:00 and verified on second day of the signup.
</div>
Solutions
Solution 1: Joining Two Tables
We can join the two tables and then use the DATEDIFF function to calculate whether the difference between the registration date and the operation date is equal to 1, and whether the registration operation is Verified, to filter out the user IDs that meet the conditions.
-
# Write your MySQL query statement below SELECT user_id FROM Emails AS e JOIN texts AS t ON e.email_id = t.email_id AND DATEDIFF(action_date, signup_date) = 1 AND signup_action = 'Verified' ORDER BY 1;