How to use row_number to count dates after a desired anchor date?-CodePudding

I have a dataset where I want to count the rows before and after my anchor date. I think a window function using row_number() would work, but I am not sure how it would be written.

my current table:

order_id    contact_id    placed_at              anchor_date
13236647    123456        2020-06-24T12:47:18   
16253983    123456        2020-07-19T05:54:52   
16720335    123456        2020-08-20T02:02:06   
17823059    123456        2020-09-17T02:02:04    2020-09-17T02:02:04
18523920    123456        2020-10-12T13:53:19   
19324467    123456        2020-11-12T01:02:18   
20234536    123456        2020-12-04T01:02:42   
70523487    654321        2015-09-21T09:25:25   
71234048    654321        2015-10-01T19:02:28   
14145443    654321        2020-03-28T10:21:57   
14134525    654321        2020-03-28T10:31:33   
11244748    654321        2020-04-03T06:20:57    2020-04-03T06:20:57

My desired output would look like this:

rows_before_anchor is numbering all the rows before anchor_date ordered by placed_at and grouped by contact_id.

rows_after_anchor is numbering all the rows after anchor_date ordered by placed_at grouped by contact_id

Here's what I tried:

SELECT
  order_id,
  contact_id,
  placed_at,
  ROW_NUMBER() OVER (PARTITION BY contact_id ORDER BY placed_at < anchor_date) AS rows_before_anchor,
  ROW_NUMBER() OVER (PARTITION BY contact_id ORDER BY placed_at > anchor_date) AS rows_after_anchor
FROM mytable

My desired table:

order_id  contact_id  placed_at            anchor_date         rows_before_anchor rows_after_anchor
13236647  123456      2020-06-24T12:47:18                      1                  
16253983  123456      2020-07-19T05:54:52                      2    
16720335  123456      2020-08-20T02:02:06                      3    
17823059  123456      2020-09-17T02:02:04  2020-09-17T02:02:04
18523920  123456      2020-10-12T13:53:19                                         1
19324467  123456      2020-11-12T01:02:18                                         2             
20234536  123456      2020-12-04T01:02:42                                         3
70523487  654321      2015-09-21T09:25:25                      1
71234048  654321      2015-10-01T19:02:28                      2            
14145443  654321      2020-03-28T10:21:57                      3
14134525  654321      2020-03-28T10:31:33                      4
11244748  654321      2020-04-03T06:20:57  2020-04-03T06:20:57

CodePudding user response：

Here's one way you can do it. First you need to identify all the rows either side of your anchor date and assign them a common grouping which is done in the CTE below. Once you have this grouping you can use it to apply the desired numbering by including it as a partition.

It's not clear from your sample data whether the row numbering should be zero or a blank string, since row numbers are integers by definition I've defaulted the blank values to be zero - if you really want blanks then just cast the row numbers as a varchar.

with grp as (
    select *, 
        Row_Number() over(partition by contact_id order by placed_at)
        - Row_Number() over(partition by contact_id, anchor_date order by placed_at) gnum
    from t
)

select order_id, contact_id, placed_at, anchor_date,
    case when anchor_date is null and gnum=0 then
        Row_Number() over(partition by contact_id, gnum order by placed_at)
    else 0 end as rows_before_anchor,
    case when anchor_date is null and gnum>0 then
        Row_Number() over(partition by contact_id, gnum order by placed_at)
    else 0 end as rows_after_anchor
from grp
order by contact_id, placed_at;

There's no Fiddle I know of for Amazon Redshift but see this example DB<>Fiddle using SQL Server, it should hopefully share the same or similar syntax.