Swiggy Business Analyst Interview- SQL Query

Swiggy SQL Interview Question : A sql question very similar to this was asked to me in one of the rounds of interviews at Swiggy.
#data analyst #data science #sql
Table Creation Code(DDL):
/*CREATE TABLE Orders ( Order_id INT, is_defective BOOLEAN, order_timestamp timestamp, is_completed BOOLEAN );
INSERT INTO Orders (Order_id, is_defective, order_timestamp, is_completed) VALUES
(1, TRUE, '2024-07-22 08:00:00', TRUE),
(2, TRUE, '2024-07-22 09:00:00', TRUE),
(3, TRUE, '2024-07-22 10:00:00', TRUE),
(4, TRUE, '2024-07-23 11:00:00', TRUE),
(5, FALSE, '2024-07-24 12:00:00', TRUE),
(6, TRUE, '2024-07-25 13:00:00', TRUE),
(7, TRUE, '2024-07-26 14:00:00', FALSE),
(8, TRUE, '2024-07-27 15:00:00', TRUE),
(9, FALSE, '2024-07-28 16:00:00', TRUE),
(10, TRUE, '2024-07-29 17:00:00', TRUE),
(11, TRUE, '2024-07-30 18:00:00', TRUE),
(12, TRUE, '2024-07-31 19:00:00', FALSE),
(13, FALSE, '2024-08-01 08:00:00', TRUE),
(14, TRUE, '2024-08-02 09:00:00', TRUE),
(15, FALSE, '2024-08-03 10:00:00', TRUE),
(16, TRUE, '2024-08-04 11:00:00', TRUE),
(17, FALSE, '2024-08-05 12:00:00', FALSE),
(18, TRUE, '2024-08-06 13:00:00', TRUE),
(19, FALSE, '2024-08-07 14:00:00', TRUE),
(20, TRUE, '2024-08-08 15:00:00', FALSE);
*/

/*
Defect Rate = Total Number of Defective Orders/ Requested Orders
Effective Rate = as Completed Orders/Requested Orders ratio
Order_id|is_defective|order_timestamp|is_completed
-- output
week dr er

*/
SQL Interview Question. (DML):
select date_trunc('week',order_timestamp) as week,
round(count(distinct case when is_defective = 't' then order_id else null end)*1.0/count(distinct order_id),2) as dr,
round(count(distinct case when is_completed = 't' then order_id else null end)*1.0/count(distinct order_id),2) as er
from orders
group by 1