SQL query to return ids that only signed up but didn't take any other action-CodePudding

I have an event table that looks like this.

| id | event   | date       |
|----|---------|------------|
| 1  | sign up | 28/01/2022 |
| 1  | log in  | 29/01/2022 |
| 1  | delete  | 30/01/2022 |
| 2  | sign up | 31/01/2022 |
| 3  | log in  | 01/02/2022 |
| 4  | sign up | 02/02/2022 |
| 4  | NULL    | 03/02/2022 |

I want to write a query that selects distinct ids and that only signed up and did not carry out any other event.

Thanks.

CodePudding user response：

select id from t
group by id 
having count(distinct event)=1 and max(event)='sign up'

or if you want null as a value:

having count(distinct coalesce(event,'x'))=1 and max(event)='sign up'

CodePudding user response：

You can write this with a NOT EXISTS condition:

select t1.*
from the_table t1
where not exists (select * 
                  from the_table t2
                  where t2.id = t1.id
                    and t2."date" > t1."date"
                    and t2.event <> t1.event)
  and t1.event = 'sign up';

You did not specify if the NULL event for id = 4 is to be taken as "something else" or "nothing".