Please, I need your help guys to solve this

Question

I am new in sql and I need some help. I have a table with 4 columns (user_id; type: string, action; type string, time; type: timestamp, session_id: string)

| id   | action     | time                | session_id |

+------+--------+---------------------+------------+

| 1    | action_1   | 2016-10-01 09:00:00 | 1111       |

| 1    | action_2   | 2016-10-01 15:00:00 | 1111       |

| 1    | xxxx       | 2016-10-01 19:10:00 | 1111       |

| 1    | action_4   | 2016-10-01 20:00:00 | 1111       |

| 1    | action_1   | 2016-11-02 13:00:00 | 1122       |

| 1    | yyyy       | 2016-11-02 16:00:00 | 1122       |

| 1    | action_3   | 2016-11-02 23:00:00 | 1122       |

| 2    | action_1   | 2016-11-01 20:30:00 | 2222       |

| 2    | action_2   | 2016-11-01 20:50:41 | 2222       |

| 2    | yyyy       | 2016-11-01 21:00:00 | 2222       |

| 2    | action_4   | 2016-11-01 21:30:00 | 2222       |

| 3    | action_1   | 2016-11-04 09:50:00 | 3333       |

| 3    | action_2   | 2016-11-04 10:00:00 | 3333       |

| 3    | action_3   | 2016-11-04 10:10:00 | 3333       |

| 3    | action_4   | 2016-11-04 10:25:00 | 3333       |

| 3    | action_5   | 2016-11-04 10:40:00 | 3333       |

| 3    | xxxx       | 2016-11-04 11:00:00 | 3333       |

| 3    | action_7   | 2016-11-04 15:00:00 | 3333       |

("xxxx" and "yyyy" are any actions which should be found).
The question is: number of cases when a specific action is the penultimate action in a session (the final result of the query should be per action and should look like:
action | count(action)
-----------------------
Action | Number
......

Thanks in advance.

Answer 1

Assuming that you want count the occurrence for xxxx and yyyy You should use in a group by

select action, count(*) 
from my_table 
where action in ('xxxx', 'yyyy')
group by action