Find last occurring value within record in PostgreSQL -

i'm not new sql, new postgresql , struggling adapt current knowledge in different environment.

i trying create variable captures whether or not stays active, skips, or churns within 0/1 time series variable. example, in data below, dataset include variables id,time, , voted, , create variable "skipped":

id     time     voted     skipped 1      1        1         active 1      2        0         skipped 1      3        1         active 2      1        1         active 2      2        0         churned 2      3        0         churned 3      1        1         active 3      2        1         active 3      3        0         churned 

the rule coding "skipped" pretty simple: if 1 last record, person "active" , zeroes count "skipped", if 0 last record, person "churned".

the record id = 1 skip because id non-zero @ time 3 after being 0 @ time 2. other 2 cases, 0 final value "churned". can help? i've been noodling on day, , hitting wall.

this isn't particularly elegant, should meet needs:

with votes (   select     id, time, voted,     max(time) on (partition id) max_time   voter_data ) select   v1.id, v1.time, v1.voted,   case     when v1.voted = 1 'active'     when v2.voted = 1 'skipped'     else 'churned'   end skipped   votes v1   join votes v2 on     v1.id = v2.id ,     v1.max_time = v2.time 

in nutshell, first figure out last record each voter id, , self-join on resulting table isolate last id.

there chance produce multiple results -- if it's possible have same id vote twice @ same time. if that's case, want row_number() instead of max().

results on data:

1   1   1   'active' 1   2   0   'skipped' 1   3   1   'active' 2   1   1   'active' 2   2   0   'churned' 2   3   0   'churned' 3   1   1   'active' 3   2   1   'active' 3   3   0   'churned'