SQL Window Function

Question

I have a data frame that looks like this:

ID   DATE
ABC  2018-02-07
ABC  2018-02-10
ABC  2018-02-13
ABC  2018-02-22
ABC  2018-02-26
ABC  2018-02-28
ABC  2018-04-06
ABC  2018-04-06
ABC  2018-04-12

I am trying to add 3 additional columns : (1) Earliest Date for all records (2) Time between Date and Earliest Date (3) Return the nth occurence # for the record, return maximum of nth record for duplicate dates. I am expecting the following as an output:

PEL_ID TRANSACTIONDATEDIFF EARLIESTEXPOSURE    TIMEDIFF      NTH_FREQUENCY
ABC  2018-02-07     2018-02-07            0             1
ABC  2018-02-10     2018-02-07            3             2 
ABC  2018-02-13     2018-02-07            6             3
ABC  2018-02-22     2018-02-07           15             4
ABC  2018-02-26     2018-02-07           19             5 
ABC  2018-02-28     2018-02-07           21             6
ABC  2018-04-06     2018-02-07           58             8
ABC  2018-04-12     2018-02-07           64             9

This is my SQL Code:

SELECT 
PEL_ID,TRANSACTIONDATEDIFF,EARLIESTEXPOSURE,TIME_DIFF,MAX(NTH_FREQUENCY) 
FROM (
SELECT C.*,ROW_NUMBER() OVER(PARTITION BY PEL_ID ORDER BY PEL_ID) AS 
NTH_FREQUENCY FROM
(SELECT A.PEL_ID,A.TRANSACTIONDATEDIFF,B.EARLIESTEXPOSURE, 
(A.TRANSACTIONDATEDIFF-B.EARLIESTEXPOSURE) AS TIME_DIFF FROM
CAMP_31323_TODATE A JOIN (SELECT PEL_ID,MIN(TRANSACTIONDATEDIFF) AS 
EARLIESTEXPOSURE FROM CAMP_31323_TODATE
GROUP BY PEL_ID) B ON A.PEL_ID=B.PEL_ID
ORDER BY A.PEL_ID) C
  )
GROUP BY PEL_ID,TRANSACTIONDATEDIFF,EARLIESTEXPOSURE,TIME_DIFF
ORDER BY PEL_ID,TRANSACTIONDATEDIFF ASC;

Most of this code is working other than the nth_frequency and this is the output:

PEL_ID TRANSACTIONDATEDIFF EARLIESTEXPOSURE    TIMEDIFF      NTH_FREQUENCY
ABC  2018-02-07     2018-02-07            0             3
ABC  2018-02-10     2018-02-07            3             6 
ABC  2018-02-13     2018-02-07            6             8
ABC  2018-02-22     2018-02-07           15             2
ABC  2018-02-26     2018-02-07           19             7 
ABC  2018-02-28     2018-02-07           21             1
ABC  2018-04-06     2018-02-07           58             5
ABC  2018-04-12     2018-02-07           64             9

I am not sure why this is happening. Any help will be appreciated wholeheartedly. Thanks in advance.

Wernfried Domscheit · Accepted Answer · 2018-11-01 20:58:51Z

Not the full solution but perhaps a starting point:

with t as (
  select 'ABC' AS ID, DATE '2018-02-07' as D from dual union all
  select 'ABC' AS ID, DATE '2018-02-10' as D from dual union all
  select 'ABC' AS ID, DATE '2018-02-13' as D from dual union all
  select 'ABC' AS ID, DATE '2018-02-22' as D from dual union all
  select 'ABC' AS ID, DATE '2018-02-26' as D from dual union all
  select 'ABC' AS ID, DATE '2018-02-28' as D from dual union all
  select 'ABC' AS ID, DATE '2018-04-06' as D from dual union all
  select 'ABC' AS ID, DATE '2018-04-06' as D from dual union all
  select 'ABC' AS ID, DATE '2018-04-12' as D from dual),
g as 
   (select  
      ID,
      D as TRANSACTIONDATEDIFF,
      MIN(D) OVER (PARTITION BY ID ORDER BY D) as EARLIESTEXPOSURE,
      D - MIN(D) OVER (PARTITION BY ID ORDER BY D) as TIMEDIFF,
      RANK() OVER (PARTITION BY ID ORDER BY D) AS NTH_FREQUENCY
   from t)
select distinct *
from g
order by 2;


+----------------------------------------------------------------+
|ID  |TRANSACTIONDATEDIFF|EARLIESTEXPOSURE|TIMEDIFF|NTH_FREQUENCY|
+----------------------------------------------------------------+
|ABC |07-FEB-18          |07-FEB-18       |0       |1            |
|ABC |10-FEB-18          |07-FEB-18       |3       |2            |
|ABC |13-FEB-18          |07-FEB-18       |6       |3            |
|ABC |22-FEB-18          |07-FEB-18       |15      |4            |
|ABC |26-FEB-18          |07-FEB-18       |19      |5            |
|ABC |28-FEB-18          |07-FEB-18       |21      |6            |
|ABC |06-APR-18          |07-FEB-18       |58      |7            |
|ABC |12-APR-18          |07-FEB-18       |64      |9            |
+----------------------------------------------------------------+

Collectives™ on Stack Overflow

SQL Window Function

1 Answer 1

Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

Comments

Your Answer

Sign up or log in

Post as a guest

Related