Mysql Question posted in
The official MySQL documentation can be found here.

Mysql – Select most current row which is at least 7 days younger than the 2nd oldest row per user

I’m working on a monitoring system based on MySQL. The system consists of a table which records checks (e.g. a crawled website) for multiple users. The users shall be notified about their last failed (NOK) check but only once a week. So if multiple checks happen within a week a user shall get only one notification. If a check was good the status is OK. Otherwise it’s NOK.

Table "Checks":

id userId checkAt status notifiedAt
1 10 2024-07-31 NOK
2 10 2024-07-30 OK
3 10 2024-06-20 NOK 2024-06-21
4 20 2024-07-31 NOK
5 20 2024-07-30 NOK 2024-07-30

Now I would like to notify each user about its latest NOK check. The latest NOK check shall be selected per user if there’s no other NOK check for the same user which has a notifiedAt date which is not older than 7 days. This is needed to prevent that a user gets notified more than once per week.

Comments about the example data:

  • Let’s assume the query shall be executed on 2024-07-31
  • Check-1 would be selected as it’s the most current check for user-10 with status NOK and the last notification for user-10 (check-3) lays more that 7 days in the past
  • User-20 would not get a notification as he was notified about his last NOK check just one day before

Expected result:

id userId checkAt status notifiedAt
1 10 2024-07-31 NOK

I’ve tried a MySQL query with a group by userId but then I just have one record per user and I can’t compare the notifiedAt date to his previous NOK record.

My question is: Is it possible two resolve this selection with just one SQL query or how should I proceed?

Thanks in advance for a suggestion!

Tags:

2

Answers


  1. 0

    I think this query can work,

    
select id, userId, checkAt, status, notifiedAt
from Checks
where status = 'NOK' 
and checkAt = (
select max(checkAt) from Checks as sub where sub.userId = Checks.userId
and sub.status = 'NOK')
and (notifiedAt IS NULL
OR notifiedAt < DATE_SUB(CURRENT_DATE, INTERVAL 7 DAY));
    Login or Signup to reply.
  2. 0

    Since you want to notify the user once per week in case of NOK check status – it would be best to use WEEK() function. It is much more consistent then looking 7 days behind for each and every date.
    WEEK() function allows you to define which day you want to consider as start of the week (check here)…

    WITH    --  S a m p l e    D a t a :
  checks ( id, userId, checkAt, status, notifiedAt ) AS
    ( Select 1, 10, '2024-07-31', 'NOK', Null  Union All    
      Select 2, 10, '2024-07-30', 'OK',  Null  Union All
      Select 3, 10, '2024-06-20', 'NOK', '2024-06-21'  Union All
      Select 4, 20, '2024-07-31', 'NOK', NUll   Union All
      Select 5, 20, '2024-07-30', 'NOK', '2024-07-30' 
    )

    --      S Q L :
SELECT   c.id, c.userId, c.checkAt, c.status, c.notifiedAt
FROM   ( Select   c.id, c.userId, c.checkAt, c.status, c.notifiedAt, 
                  WEEK(c.checkAt) as week_checked,
                  Max( WEEK(c.notifiedAt) ) 
                       Over( Partition By c.userid ) as max_week_notified
         From     checks c
         Where    c.status = 'NOK'  ) c
WHERE    Coalesce( c.max_week_notified, 0 ) < c.week_checked

    /*    R e s u l t : 
id  userId  checkAt     status  notifiedAt
--  ------  ----------  ------  ----------
 1      10  2024-07-31  NOK     null         */

    See the fiddle here.

    NOTE:
    If you have a case of checks containing data from multiple years you should, beside the week, add year to your conditions.

    Login or Signup to reply.
Please signup or login to give your own answer.

Back To Top
Search