By using db<>fiddle, you agree to license everything you submit by Creative Commons CC0.
create table clicks (u char, d date);
insert into clicks (u, d) values
('a', '2021-10-01'),
('a', '2021-10-02'),
('a', '2021-10-03'),
('b', '2021-09-27'),
('c', '2021-10-08'),
('c', '2021-10-13');
6 rows affected
create table segment (u char, d date, segment int);
insert into segment (u, d, segment) values
('a', '2021-09-26', 1),
('a', '2021-09-27', 1),
('a', '2021-10-03', 2),
('c', '2021-10-01', 5),
('c', '2021-10-10', 6);
5 rows affected
select *
from (
select
c.*, s.segment,
row_number() over(partition by c.u, c.d order by s.d desc) as rn
from clicks c
left join segment s on s.u = c.u and c.d >= s.d
) x
where rn = 1
| u | d | segment | rn |
|---|---|---|---|
| a | 2021-10-01 | 1 | 1 |
| a | 2021-10-02 | 1 | 1 |
| a | 2021-10-03 | 2 | 1 |
| b | 2021-09-27 | null | 1 |
| c | 2021-10-08 | 5 | 1 |
| c | 2021-10-13 | 6 | 1 |