SQL window function with where clause

Question

I am trying to match two types of events for users. I want to see all events "B" together with the most recent event "A" for this user before event "A". How to do it? In particular, I am trying to do this in Postgres.

I was hoping that the where clause could be used in the window function, in which case I could essentially do LAG () with "where event = 'A", but this doesn't seem possible.

Any recommendations?

Sample data:

|user |time|event|
|-----|----|-----|
|Alice|1   |A    |
|Bob  |2   |A    |
|Alice|3   |A    |
|Alice|4   |B    |
|Bob  |5   |B    |
|Alice|6   |B    |

Desired Result:

|user |event_b_time|last_event_a_time|
|-----|------------|-----------------|
|Alice|4           |3                |
|Bob  |5           |2                |
|Alice|6           |3                |

Answer 1

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

FILTER

, lag() FILTER . Gordon, max(), 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_time
FROM subq
WHERE event = 'B';

, PostgreSQL 9.5.4.

FILTER!

Answer 2

:

select t.*
from (select t.*,
             lag(time) filter (where event = 'A') (partition by user order by time)
      from t
     ) t
where event = 'B';

, / .

Answer 3

. B, A . - :

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 t
WHERE event='B';

, t ( ).