HQL find the maximum value in a range

The main thing to understand is

Strings can be sorted , But you can't do the best , For example, the date here

 This problem is to find the largest value in the start time and end time , And it's also a way to turn two columns into one example 

The answer to the final query

select user_id,
       max(num) max_num
from (
    select id,
           user_id,
           dt,
           sum(p) over(partition by user_id order by dt) num
    from (
        select id,
               user_id,
               begin_date dt,
               1 p
        from test
        union
        select id,
               user_id,
               end_date dt,
               -1 p
        from test
    ) t1
) t2
group by user_id;

Test statement

create table if not exists test(
id int,
user_id string,
begin_date string,
end_date string
)
row format delimited 
fields terminated by ',';

insert into test values(1,'A','2020-01-01','2020-01-30');
insert into test values(2,'A','2020-01-02','2020-01-30');
insert into test values(3,'A','2020-01-10','2020-02-10');
insert into test values(4,'B','2020-02-11','2020-02-30');
insert into test values(5,'C','2020-01-01','2020-01-30');

Let's decompose this table , Then merge , Get the fields we want

--  Which table to get the start time 
select id,user_id,begin_date dt, 1 p
from test
1       A       2020-01-01      1
2       A       2020-01-02      1
3       A       2020-01-10      1
4       B       2020-02-11      1
5       C       2020-01-01      1

--  Which table to get the end time 
select id,user_id,end_date dt, -1 p
from test

1       A       2020-01-30      -1
2       A       2020-01-30      -1
3       A       2020-02-10      -1
4       B       2020-02-30      -1
5       C       2020-01-30      -1

 Merge the two tables (union)( Turn... Into two columns )
id     user_id      dt          p
1       A       2020-01-01      1
1       A       2020-01-30      -1
2       A       2020-01-02      1
2       A       2020-01-30      -1
3       A       2020-01-10      1
3       A       2020-02-10      -1
4       B       2020-02-11      1
4       B       2020-02-30      -1
5       C       2020-01-01      1
5       C       2020-01-30      -1

 Then group this table . And then to dt Sort , Also on p In sum 
1       A       2020-01-01      1
2       A       2020-01-02      2
3       A       2020-01-10      3
2       A       2020-01-30      1
1       A       2020-01-30      1
3       A       2020-02-10      0
4       B       2020-02-11      1
4       B       2020-02-30      0
5       C       2020-01-01      1
5       C       2020-01-30      0

 Finally, it's going on use_id Grouping , Go to the biggest p that will do 

 This result is the maximum value in a certain range , That is, the largest number of positions