Postgres 12

By using db<>fiddle, you agree to license everything you submit by Creative Commons CC0.
CREATE TABLE tbl (
  user_id int
, date    timestamp
, event   text
);

INSERT INTO tbl VALUES
 (22, '2012-05-02 11:02:39', 'login')
,(22, '2012-05-02 11:02:53', 'send_message')
,(22, '2012-05-02 11:03:28', 'logout')
,(22, '2012-05-02 11:04:09', 'login')
,(22, '2012-05-02 11:03:16', 'send_message')
,(22, '2012-05-02 11:03:43', 'search_run')
-- my addition
,(22, '2012-05-03 11:00:00', 'login')  -- fail
,(22, '2012-05-03 11:00:05', 'logout')
,(22, '2012-05-03 11:00:10', 'login')  -- fail
,(22, '2012-05-03 11:00:15', 'logout')
,(22, '2012-05-03 11:00:20', 'login')  -- fail
,(22, '2012-05-03 11:00:25', 'logout')
,(22, '2012-05-03 11:00:30', 'login')  -- success
,(22, '2012-05-03 11:00:40', 'send_message')
,(22, '2012-05-03 11:00:50', 'logout')

,(23, '2012-05-02 11:02:39', 'login')
,(23, '2012-05-02 11:02:53', 'send_message')
,(23, '2012-05-02 11:03:28', 'logout')
,(23, '2012-05-02 11:04:09', 'login')
,(23, '2012-05-02 11:05:16', 'send_message')
,(23, '2012-05-02 11:05:43', 'search_run')
;

CREATE TABLE



INSERT 0 21


-- 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  (
      SELECT date, event
           , count(*) FILTER (WHERE event = 'login')
                      OVER (ORDER BY date ROWS BETWEEN UNBOUNDED PRECEDING AND CURRENT ROW) AS grp
      FROM   tbl
      WHERE  user_id = 22  -- given user
      ) sub1
   GROUP  BY grp
   ) sub2;


pct_sent_in_time


33.33



SELECT 1


-- 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 (PARTITION BY user_id ORDER BY date ROWS BETWEEN UNBOUNDED PRECEDING AND CURRENT ROW) AS grp
      FROM   tbl
      ) sub1
   GROUP  BY user_id, grp
   ) sub2
GROUP  BY user_id;


user_id
pct_sent_in_time


22
33.33

23
100.00



SELECT 2


-- Nick's query may count fails in quick succession as successes
WITH CTE AS (
  SELECT user_id, 
         COUNT(*) AS logins,
         COUNT(*) FILTER (WHERE EXISTS 
                          (SELECT *
                           FROM tbl d2 
                           WHERE d2.user_id = d1.user_id
                             AND d2.date > d1.date
                             AND d2.date - d1.date < interval '2 minutes'
                             AND d2.event = 'send_message')) AS sent
  FROM tbl d1
  WHERE d1.event = 'login'
  GROUP BY user_id
)
SELECT user_id, logins, sent, 100.0 * sent / logins AS pct_sent
FROM CTE


user_id
logins
sent
pct_sent


22
6
5
83.3333333333333333

23
2
2
100.0000000000000000



SELECT 2