The number of records in a row?

Hello! I can not figure out how to build the query. There is a table: date, user_id, status (pair date + user_id unique), in which daily data is written. Ie, for each employee daily maintained its status (1 = active, 0 = inactive).
The task is to deduce the number of CONSECUTIVE days (starting from the last record for each employee) if the last record is active - how many CONSECUTIVE days an employee is active if locked - how many days in a ROW blocked.
March 20th 20 at 11:49
3 answers
March 20th 20 at 11:51
Good morning.
You can get the difference between dates.
Get the difference between the registration date and the date when they last edited their profile.
SELECT `username`, DATEDIFF(FROM_UNIXTIME(`updated_at`), FROM_UNIXTIME(`created_at`)) AS `date_diff` FROM `user`


The result is the following:
Array
(
 [0] => Array
(
 [username] => admin
 [date_diff] => 261
)

 [1] => Array
(
 [username] => Denis
 [date_diff] => 73
)

 [2] => Array
(
 [username] => Vitaliy
 [date_diff] => 80
)

 [3] => Array
(
 [username] => Elena
 [date_diff] => 78
)

 [4] => Array
(
 [username] => Boris
 [date_diff] => 0
)

 [5] => Array
(
 [username] => Daniel
 [date_diff] => 0
)

 [6] => Array
(
 [username] => Vladimir
 [date_diff] => 42
)

 [7] => Array
(
 [username] => Vasya
 [date_diff] => 0
)

 [8] => Array
(
 [username] => Anton
 [date_diff] => 0
)

 [9] => Array
(
 [username] => Nicolay
 [date_diff] => 5
)
)


Or receive the difference between the current date and the date of registration of the user
SELECT `username`, DATEDIFF(CURDATE(), FROM_UNIXTIME(`created_at`)) AS `date_diff` FROM `user`


The result is the following:
Array
(
 [0] => Array
(
 [username] => admin
 [date_diff] => 794
)

 [1] => Array
(
 [username] => Denis
 [date_diff] => 616
)

 [2] => Array
(
 [username] => Vitaliy
 [date_diff] => 515
)

 [3] => Array
(
 [username] => Elena
 [date_diff] => 315
)

 [4] => Array
(
 [username] => Boris
 [date_diff] => 215
)

 [5] => Array
(
 [username] => Daniel
 [date_diff] => 200
)

 [6] => Array
(
 [username] => Vladimir
 [date_diff] => 175
)

 [7] => Array
(
 [username] => Vasya
 [date_diff] => 150
)

 [8] => Array
(
 [username] => Anton
 [date_diff] => 149
)

 [9] => Array
(
 [username] => Nicolay
 [date_diff] => 149
)

)

March 20th 20 at 11:53
SELECT `user_id`, ABS(DATEDIFF(`t0`.`date`, `t1`.`date`)) AS `days`
 FROM (
 SELECT `user_id`, MAX(`date`) as `date`
 FROM `table`
 WHERE `status` = 0
 GROUP BY `user_id`
 ) AS `t0`
 JOIN (
 SELECT `user_id`, MAX(`date`) as `date`
 FROM `table`
 WHERE `status` = 1
 GROUP BY `user_id`
 ) AS `t1` USING `user_id`
In both subqueries, status = 0, but apparently it was assumed that some must be 1 otherwise the result will always be 0 days. - ursula57 commented on March 20th 20 at 11:56
@Markus_Hessel55, Yes, corrected - isabelle commented on March 20th 20 at 11:59
March 20th 20 at 11:55
1. To obtain the current status of the user
2. To get the maximum date for this user with a different status
3. To calculate the difference.

Find more questions by tags MySQL