DROP TABLE IF EXISTS Trips;
DROP TABLE IF EXISTS Users;

-- Создание таблицы Users
CREATE TABLE Users
(
    id       INT AUTO_INCREMENT PRIMARY KEY,
    banned   VARCHAR(50),
    role     ENUM ('client', 'driver')
);


INSERT INTO Users(banned, role)
VALUES ('yes', 'client'),
       ('yes', 'client'),
       ('no', 'client'),
       ('no', 'client'),
       ('no', 'client'),
       ('no', 'driver'),
       ('no', 'driver'),
       ('no', 'driver'),
       ('no', 'driver'),
       ('yes', 'driver'),
       ('no', 'driver');

-- Создание таблицы Trips
CREATE TABLE Trips
(
    id         INT PRIMARY KEY AUTO_INCREMENT,
    client_id  INT,
    driver_id  INT,
    status     ENUM ('completed', 'cancelled_by_driver', 'cancelled_by_client'),
    request_at VARCHAR(50)
);

TRUNCATE Trips;
INSERT INTO Trips(client_id, driver_id, status, request_at)
VALUES (3, 6, 'completed', '2023-10-01'),
       (4, 8, 'completed', '2023-10-01'),
       (2, 9, 'completed', '2023-10-01'),
       (1, 8, 'cancelled_by_client', '2023-10-01'),
       (5, 11, 'completed', '2023-10-02'),
       (5, 11, 'cancelled_by_driver', '2023-10-02'),
       (2, 10, 'completed', '2023-10-02'),
       (2, 8, 'completed', '2023-10-02'),
       (3, 11, 'completed', '2023-10-02'),
       (3, 7, 'cancelled_by_driver', '2023-10-03'),
       (2, 6, 'cancelled_by_driver', '2023-10-03'),
       (4, 8, 'completed', '2023-10-03'),
       (2, 11, 'completed', '2023-10-03'),
       (3, 10, 'completed', '2023-10-04'),
       (1, 10, 'completed', '2023-10-05'),
       (4, 8, 'completed', '2023-10-05'),
       (2, 8, 'cancelled_by_driver', '2023-10-05'),
       (5, 9, 'completed', '2023-10-05'),
       (2, 6, 'completed', '2023-10-05'),
       (5, 8, 'completed', '2023-10-06'),
       (3, 9, 'completed', '2023-10-06'),
       (2, 10, 'completed', '2023-10-07'),
       (2, 6, 'completed', '2023-10-07'),
       (3, 6, 'completed', '2023-10-08'),
       (3, 7, 'completed', '2023-10-08'),
       (5, 8, 'completed', '2023-10-09'),
       (2, 8, 'completed', '2023-10-09'),
       (2, 11, 'completed', '2023-10-09'),
       (4, 10, 'completed', '2023-10-09'),
       (3, 11, 'completed', '2023-10-10');

/*
SELECT all_t.request_at AS day,
COUNT(can_t.status)/COUNT(com_t.status)
FROM Trips all_t
LEFT JOIN Trips com_t ON all_t.id = com_t.id AND com_t.status = 'completed'
LEFT JOIN Trips can_t ON all_t.id = can_t.id AND (can_t.status = 'cancelled_by_driver' OR can_t.status = 'cancelled_by_client')
LEFT JOIN Users uc ON all_t.client_id = uc.id
GROUP BY day
*/



SELECT *
FROM Trips all_t
LEFT JOIN Trips com_t ON all_t.id = com_t.id AND com_t.status = 'completed'
LEFT JOIN Trips can_t ON all_t.id = can_t.id AND (can_t.status = 'cancelled_by_driver' OR can_t.status = 'cancelled_by_client')
LEFT JOIN Users u ON all_t.client_id = u.id AND all_t.driver_id = u.id