which shipping mode has the highest number of delayed orders

Question

I'm in the middle of an SQL tutorial, and the question is which shipping mode has the highest number of delayed orders. The code i have tried using is as given below. The output is empty. Can someone please tell me where i'm going wrong?

WITH summary AS
(
SELECT 
order_id, Real_Shipping_Days, Scheduled_Shipping_Days, Shipping_Mode, order_status,
CASE WHEN order_status = 'SUSPECTED FRAUD' or order_status = 'CANCELED' THEN 
'CANCELLED SHIPMENT'
WHEN Real_Shipping_Days<Scheduled_Shipping_Days THEN 'WITHIN SCHEDULE'
WHEN Real_Shipping_Days=Scheduled_Shipping_Days THEN 'ON TIME'
WHEN Real_Shipping_Days<=Scheduled_Shipping_Days+2 THEN 'UPTO 2 DAYS OF DELAY'
WHEN Real_Shipping_Days>Scheduled_Shipping_Days THEN 'BEYOND 2 DAYS OF DELAY'

ELSE 'OTHERS' END AS shipment_compliance
FROM
orders
)
SELECT COUNT(order_id) as order_count, Shipping_Mode
FROM summary 
WHERE shipment_compliance = 'UPTO 2 DAYS OF DELAY' AND shipment_compliance = 'BEYOND 
2 DAYS OF DELAY'
GROUP BY Shipping_Mode DESC;

Answer 1

with summary as
(
select
order_id,Real_Shipping_Days, Scheduled_Shipping_Days,shipping_mode,
case when order_status='SUSPECTED_FRAUD' or order_status = 'CANCELED' then 'Cancelled Shipment'
when Real_Shipping_Days<Scheduled_Shipping_Days then 'Within Schedule'
when Real_Shipping_Days=Scheduled_Shipping_Days then 'On Time'
when Real_Shipping_Days<=Scheduled_Shipping_Days+2 then 'Upto 2 days of delay'
when Real_Shipping_Days>Scheduled_Shipping_Days+2 then 'Beyond 2 days of delay'
else 'Others' end as shipment_compliance
from orders
)
SELECT COUNT(order_id) as order_count, Shipping_Mode
FROM summary 
WHERE shipment_compliance in ('UPTO 2 DAYS OF DELAY' , 'BEYOND 2 DAYS OF DELAY')
GROUP BY Shipping_Mode
order by order_count desc;

Answer 2

This is the problem in SQL :

shipment_compliance = 'UPTO 2 DAYS OF DELAY' AND shipment_compliance = 'BEYOND 
2 DAYS OF DELAY'

You can use it like :

shipment_compliance in ( 'UPTO 2 DAYS OF DELAY' , 'BEYOND 
2 DAYS OF DELAY')

or

shipment_compliance = 'UPTO 2 DAYS OF DELAY' OR shipment_compliance = 'BEYOND 
2 DAYS OF DELAY'