getting first sunday using the window function

I need to get the first Sunday from each month and add that as a new column to the original table. The below query works only when the first row of a group starts with Sunday i.e for March month. It doesn’t work when the month is not starting with Sunday.

Query:

with t1 (select *, first(case when wk='Sun' then dt end) over(partition by mon order by dt) fs 
from cc ) select * from t1

Incorrect result

+---+---+---+----------+------------------+----------+
|id |mon|wk |dt        |r                 |fs        |
+---+---+---+----------+------------------+----------+
|1  |Jan|Wed|2020-01-01|60.371644652731526|null      | <-- *need 2020-01-05*
|2  |Jan|Thu|2020-01-02|67.98230647367191 |null      |
|3  |Jan|Fri|2020-01-03|55.9852350207294  |null      |
|4  |Jan|Sat|2020-01-04|25.28876524785283 |null      |
|5  |Jan|Sun|2020-01-05|91.32462122371145 |null      |
|6  |Jan|Mon|2020-01-06|53.724752308066606|null      |
|7  |Jan|Tue|2020-01-07|77.77398211017427 |null      |
|8  |Jan|Wed|2020-01-08|65.48278270983366 |null      |
|9  |Jan|Thu|2020-01-09|55.9667183724826  |null      |
|10 |Jan|Fri|2020-01-10|26.216674645270466|null      |
|32 |Feb|Sat|2020-02-01|63.53257131288066 |null      |  *<-- need 2020-02-02*
|33 |Feb|Sun|2020-02-02|15.062647294264197|null      |
|34 |Feb|Mon|2020-02-03|33.139296037799525|null      |
|35 |Feb|Tue|2020-02-04|53.17446345740387 |null      |
|36 |Feb|Wed|2020-02-05|31.589549474516645|null      |
|37 |Feb|Thu|2020-02-06|23.382346350080184|null      |
|38 |Feb|Fri|2020-02-07|51.68563021763766 |null      |
|39 |Feb|Sat|2020-02-08|50.619708431176356|null      |
|40 |Feb|Sun|2020-02-09|75.46581917512121 |null      |
|41 |Feb|Mon|2020-02-10|56.45126797786632 |null      |
|61 |Mar|Sun|2020-03-01|0.4296510373365203|2020-03-01|
|62 |Mar|Mon|2020-03-02|81.46159608460079 |2020-03-01|
|63 |Mar|Tue|2020-03-03|24.80751007585814 |2020-03-01|
|64 |Mar|Wed|2020-03-04|48.633874958182055|2020-03-01|
|65 |Mar|Thu|2020-03-05|75.59001182664245 |2020-03-01|
|66 |Mar|Fri|2020-03-06|32.68590135343421 |2020-03-01|
|67 |Mar|Sat|2020-03-07|72.94751325972402 |2020-03-01|
|68 |Mar|Sun|2020-03-08|74.00972887997449 |2020-03-01|
|69 |Mar|Mon|2020-03-09|40.234055771642275|2020-03-01|
|70 |Mar|Tue|2020-03-10|46.633603473256294|2020-03-01|
+---+---+---+----------+------------------+----------+

How can this be solved using window function?

Answer

I would suggest:

select t1.*,
       min(case when wk = 'Mon' then dt end) over (partition by mon) as first_monday
from t1;

This is a lot like your logic, but it uses min() instead of first().

Actually, your table doesn’t seem to have a year column — and you really want the year included in the partition by. So if one really isn’t available, then:

select t1.*,
       min(case when wk = 'Mon' then dt end) over (partition by year(dt), mon) as first_monday
from t1;

Advertisement

Answer