CODEWITHSUNDEEP
sqlhivewindow-functionsrow-number
enavuio
enavuio

Reputation: 1500

Create a Monthly Label value by last day of month

I have a dataset that I have applied a

row_num() partition by month order by day_date desc

My results are such, not limited to - just for example

Month        Day            User        Active        row_num
Jan-2021     2021-01-28     A           yes            1
Jan-2021     2021-01-27     A           yes            2
Jan-2021     2021-01-28     A           yes            3
Feb-2021     2021-02-25     A           no             1
Feb-2021     2021-02-15     A           yes            2
Feb-2021     2021-02-01     A           no             3
Jan-2021     2021-01-28     B           yes            1
Jan-2021     2021-01-28     B           yes            2
Jan-2021     2021-01-28     B           yes            3
Feb-2021     2021-02-28     B           no             1
Feb-2021     2021-02-08     B           yes            2

The appendage I would like to add is, if the user is active at the end of their month, I would like to label the month active. Logic is if row_num = 1 and active = yes, label full month, yes vice versa row_num = 1 and active = no label month_active = no.

I am currently stuck at how to apply a value to the partition of the month but evaluate at the daily level. Appreciate it in advance.

Month        Day            User        Active        row_num        month_active
Jan-2021     2021-01-28     A           yes            1             yes
Jan-2021     2021-01-27     A           yes            2             yes
Jan-2021     2021-01-28     A           yes            3             yes
Feb-2021     2021-02-25     A           no             1             no
Feb-2021     2021-02-15     A           yes            2             no
Feb-2021     2021-02-01     A           no             3             no             
Jan-2021     2021-01-28     B           yes            1             yes
Jan-2021     2021-01-28     B           yes            2             yes
Jan-2021     2021-01-28     B           yes            3             yes
Feb-2021     2021-02-28     B           no             1             no
Feb-2021     2021-02-08     B           yes            2             no

Upvotes: 1

Views: 55

Answers (1)

Gordon Linoff
Gordon Linoff

Reputation: 1270463

One method is to use first_value():

select t.*,
       first_value(active) over (partition by month order by day desc) as month_active
from t;

Or if you use your query as a subquery, you can use:

select t.*,
       max(case when seqnum = 1 then active end) over (partition by month) as month_active
from t;

Upvotes: 1

Related Questions