Solved with window function. How to make a complex query for MySql?

Question

MySQL

There is a table:

RecordID Date            Time   EventID UserID ClientID
1             |2019-02-01 |15:00  |2           |100      |5
2             |2019-02-01 |17:00  |2           |150      |5
3             |2019-02-05 |12:00  |2           |200      |5
4             |2019-02-05 |15:00  |3           |100      |5
5             |2019-02-06 |15:00  |2           |200      |5

You need to write a query to the MySQL database that generates consecutive intervals of work of different users with the specified client and the number of days of work.
It should come out something like:

UserID DateStart      DateEnd         Days
100     |2019-02-01  |2019-02-01  |1
150     |2019-02-01  |2019-02-05  |5
200     |2019-02-05  |2019-02-06  |2

I upgraded to MySql 8 and used the window function, this is what came out:

SELECT 
    Date, Time, EventId, UserId, 
    IF (LEAD(Date) OVER W, LEAD(Date) OVER W - Date + 1, NOW() - Date) AS 'diff' 
FROM first 
WINDOW W AS (ORDER BY Date);

But this thing still does not take into account that the EventId is different.
You can do like this:

SET sql_mode = ''; SELECT
a.UserId, MIN(a.`Date`) AS DateStart, IFNULL(b.`Date`, CURDATE()) AS DateEnd,
DATEDIFF(b.`Date`, a.`Date`) + 1 AS Days, a.EventId, a.ClientId
FROM first a LEFT JOIN first b
ON b.RecordId = ( SELECT RecordId FROM first aa WHERE aa.EventId = a.EventId AND aa.`Date` >= a.`Date` AND aa.RecordId > a.RecordId ORDER BY aa.`Date` ASC LIMIT 1 )
GROUP BY a.UserId, a.EventId, a.ClientId ORDER BY a.`RecordId` ASC;

Final variant for MySql 8:

SELECT      
  RecordId AS RecId, Date, Time, EventId AS EvID, UserId,        
  CASE          
    WHEN (LEAD(EventId) OVER W = EventId)            
    THEN (LEAD(Date) OVER W - Date + 1)          
    WHEN (LEAD(EventId) OVER W != EventId)            
    THEN IF ((SELECT Date FROM first WHERE (RecordId > RecId AND EvID = EventId) LIMIT 1),                   DATEDIFF( (SELECT Date FROM first WHERE (RecordId > RecId AND EvID = EventId) LIMIT 1), NOW()), NOW() - Date)           ELSE (NOW() - DATE)             
    END         
    AS 'diff'  
    FROM first WINDOW W AS (ORDER BY Date);

Answer 1

More or less like this:

SELECT 
    t1.UserID,
    t1.Date DateStart,
    min(CASE 
        WHEN t2.Event='end' AND t2.Date>t1.Date THEN t2.Date 
        ELSE null END) DateEnd
FROM table t1
CROSS JOIN table t2 
WHERE t1.Event='start' 
GROUP BY UserID

although it might be better to do it with analytics.
Further themselves. You have something messed up with EventID, so I took a simple understandable option as an example. Change Date to a timestamp: either in the query, or it's better to do it in the database to avoid similar problems in the future.
If EventId is the identifier of a single time interval, i.e. the table stores up to 2 EventIds (beginning and end, or only the beginning, or nothing), then:

SELECT 
    t1.UserID,
    t1.Date DateStart,
    CASE 
        WHEN t2.Date is null THEN t1.Date 
        ELSE t2.Date END DateEnd
FROM table t1
LEFT JOIN table t2 ON t1.EventID=t2.EventID AND t1.Date<t2.Date

something like this. It doesn't look very good with case, but it's better than cross join with distinct. Well, instead of date, take a timestamp.