Find rows where ID matches and date is within X days

Somewhat new to SQL and I'm running into a bit of issue with a project. I have a table like this:

My goal is to return rows where the subscription_ID matches and the start_date is within or equal to a certain # of days (hours would work as well). For instance, I'd like rows where subscription_ID matches and the start_date is within or equal to 1 day such that my results from the table above would be:

Any assistance would be greatly appreciated--thanks!

Solution 1:

If I understand correctly maybe you are trying something like:

select t.*
from test_tbl t 
join  ( SELECT subscription_id
     , MAX(diff) max_diff
  FROM 
     ( SELECT x.subscription_id
            , DATEDIFF(MIN(y.start_date),x.start_date) diff
         FROM test_tbl x 
         JOIN test_tbl y ON y.subscription_id = x.subscription_id 
         AND y.start_date > x.start_date 
        GROUP  BY x.subscription_id , x.start_date
     ) z
 GROUP BY subscription_id 
        ) as t1 on t.subscription_id=t1.subscription_id
where t1.max_diff<=1;

Result:

id    subscription_id  start_date
1   11             2022-01-01 00:00:00
2   11             2022-01-02 00:00:00
3   12             2022-01-01 00:00:00
4   12             2022-01-01 12:00:00

The subquery returns:

subscription_id max_diff
   11              1
   12              0
   13              2

which is used on the where condition.

Demo