SQL Server 2019

By using db<>fiddle, you agree to license everything you submit by Creative Commons CC0.

CREATE TABLE Users (user_id int, place varchar(13), purchase_time datetime);
    
INSERT INTO Users
    (user_id, place, purchase_time)
VALUES
    (1, 'New York',   '2021-11-27 17:00:21'),
    (1, 'Chicago',    '2021-11-25 19:00:21'),
    (1, 'Chicago',    '2021-11-23 03:00:21'),
    (1, 'Washington', '2021-11-21 07:00:21'),
    (1, 'Washington', '2021-11-19 12:00:21'),
    (1, 'Washington', '2021-11-17 00:00:21'),
    (1, 'Washington', '2021-11-15 23:00:21'),
    (1, 'Washington', '2021-11-12 21:00:21'),
    (2, 'Chicago',    '2021-09-25 01:00:21'),
    (2, 'Milwaukee',  '2021-09-24 02:00:21'),
    (2, 'Milwaukee',  '2021-09-23 03:00:21'),
    (2, 'New York',   '2021-09-22 19:00:21'),
    (2, 'Chicago',    '2021-09-21 01:00:21'),
    (3, 'Milwaukee',  '2021-10-27 12:31:21'),
    (3, 'Washington', '2021-10-24 07:01:23'),
    (3, 'Chicago',    '2021-10-21 01:18:19');
;
select * from Users order by user_id, purchase_time desc


user_id
place
purchase_time


1
New York
2021-11-27 17:00:21.000

1
Chicago
2021-11-25 19:00:21.000

1
Chicago
2021-11-23 03:00:21.000

1
Washington
2021-11-21 07:00:21.000

1
Washington
2021-11-19 12:00:21.000

1
Washington
2021-11-17 00:00:21.000

1
Washington
2021-11-15 23:00:21.000

1
Washington
2021-11-12 21:00:21.000

2
Chicago
2021-09-25 01:00:21.000

2
Milwaukee
2021-09-24 02:00:21.000

2
Milwaukee
2021-09-23 03:00:21.000

2
New York
2021-09-22 19:00:21.000

2
Chicago
2021-09-21 01:00:21.000

3
Milwaukee
2021-10-27 12:31:21.000

3
Washington
2021-10-24 07:01:23.000

3
Chicago
2021-10-21 01:18:19.000


WITH prior_user AS (
    SELECT 
        user_id,
        place,
        purchase_time,
        COUNT(place) OVER (PARTITION BY user_id, place ORDER BY purchase_time ROWS BETWEEN UNBOUNDED PRECEDING AND CURRENT ROW) AS place_count
    FROM users
), data as (
SELECT 
    u.user_id,
    u.place,
    u.purchase_time,
    p.place AS most_visited_place,
    row_number() OVER (partition by u.user_id, u.purchase_time ORDER BY place_count DESC, p.purchase_time DESC) rn
FROM users u
JOIN prior_user p
    ON u.user_id = p.user_id AND u.purchase_time >= p.purchase_time
)
select * from data where rn = 1
order by user_id, purchase_time desc


user_id
place
purchase_time
most_visited_place
rn


1
New York
2021-11-27 17:00:21.000
Washington
1

1
Chicago
2021-11-25 19:00:21.000
Washington
1

1
Chicago
2021-11-23 03:00:21.000
Washington
1

1
Washington
2021-11-21 07:00:21.000
Washington
1

1
Washington
2021-11-19 12:00:21.000
Washington
1

1
Washington
2021-11-17 00:00:21.000
Washington
1

1
Washington
2021-11-15 23:00:21.000
Washington
1

1
Washington
2021-11-12 21:00:21.000
Washington
1

2
Chicago
2021-09-25 01:00:21.000
Chicago
1

2
Milwaukee
2021-09-24 02:00:21.000
Milwaukee
1

2
Milwaukee
2021-09-23 03:00:21.000
Milwaukee
1

2
New York
2021-09-22 19:00:21.000
New York
1

2
Chicago
2021-09-21 01:00:21.000
Chicago
1

3
Milwaukee
2021-10-27 12:31:21.000
Milwaukee
1

3
Washington
2021-10-24 07:01:23.000
Washington
1

3
Chicago
2021-10-21 01:18:19.000
Chicago
1


select *
from Users u cross apply (
    select --place as visited_place, count(*) as place_cnt
        distinct first_value(place) over (order by count(*) desc, max(u2.purchase_time) desc) mr_place
        --place as vplace, count(*) ct, max(purchase_time) mx,
        --row_number() over (order by count(*) desc) as rn2
    from Users u2
    where u2.user_id = u.user_id and u2.purchase_time <= u.purchase_time
    group by place
) as mr
order by user_id, purchase_time desc


user_id
place
purchase_time
mr_place


1
New York
2021-11-27 17:00:21.000
Washington

1
Chicago
2021-11-25 19:00:21.000
Washington

1
Chicago
2021-11-23 03:00:21.000
Washington

1
Washington
2021-11-21 07:00:21.000
Washington

1
Washington
2021-11-19 12:00:21.000
Washington

1
Washington
2021-11-17 00:00:21.000
Washington

1
Washington
2021-11-15 23:00:21.000
Washington

1
Washington
2021-11-12 21:00:21.000
Washington

2
Chicago
2021-09-25 01:00:21.000
Chicago

2
Milwaukee
2021-09-24 02:00:21.000
Milwaukee

2
Milwaukee
2021-09-23 03:00:21.000
Milwaukee

2
New York
2021-09-22 19:00:21.000
New York

2
Chicago
2021-09-21 01:00:21.000
Chicago

3
Milwaukee
2021-10-27 12:31:21.000
Milwaukee

3
Washington
2021-10-24 07:01:23.000
Washington

3
Chicago
2021-10-21 01:18:19.000
Chicago

user_id	place	purchase_time
1	New York	2021-11-27 17:00:21.000
1	Chicago	2021-11-25 19:00:21.000
1	Chicago	2021-11-23 03:00:21.000
1	Washington	2021-11-21 07:00:21.000
1	Washington	2021-11-19 12:00:21.000
1	Washington	2021-11-17 00:00:21.000
1	Washington	2021-11-15 23:00:21.000
1	Washington	2021-11-12 21:00:21.000
2	Chicago	2021-09-25 01:00:21.000
2	Milwaukee	2021-09-24 02:00:21.000
2	Milwaukee	2021-09-23 03:00:21.000
2	New York	2021-09-22 19:00:21.000
2	Chicago	2021-09-21 01:00:21.000
3	Milwaukee	2021-10-27 12:31:21.000
3	Washington	2021-10-24 07:01:23.000
3	Chicago	2021-10-21 01:18:19.000