I'm working on a SQL query on BigQuery and trying to find the second minimum login time for each user id.
| User_id | LoginTime |
|---|---|
| 1 | 2022-11-06 08:45:17 |
| 1 | 2022-11-07 09:52:27 |
| 1 | 2022-11-08 02:08:54 |
| 2 | 2022-11-04 08:10:38 |
| 2 | 2022-11-07 16:46:34 |
| 2 | 2022-11-18 01:26:53 |
And I want to find the following result.
| User_id | SecondLoginTime |
|---|---|
| 1 | 2022-11-07 09:52:27 |
| 2 | 2022-11-07 16:46:34 |
Could anyone help me figure out how to approach this problem?
CodePudding user response：
We can use the RANK() analytic function:
WITH cte AS (
SELECT *, RANK() OVER (PARTITION BY User_id ORDER BY LoginTime) rnk
FROM yourTable
)
SELECT User_id, LoginTime AS SecondLoginTime
FROM cte
WHERE rnk = 2
ORDER BY User_id;
Note that, depending on the behavior you want if ties can happen, you may want to use DENSE_RANK() instead of RANK().