Posted by Will, Jan. 18, 2023, 12:19 p.m.
Question 105 - Subtracting Dates vs. DATEDIFF (MySQL)
Hello - I have been working this problem and doing some investigation, and I believe the solution may be incorrect, but I want to lay things out here to confirm.
Using user_id 8000022 as our example, I calculated (using DATEDIFF) a delta of 16 days. According to the solution, this should be 85 days. I inspected the data by running the following:
SELECT *
FROM ordered_actions
WHERE user_id = 8000022
LIMIT 5;
against your CTE ordered_actions. It gives the following result:
| user_id | date | nth_action |
|---|---|---|
| 8000022 | 2021-08-07 | 1 |
| 8000022 | 2021-07-22 | 2 |
| 8000022 | 2021-07-22 | 3 |
| 8000022 | 2021-07-22 | 4 |
| 8000022 | 2021-07-22 | 5 |
where we can clearly see that the delta between the 1st (last) and 2nd (second last) action is bewteen August 7th and July 22nd. Definitely 16 days, not 85.
So my question is why does
date1 - date2 = 85 days
when
DATEDIFF(date1, date2) = 16 days?
Any insight would be much appreciated - Thanks!
Will
