CODEWITHSUNDEEP
sql
user8545255
user8545255

Reputation: 839

case statement within group by clause or partition clause

Customer   Decision      req_date      dept        salary
   A       Approved     2017-06-13      IT          1000
   A       Approved     2017-06-13      Sales       1000
   A       Pending      2017-06-13      IT          500
   B       Pending      2017-10-23      IT          800     
   B       final_stage  2017-10-20      Sales       400
   B       final_stage  2017-03-19      Sales       400
   B       final_stage  2017-03-20      Marketing   300
   B       pending      2017-10-20      Marketing   400
   B       Pending      2017-04-23      HR          800

For a given customer ID,

case 1:If the decision is Approved, then retain all the approved records for that customer and drop others.

Case 2: If the customer doesn't have any Approved decision then ,retain the records of particular customer based on latest "req_date" and records within 5 days of this most recent "req_date" and pick the record based on the lowest salary across unique departments(dept).

Customer   Decision      req_date      dept        salary
   A       Approved     2017-06-13      IT          1000
   A       Approved     2017-06-13      Sales       1000
   B       Pending      2017-10-23      IT          800     
   B       final_stage  2017-10-20      Sales       400
   B       pending      2017-10-20      Marketing   400

Upvotes: 0

Views: 70

Answers (1)

Gordon Linoff
Gordon Linoff

Reputation: 1269553

This logic is quite complicated. The following calculates:

  • The maximum req_date for each customer.
  • The total number of approved for a customer.
  • The sequence number by customer and department by salary.

These are then combined with logic to achiever what you want:

select t.*
from (select t.*,
             row_number() over (partition by customer, dept order by salary asc) as seqnum
      from (select t.*,
                   max(req_date) over (partition by customer) as max_req_date,
                   count(*) filter (where decision = 'Approved') over (partition by customer) as num_approved
            from t
           ) t
      where decision = 'Approved' or
            req_date >= max_req_date - interval '5 day'
     ) t
where decision = 'Approved' or
      (num_approved = 0 and seqnum = 1);

Upvotes: 1

Related Questions