SQL window function with a where clause?

Just tried Gordon's approach using PostgreSQL 9.5.4, and it complained that

FILTER is not implemented for non-aggregate window functions

which means using lag() with FILTER is not allowed. So I modified Gordon's query using max(), a different window frame, and CTE:

WITH subq AS (  SELECT    "user", event, time as event_b_time,    max(time) FILTER (WHERE event = 'A') OVER (      PARTITION BY "user"      ORDER BY time      ROWS BETWEEN UNBOUNDED PRECEDING AND 1 PRECEDING    ) AS last_event_a_time  FROM events  ORDER BY time)SELECT  "user", event_b_time, last_event_a_timeFROM subqWHERE event = 'B';

Verified that this works with PostgreSQL 9.5.4.

Thanks to Gordon for the FILTER trick!

There is not need for window functions here. Just find all B events, and for each one of them, find the most recent A of the same user via a subquery. Something like that should do it:

SELECT    "user",    time AS event_b_time,    (SELECT time AS last_event_a_time     FROM t t1     WHERE "user"=t.user AND event='A' AND time<t.time     ORDER BY time DESC LIMIT 1)FROM tWHERE event='B';

I assume that the table is called t (I used it twice).