CODEWITHSUNDEEP
sqlamazon-redshift
user8834780
user8834780

Reputation: 1670

Conditional time to status calculation

I am trying to calculate how long it takes a rep to have x amount of clients apply for service: meaning I need the time between date_created - ie. date the rep was onboarded, and when rep reaches a certain "status". Status is reached when x of the rep's clients (= users) have a non-null date_applied- ie. date user signed up.

x is minimum criteria to reach each "status", and ties back to a previous question: Aggregate case when inside non aggregate query where I am currently calculating "status" like so:

  case when count(date_applied) over (partition by rep_id) >=10 then 'status1'
    when count(date_applied) over (partition by rep_id) >=5 then 'status2'
    when count(date_applied) over (partition by rep_id) >=1 then 'status3'
    else 'no_status' end status

So it takes 10 clients to reach status1, 5 to reach status2 and 1 to reach status3. These are the criteria for each "status", so if you have 7 users for example, you still calculate status2 based on the date the 5th user applied.

I think calculating time_to_status1/2/3 (what i am trying to get at) should look something like this:

case when count(date_applied) over (partition by rep_id) >=10 then
  datediff(day, date_created, date_applied for the 10th user that applied with that rep) end as time_to_status1,
case when count(date_applied) over (partition by rep_id) >=5 then
  datediff(day, date_created, date_applied for the 5th user that applied with that rep) end as time_to_status2,
case when count(date_applied) over (partition by rep_id) >=1 then
  datediff(day, date_created, date_applied for the 1st user that applied with that rep) end as time_to_status3

Any help is greatly appreciated!

--Edit--

Sample current data:

rep_id user_id date_created          date_applied         status
1      1       1/1/2018 6:43:22 AM   1/5/2018 2:45:15 PM  status2 
1      2       1/1/2018 6:43:22 AM   1/5/2018 3:35:15 PM  status2 
1      3       1/1/2018 6:43:22 AM   1/6/2018 4:25:15 PM  status2 
1      4       1/1/2018 6:43:22 AM   1/7/2018 5:05:15 PM  status2 
1      5       1/1/2018 6:43:22 AM   1/10/2018 3:35:15 PM status2 
1      6       1/1/2018 6:43:22 AM   1/15/2018 12:55:23 PM status2 
2      7       1/12/2018 1:13:42 PM  1/15/2018 4:25:15 PM status3
2      8       1/12/2018 1:13:42 PM  1/16/2018 1:05:15 PM status3 
2      9       1/12/2018 1:13:42 PM  1/16/2018 3:35:15 PM status3 
3      10      1/20/2018 10:13:15 AM 1/26/2018 7:25:15 PM status3
4      11      1/21/2018 3:33:23 PM  (null)               no_status

Desired output:

 rep_id user_id date_created          date_applied         status  time_to_status1  time_to_status2  time_to_status3
1      1       1/1/2018 6:43:22 AM   1/5/2018 2:45:15 PM  status2  (null)  9  (null)
1      2       1/1/2018 6:43:22 AM   1/5/2018 3:35:15 PM  status2  (null)  9  (null)
1      3       1/1/2018 6:43:22 AM   1/6/2018 4:25:15 PM  status2  (null)  9  (null)
1      4       1/1/2018 6:43:22 AM   1/7/2018 5:05:15 PM  status2  (null)  9  (null)
1      5       1/1/2018 6:43:22 AM   1/10/2018 3:35:15 PM status2  (null)  9  (null)
1      6       1/1/2018 6:43:22 AM   1/15/2018 12:55:23 PM status2  (null)  9 (null)
2      7       1/12/2018 1:13:42 PM  1/15/2018 4:25:15 PM status3  (null) (null) 3
2      8       1/12/2018 1:13:42 PM  1/16/2018 1:05:15 PM status3  (null) (null) 3
2      9       1/12/2018 1:13:42 PM  1/16/2018 3:35:15 PM status3  (null) (null) 3
3      10      1/20/2018 10:13:15 AM 1/26/2018 7:25:15 PM status3 (null) (null) 6
4      11      1/21/2018 3:33:23 PM  (null)               no_status (null) (null) (null)

rep_id=1 has status2 because he has 6 users with with a non null date_applied, so time_to_status2 in his case is based on date_applied of 5th client rep signed up: datediff(day, '1/1/2018 6:43:22 AM', '1/10/2018 3:35:15 PM') = 9 days

rep_id=2 has status3 because he has 3 users with a non null date_applied, so time_to_status3 in his case is based on date_applied of 1st client rep signed up: datediff(day, '1/12/2018 1:13:42 PM', '1/15/2018 4:25:15 PM') = 3 days

rep_id=3 has status3 because he has 1 (>=1) user with a non null date_applied, so time_to_status3 in his case is datediff(day, '1/20/2018 10:13:15 AM', '1/26/2018 7:25:15 PM') = 6 days

Upvotes: 1

Views: 88

Answers (1)

user8834780
user8834780

Reputation: 1670

Based on @Parfait's deleted hint, and @Gordon's answer on a different question, I was able to come up with an answer:

with cte as 
(
initial query with:
 case when count(client_signup_date) over (partition by rep_id) >=10 then 'status1'
        when count(client_signup_date) over (partition by rep_id) >=5 then 'status2'
        when count(client_signup_date) over (partition by rep_id) >=1 then 'status3'
        else 'none' end status,
      row_number() over(partition by rep_id order by client_signup_date) as rank
)

select *, 
        max(case when status = 'status1' and rank = 10
                 then datediff(day, advisor_onboard_date, client_signup_date)
            end) over (partition by rep_id) as time_to_status1,
        max(case when status = 'status2' and rank = 5
                 then datediff(day, advisor_onboard_date, client_signup_date)
            end) over (partition by rep_id) as time_to_status2,
        max(case when status = 'status3' and rank = 1
                 then datediff(day, advisor_onboard_date, client_signup_date)
            end) over (partition by rep_id) as time_to_status3
into #t
from cte

Upvotes: 0

Related Questions