CODEWITHSUNDEEP
sqlhive
Elijah
Elijah

Reputation: 25

Hive/SQL How do you access the value of the column which you just computed for previous rows?

I have a table uv_user_date looks like this: enter image description here

Its basically a user log in table which shows the cumulative login days partition by user_id. And the column pre show the last login date of a user login record.

Based on this I want to compute the consecutive login days for each user record.

The answer should be : enter image description here My idea is : for a record

The Code would be:

SELECT *,
   if(pre = date_add(uv_date, -1), last(consecutive_days) + 1, 1) consecutive_days
FROM uv_user_date

Is there any way to get the value of last(consecutive_days)

Upvotes: 0

Views: 117

Answers (1)

serge_k
serge_k

Reputation: 1772

First find date difference

tbl1:
select *, 
       if(pre = NULL, 1, datediff(uv_date, pre)) as diff
  from your_table

then difference between cumulative sum of difference and accumulative_uv_date for each user_id, you want to use it as rank

tbl2:
select *, 
sum(diff) over (partition by user_id order by uv_date rows between unbounded preceding and current) - accumulative_uv_date as rnk
   from tbl1

finally, count consecutive days

select user_id, uv_date, rnk
row_number() over (partition by user_id, rnk order by uv_date) as consecutive_days
  from tbl2

Upvotes: 2

Related Questions