I'm using MySQL with phpMyAdmin,
below is my data table:
+--------+---------+-----------------+
| userID | context | time |
+--------+---------+-----------------+
| 111 | | 7/1/2021 |
| 111 | | 7/16/2019 |
| 111 | Reset | 7/15/2019 |
| 222 | | 7/9/2020 |
| 222 | Reset | 7/8/2020 |
| 333 | Reset | 5/11/2020 |
| 333 | | 5/10/2020 |
| 444 | | 9/8/2020 |
+--------+---------+-----------------+
I'm looking for a SELECT query that gives me the MIN time greater or equal to the date where a Reset is logged in the context column. If no Reset marker exists in the context column, I'd like to have the result included.
So for the table above, I expect the result:
+--------+-----------------+
| userID | time |
+--------+-----------------+
| 111 | 7/15/2019 |
| 222 | 7/8/2020 |
| 333 | 5/11/2020 |
| 444 | 9/8/2020 |
+--------+-----------------+
I tried :
SELECT * FROM foo as a
WHERE ((SELECT MIN(a.time) FROM foo) >= (SELECT Max(a.time) FROM foo where context = 'Reset')) group by userID
does not produce my expected output, but returns:
+--------+-----------+
| userID | time |
+--------+-----------+
| 111 | 7/1/2021 | <--- wrong
| 222 | 7/8/2020 |
| 333 | 5/11/2020 |
+--------+-----------+
Use window functions:
select t.*,
coalesce(next_time, time) as imputed_time
from (select t.*,
sum(context = 'reset') over (partition by user_id) as cnt_reset,
min(case when context is null then time end) over (partition by userid order by time rows between current row and unbounded following) as next_time
from t
) t
where cnt_reset = 0 or context = 'reset';
Basis on your expected result, You may try below query -
SELECT userID, MAX(time)
FROM YOUR_TABLE
WHERE context = 'RESET'
GROUP BY userID
UNION ALL
SELECT userID, MAX(time)
FROM YOUR_TABLE
WHERE context IS NULL
GROUP BY userID
ORDER BY userID