MYSQL: How to get records based on the previous related ones

August 1, 2022

I’m trying to get records taking account of the just related one. In this case we have some users and we need subscriptions that their just previous ones where created in 2018.

We have this subscriptions table:

id	user_id	created_at
1	1	2016-01-01
2	1	2017-01-01
3	1	2018-01-01
4	1	2019-01-01
5	1	2020-01-01
6	2	2018-01-01
7	2	2019-01-01

I am using a self-join:

SELECT `subscriptions`.`id`
FROM `subscriptions`
LEFT JOIN subscriptions as previous 
    ON subscriptions.user_id = previous.user_id AND subscriptions.created_at > previous.created_at
WHERE `previous`.`created_at` BETWEEN '2018-01-01' AND '2018-12-31';

It returns 4,5,7 but I only want the just following ones 4,7

SQLFiddle

>Solution :

On MySQL 8+, we can use the LAG() analytic function here:

WITH cte AS (
    SELECT *, LAG(created_at) OVER (PARTITION BY user_id
                                    ORDER BY created_at) lag_created_at
    FROM subscriptions
)

SELECT id, user_id, created_at
FROM cte
WHERE YEAR(lag_created_at) = 2018;