What Percent Of The Time Does A User Login, Immediately Followed By Sending A Message?

I have never queried for such a thing before and not sure how possible it is. Let's say I have the following table: user_id date event 22 2012-05-02 11:02:39 lo

Solution 1:

For a given user:

SELECT round(count(*) FILTER (WHERE sent_in_time) *100.0/count(*), 2) AS pct_sent_in_time
FROM  (
   SELECT (min(date) FILTER (WHERE event ='send_message')
         -min(date)) <interval'2 min'AS sent_in_time
   FROM  (
      SELECTdate, event
           , count(*) FILTER (WHERE event ='login')
                      OVER (ORDERBYdateROWSBETWEEN UNBOUNDED PRECEDING ANDCURRENTROW) AS grp
      FROM   tbl
      WHERE  user_id =22-- given user
      ) sub1
   GROUPBY grp
   ) sub2;

| pct_sent_in_time |
| ---------------: |
|            50.00 |

For all users:

SELECT user_id
     , round(count(*) FILTER (WHERE sent_in_time) *100.0/count(*), 2) AS pct_sent_in_time
FROM  (
   SELECT user_id
        , (min(date) FILTER (WHERE event ='send_message')
         -min(date)) <interval'2 min'AS sent_in_time
   FROM  (
      SELECT user_id, date, event
           , count(*) FILTER (WHERE event ='login')
                      OVER (PARTITIONBY user_id ORDERBYdateROWSBETWEEN UNBOUNDED PRECEDING ANDCURRENTROW) AS grp
      FROM   tbl
      ) sub1
   GROUPBY user_id, grp
   ) sub2
GROUPBY user_id;

user_id | pct_sent_in_time
------: | ---------------:
     22 |            33.33
     23 |           100.00

I extended the test case to make it more revealing, hence a different percentage. See: db<>fiddle here

Partition data after every new login, and check whether 'send_message' happens within less than 2 minutes. Then calculate percentage and round.

Notably, this is not fooled by many logins in quick succession, followed my a login with a message in under 2 minutes.

Related:

• Aggregate values over a range of hours, every hour

Aside: The name "date" for a timestamp column is quite misleading.