SQLize Online / PHPize Online  /  SQLtest Online

SELECT K.Num, K.user_id, K.user_position, K.position_start, V.position_end FROM(SELECT (row_number() OVER (ORDER BY position_start)) Num, user_id, user_position, position_start FROM (SELECT T.*, (CASE WHEN STR = 0 THEN date_position ELSE 0 END) position_start FROM(SELECT users_positions.*, CASE WHEN COALESCE (LAG(date_position) OVER (PARTITION BY user_id, user_position ORDER BY date_position ASC)) = (date_position - 1) THEN 1 ELSE 0 END STR FROM users_positions) T WHERE (CASE WHEN STR = 0 THEN date_position ELSE 0 END) >0 order by user_id, position_start) K JOIN (SELECT (row_number() OVER (ORDER BY position_end)) Num, position_end FROM (SELECT U.*, (CASE WHEN FTR = 0 THEN date_position ELSE 0 END) position_end FROM(SELECT users_positions.*, CASE WHEN COALESCE (LEAD(date_position) OVER (PARTITION BY user_id, user_position ORDER BY date_position ASC)) = (date_position + 1) THEN 1 ELSE 0 END FTR FROM users_positions) U WHERE (CASE WHEN FTR = 0 THEN date_position ELSE 0 END) >0 order by user_id, position_end)C )V ON K.Num = V.Num