SQL select with dynamic count of "BETWEEN" conditions based on joined table

Question

I want to add a messenger to my pet project, but I am having difficulty writing database queries. I use MySQL for this service with Hibernate as ORM. Almost all queries was written in HQL, but in principle I can use native queries.

Messenger can contain group conversations. In addition to writing messages, user can enter the conversation, leave it, clear personal message history. User sees all messages when he has been in a conversation, but he can also clear the history and see only messages after the last clearing.

Below I described the simplified structure of two tables important for this task.

Message table:

ID	text	timestamp
1	first_msg	1609459200
2	second_msg	1609545600

Member_event table:

id	user_id	type	timestamp
1	1	1	1609459100
2	1	3	1609459300
3	1	2	1609459400
4	1	1	1609545500

where type:
1 - user entered the chat,
2 - user leaved the chat,
3 - user cleared his own history of messages in the chat

Is it possible to read all chat messages available to the user with one request?

I have no idea how to check conditions dynamically: WHERE message's timestamps are between all "entered-leaved" cycles and after the last "entered" if not leaved BUT only after the last history clearing. If exists.

Answer 1

I think you could proceed with these steps:

1. take the union of both tables and consider the records in order of time stamp
2. Use window functions to determine whether the most recent 1 or 2 type was a 1. We can use a running sum where type 1 adds one and type 2 subtracts one (and 3 does nothing to it). With another window function you could determine whether there is still a type 3 following. The combination of these two informations can be translated to a 1 when the line belongs to an interval that must be collected, and a 0 when not.
3. Filter the previous result to just get the message records, and only those where the calculation was 1.

Here is the query:

with unified as (
    select   id, text, timestamp, null as type
    from     message
    union
    select   id, null, timestamp, type 
    from     member_event
    where    user_id = 1),

validated as (
    select   unified.*,
             sum(case type when 1 then 1 when 2 then -1 else 0 end) 
                over (order by timestamp
                      rows unbounded preceding) * 
             min(case type when 3 then 0 else 1 end) 
                over (order by timestamp
                      rows between current row and unbounded following) valid
    from     unified
    order by timestamp)

select   id, text, timestamp
from     validated
where    type is null and valid = 1
order by timestamp