SQL window function by column value and not by rows

Question

I am trying to create a flag that looks back 7 days for a date column and if there is a row within that partition then true if there is no row returned within that 7 days then false.

Can a window function accomplish this? most windowing function seem to operate on rows and not column values.

checkout sample data There are a bunch of messeage_ids and then has an event of open then a timestamp. for each row i need to look back 604800 seconds and see if any rows exist within the message_id partition.

sample data

Answer 1

Something like this would work for many databases:

select sg_message_id, event, t1.timestamp, 
  case when t1.timestamp - previous.timestamp < 604800 
  then true else false end as hasRecent
from myTable t1, 
 lateral (select timestamp 
          from myTable t2 
          where t1.sg_message_id = t2.sg_message_id
               and t2.timestamp < t1.timestamp
          order by t2.timestamp desc
          limit 1) previous(timestamp);  

If you want to use window functions, then look at lead(). With lead() it would look like:

    with data as 
(
   select sg_message_id, event, timestamp, 
      lead(timestamp) over (partition by sg_message_id order by timestamp desc) as previousTimeStamp
)
   select sg_message_id, event, timestamp, 
          case when timestamp - previousTimestamp < 604800 
      then true else false end as hasRecent
    from data;

Answer 2

Below is an example.

SELECT
    message_id,
    event_timestamp,
    CASE
        WHEN EXISTS (
            SELECT 1
            FROM your_table AS t2
            WHERE t2.message_id = t1.message_id
              AND t2.event = 'open'
              AND t2.event_timestamp >= t1.event_timestamp - INTERVAL 7 DAY
              AND t2.event_timestamp < t1.event_timestamp
        ) THEN true
        ELSE false
    END AS flag
FROM your_table AS t1
WHERE event = 'open';

Regards,