How to Find the Next Activity of Group B Following Group A Activities in PostgreSQL?

Conditional Lead/Lag Functions in PostgreSQL

You're trying to identify the next activity performed by users belonging to group B, after they've completed an activity from group A. Essentially, you need to locate the first subsequent activity from group B for each user.

One approach is to utilize conditional window functions. However, in PostgreSQL, FILTER clauses can only be applied to aggregate functions within window functions, not to core window functions like lead() or lag().

Therefore, a viable solution is to employ a DISTINCT ON construct alongside CASE and window functions:

SELECT name
     , CASE WHEN a2 LIKE 'B%' THEN a1 ELSE a2 END AS activity
     , CASE WHEN a2 LIKE 'B%' THEN a2 END AS next_activity
FROM  (
   SELECT DISTINCT ON (name)
          name
        , lead(activity) OVER (PARTITION BY name ORDER BY time DESC) AS a1
        , activity AS a2
   FROM   t
   WHERE (activity LIKE 'A%' OR activity LIKE 'B%')
   ORDER  BY name, time DESC
   ) sub;

This query uses a subquery to:

1. Use DISTINCT ON (name) to identify the latest activity (a1) from group A for each user.
2. Apply lead() to obtain the next activity (a2) for each row.
3. Utilize CASE and LIKE 'B%' to detect and assign the next activity if it belongs to group B.

Alternatively, for small datasets with a limited number of rows per user, the following query can be used:

SELECT name
     , activity AS a1
     , lead(activity) OVER (PARTITION BY name ORDER BY time DESC) AS next_activity
FROM   t
WHERE (activity LIKE 'A%' OR activity LIKE 'B%')
ORDER  BY name, time DESC;

This query simplifies the logic by directly searching for group A activities and retrieving the subsequent activity, regardless of its group affiliation.

The above is the detailed content of How to Find the Next Activity of Group B Following Group A Activities in PostgreSQL?. For more information, please follow other related articles on the PHP Chinese website!