Logic improvement

[mike-hosseini]

On question 3 of Text Confirmation problem, your join predicate ignores the fact that a user can send confirmation on both the first day of sign up and second day of sign up.

-- dialect: MySQL

SELECT
  e.user_id
FROM Email AS e
JOIN Text AS t
ON e.user_id = t.user_id
  AND DATEDIFF(t.ts, e.ts) = 1
WHERE t.action = 'CONFIRMED';

A more accurate answer would be to only get the first date the user sent a confirmation and then perform a join and do a date comparison.

-- dialect: PostgreSQL

with first_confirm as (
  select
        user_id,
        ts,
        row_number() over (partition by user_id order by ts) as rn
  from text
  where action = 'CONFIRMED'
) select e.user_id
from email e
  join first_confirm f on e.user_id = f.user_id
  where f.rn = 1 and cast(f.ts as date) -  cast(e.ts as date) = 1