How to query the consecutive days of sign-in in MySQL?

2 years ago
Olivia Parker
2 minutes

To check the consecutive days of sign-ins, you can achieve it by following these steps:

  1. Create a table called attendance, which includes the following fields:
  2. ID (integer, primary key)
  3. user_id (integer, user ID)
  4. sign-in date
  5. Insert the attendance record data into the attendance table.
INSERT INTO attendance (user_id, date) VALUES
(1, '2022-01-01'),
(1, '2022-01-02'),
(1, '2022-01-03'),
(1, '2022-01-05'),
(1, '2022-01-06'),
(1, '2022-01-07'),
(1, '2022-01-08'),
(1, '2022-01-09');
  1. Calculate the number of consecutive days of signing in using the following SQL query statement:
SELECT user_id, MAX(streak) AS max_streak
FROM (
    SELECT user_id, date,
           ROW_NUMBER() OVER (PARTITION BY user_id ORDER BY date) - 
           ROW_NUMBER() OVER (PARTITION BY user_id, date ORDER BY date) AS streak
    FROM attendance
) AS subquery
GROUP BY user_id;

The query begins by using the window function ROW_NUMBER() to calculate the order of each check-in record on the user’s check-in date, then calculates the consecutive check-in days by subtracting. Finally, it groups by user ID and selects the maximum consecutive check-in days.

By following the steps above, you can query the maximum consecutive sign-in days for each user.

bannerAds