Check if a value exists in the column for each group

Question

Having a hard time setting what I am trying to do in words, so the search is also difficult.

Basically, I'm trying to check if a specific value exists in a column divided into groups, and then propagate that value forward.

In this example, I want to check if the user has completed the tutorial and set a flag that wraps forward.

pk | user | ... | activity
 1 |    A | ... |  "login"
 2 |    A | ... |  "started_tutorial"
 3 |    A | ... |  "completed_tutorial"
 4 |    A | ... |  "some other activity"
 5 |    A | ... |  "logout"
 5 |    B | ... |  "login"
 6 |    B | ... |  "logout"

I think it should be something like

select *,
    check(activity in ('completed_tutorial')) as completed_activity
    from tbl

but I don’t think I can use checkselect in the expression, and this will be a constant flag, and not true only after it is found.

An example of what I'm trying to get:

pk | user | ... | activity               | completed_tutorial
 1 |    A | ... |  "login"               |                 0
 2 |    A | ... |  "started_tutorial"    |                 0
 3 |    A | ... |  "completed_tutorial"  |                 1
 4 |    A | ... |  "some other activity" |                 1
 5 |    A | ... |  "logout"              |                 1
 5 |    B | ... |  "login"               |                 0
 6 |    B | ... |  "logout"              |                 0

Answer 1

SQL HAVING. , , , , :

SELECT user FROM tbl
GROUP BY user, activity
HAVING activity = 'completed_tutorial';

EDIT: , OP , . , .

SELECT *, COALESCE(date >= (
    SELECT date FROM tbl WHERE activity = 'completed_tutorial'
    AND user = outertbl.user
), FALSE)
FROM tbl AS outertbl
ORDER BY date

, N², , .

Answer 2

-

SELECT a.*, coalesce(b.completed, 0)
FROM tbl a 
LEFT JOIN (SELECT user, 1 completed 
           FROM tbl 
           WHERE user = a.user 
                 AND activity='completed_tutorial') b 
ON a.user = b.user AND b.pk >= a.pk

, activity='completed_tutorial', "". , .

Answer 3

I'm not sure about the speed of this, but what about the next solution?

SELECT
    user
    ,max(CASE
            WHEN activity = "completed_tutorial" THEN 1
            ELSE 0
            END) AS completed_tutorial
  FROM tbl
  GROUP BY user
 ;