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

user_2988753 0
Viewed 35

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

mysql sql
1 Answers

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;
user_18632290
Related