https://leetcode.com/problems/trips-and-users


Success

___


yingshaoxo: Never give up, time will prove everything!

___


```python
# 2023/01/15 07:27

SELECT 
    new_new_table.day AS Day,
    #FORMAT(new_new_table.cancelled_counting/new_new_table.all_rows_counting, 2) 'Cancellation Rate'
    ROUND(new_new_table.cancelled_counting/new_new_table.all_rows_counting, 2) 'Cancellation Rate'
    FROM
    (
        SELECT new_table.day, 
            COUNT(*) AS all_rows_counting,
            #COUNT(IF(new_table.order_status = 'cancelled_by_driver', 1, NULL)) AS cancelled_counting
            COUNT(CASE WHEN new_table.order_status LIKE 'cancelled_%' THEN 1 END) AS cancelled_counting
        FROM  
        (
            SELECT 
                order_table.request_at AS day,
                order_table.status AS order_status
            FROM Trips order_table
            WHERE (
                order_table.id NOT IN
                    (
                        SELECT DISTINCT(order_table.id) AS drop_id
                        FROM Users user_table
                        INNER JOIN Trips order_table
                        ON (user_table.users_id = order_table.client_id) OR (user_table.users_id = order_table.driver_id)
                        WHERE user_table.banned = 'Yes'
                    )
                )
                AND
                (
                    order_table.request_at REGEXP '2013-10-0[123]'
                )
        ) new_table
        GROUP BY new_table.day
    ) new_new_table
;

# 2023/01/15 11:09, debug until 13:05, then realize INNER JOIN can't do everything
```