sql - Update the column according to order status

Question

INPUT:

CUSTOMER_NAME       ORDER_ID      STATUS
-----------------------------------------------
  john                j1          delivered
  john                j2          delivered
  david               d1          submitted
  david               d3          created
  smith               s1          submitted
  krish               k1          created

A pizza company is taking orders from customers and each pizza order is added to their database as a separate order each order has an associated status "CREATED or SUBMITTED or DELIVERED". final status is calculated bases on status as follows

When all orders for a customer have a status of DELIVERED that customers order has a final status of "COMPLETED"

If a customer has some orders that are not DELIVERED and some orders that are DELIVERED that final status is "IN PROGRESS"

If all of a customers orders are SUBMITTED the final status is "AWAITING PROGRESS"

Otherwise the final status is "AWAITING SUBMISSION"

Desired output:

CUSTOMER_NAME                 FINAL_STATUS
--------------------------------------------------
david                         IN PROGRESS
john                          COMPLETED
krish                         AWAITING SUBMISSION
smith                         AWAITING PROGRESS

Answer 1

here is one way:

select customer_name 
   , case when count(case when status <> 'created' then 1 end) = 0 then 'awaiting submission'  
          when count(case when status <> 'submitted' then 1 end) = 0 then 'awaiting progress'
          when count(case when status <> 'delivered' then 1 end) > 0 then 'In progress'
          when count(case when status <> 'delivered' then 1 end) = 0 then 'Completed'          
     end
from orders
group by customer_name