﻿ SQL Challenges-1: Return the shipping and delivery rates - w3resource

# SQL Challenges-1: Return the shipping and delivery rates

## SQL Challenges-1: Exercise-77 with Solution

From the following table write a query in SQL to return the shipped and delivered rate for each order. Return order_id, shipped percentage, and delivered percentage.

Table: orderdetails

Structure:

FieldTypeNullKeyDefaultExtra
order_idintNO
order_statusvarchar(30)YES
order_datedateYES

Data:

order_idorder_statusorder_date
10001booked 2008-08-15
10001shipped2008-08-16
10002booked 2008-07-13
10002delivered2008-07-19
10003booked 2008-08-15
10003delivered2008-08-18
10004booked 2008-07-19
10004shipped2008-07-19

Sample Solution:

SQL Code(MySQL):

``````CREATE TABLE orderdetails (
order_id INT(5) NOT NULL,
order_status VARCHAR(30),
order_date DATE
);

insert into orderdetails values(10001,'booked','2008-08-15');
insert into orderdetails values(10001,'shipped','2008-08-16');
insert into orderdetails values(10002,'booked','2008-07-13');
insert into orderdetails values(10002,'delivered','2008-07-19');
insert into orderdetails values(10003,'booked','2008-08-15');
insert into orderdetails values(10003,'delivered','2008-08-18');
insert into orderdetails values(10004,'booked','2008-08-19');
insert into orderdetails values(10004,'shipped','2008-08-19');

WITH t1 AS (
SELECT
order_id,
SUM(CASE WHEN order_status = 'booked' THEN 1 ELSE 0 END) AS booked,
SUM(CASE WHEN order_status = 'shipped' THEN 1 ELSE 0 END) AS shipped,
SUM(CASE WHEN order_status = 'delivered' THEN 1 ELSE 0 END) AS delivered
FROM orderdetails
GROUP BY 1
ORDER BY 1)
SELECT
order_id,
1.0*shipped/booked AS shipped_perc,
1.0*delivered/booked AS delivered_perc
FROM t1;
```
```

Sample Output:

```order_id|shipped_perc|delivered_perc|
--------+------------+--------------+
10001|     1.00000|       0.00000|
10002|     0.00000|       1.00000|
10003|     0.00000|       1.00000|
10004|     1.00000|       0.00000|

```

SQL Code Editor:

﻿