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:


Contribute your code and comments through Disqus.

Previous: Find the 2nd highest salary among employees.



Follow us on Facebook and Twitter for latest update.