Leaderboard

There is an alternative that you might want to consider and that is to create an additional "status" column in your users table, adding it as a generated column which will automatically maintain the user status based on the date calculations (as the above queries do). At present, I have mysql> select * from users; +----+--------------------------------------+-------+---------------------+---------------------+ | id | unique_id | fname | created_at | visited | +----+--------------------------------------+-------+---------------------+---------------------+ | 1 | 5a9c4d9c-c20d-11ee-b072-8cec4b4596d3 | aaaa | 2023-03-24 21:25:06 | NULL | | 2 | 5a9c50bd-c20d-11ee-b072-8cec4b4596d3 | bbbb | 2023-05-14 21:25:07 | 2024-02-01 21:25:07 | | 3 | 5a9c5116-c20d-11ee-b072-8cec4b4596d3 | cccc | 2023-07-11 21:25:07 | 2024-02-01 21:25:07 | | 4 | 5a9c5152-c20d-11ee-b072-8cec4b4596d3 | dddd | 2023-12-13 21:25:07 | NULL | | 5 | 5a9c5189-c20d-11ee-b072-8cec4b4596d3 | eeee | 2023-08-12 21:25:07 | NULL | | 6 | 5a9c51ba-c20d-11ee-b072-8cec4b4596d3 | ffff | 2023-09-27 21:25:07 | NULL | | 7 | 5a9c51ec-c20d-11ee-b072-8cec4b4596d3 | gggg | 2023-05-31 21:25:07 | 2023-09-01 21:25:07 | | 8 | 5a9c5221-c20d-11ee-b072-8cec4b4596d3 | hhhh | 2023-06-25 21:25:07 | NULL | | 9 | 5a9c5255-c20d-11ee-b072-8cec4b4596d3 | kkkk | 2023-09-18 21:25:07 | 2023-04-01 21:25:07 | | 10 | 5a9c5288-c20d-11ee-b072-8cec4b4596d3 | mmm | 2023-04-15 21:25:07 | NULL | +----+--------------------------------------+-------+---------------------+---------------------+ If I then alter the table, adding the new status column... mysql> ALTER TABLE users -> ADD COLUMN `status` VARCHAR(10) GENERATED ALWAYS AS -> (CASE WHEN visited IS NULL -> THEN CASE WHEN timestampdiff(DAY, created_at, now()) < 90 -> THEN 'PENDING' -> WHEN timestampdiff(DAY, created_at, now()) >= 180 -> THEN 'EXPIRED' -> ELSE 'WARNING' -> END -> ELSE CASE WHEN timestampdiff(DAY, visited, now()) < 90 -> THEN 'OK' -> WHEN timestampdiff(DAY, visited, now()) >= 180 -> THEN 'EXPIRED' -> ELSE 'WARNING' -> END -> END) VIRTUAL AFTER `visited`; I can then use a conventional GROUP BY status to get the totals of each type... mysql> SELECT status -> , COUNT(*) as total -> FROM users -> GROUP BY status; +---------+-------+ | status | total | +---------+-------+ | EXPIRED | 4 | | OK | 2 | | PENDING | 1 | | WARNING | 3 | +---------+-------+ and selecting all the data now looks like this... mysql> SELECT * FROM users; +----+--------------------------------------+-------+---------------------+---------------------+---------+ | id | unique_id | fname | created_at | visited | status | +----+--------------------------------------+-------+---------------------+---------------------+---------+ | 1 | 5a9c4d9c-c20d-11ee-b072-8cec4b4596d3 | aaaa | 2023-03-24 21:25:06 | NULL | EXPIRED | | 2 | 5a9c50bd-c20d-11ee-b072-8cec4b4596d3 | bbbb | 2023-05-14 21:25:07 | 2024-02-01 21:25:07 | OK | | 3 | 5a9c5116-c20d-11ee-b072-8cec4b4596d3 | cccc | 2023-07-11 21:25:07 | 2024-02-01 21:25:07 | OK | | 4 | 5a9c5152-c20d-11ee-b072-8cec4b4596d3 | dddd | 2023-12-13 21:25:07 | NULL | PENDING | | 5 | 5a9c5189-c20d-11ee-b072-8cec4b4596d3 | eeee | 2023-08-12 21:25:07 | NULL | WARNING | | 6 | 5a9c51ba-c20d-11ee-b072-8cec4b4596d3 | ffff | 2023-09-27 21:25:07 | NULL | WARNING | | 7 | 5a9c51ec-c20d-11ee-b072-8cec4b4596d3 | gggg | 2023-05-31 21:25:07 | 2023-09-01 21:25:07 | WARNING | | 8 | 5a9c5221-c20d-11ee-b072-8cec4b4596d3 | hhhh | 2023-06-25 21:25:07 | NULL | EXPIRED | | 9 | 5a9c5255-c20d-11ee-b072-8cec4b4596d3 | kkkk | 2023-09-18 21:25:07 | 2023-04-01 21:25:07 | EXPIRED | | 10 | 5a9c5288-c20d-11ee-b072-8cec4b4596d3 | mmm | 2023-04-15 21:25:07 | NULL | EXPIRED | +----+--------------------------------------+-------+---------------------+---------------------+---------+