SQL Challenges-1: Return the shipping and delivery rates

Last update on December 20 2024 11:39:51 (UTC/GMT +8 hours)

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:

Data:

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.