Python

Рассчитать коэффициент отмены поездки с помощью SQL

Расчет коэффициента отмены поездки является популярным SQL вопрос в Наука о данных интервью. Он включает в себя анализ частоты отмены запросов на поездки, особенно от пользователей, которые не заблокированы. Итак, если вы хотите узнать, как рассчитать коэффициент отмены поездки, эта статья для вас. В этой статье я расскажу вам, как рассчитать коэффициент отмены поездки с помощью SQL.

Расчет коэффициента отмены поездки: постановка задачи

Даны две таблицы «Поездки» и «Пользователи», содержащие информацию о поездках и пользователях соответственно. Рассчитайте коэффициент отмены для запросов на совместное использование автомобилей с участием пользователей, которые не заблокированы. Заявка на поездку считается отмененной, если она отменена клиентом или водителем. Коэффициент отмены рассчитывается путем деления количества отмененных запросов (с незаблокированными пользователями) на общее количество запросов (с незабаненными пользователями) каждый день.

Входные таблицы:

Trips:
+------+-----------+-----------+---------+---------------------+------------+
| id   | client_id | driver_id | city_id | status              | request_at |
+------+-----------+-----------+---------+---------------------+------------+
|    1 |         1 |        10 |       1 | completed           | 2023-07-12 |
|    2 |         2 |        11 |       1 | cancelled_by_driver | 2023-07-12 |
|    3 |         3 |        12 |       6 | completed           | 2023-07-12 |
|    4 |         4 |        13 |       6 | cancelled_by_client | 2023-07-12 |
|    5 |         1 |        10 |       1 | completed           | 2023-07-13 |
|    6 |         2 |        11 |       6 | completed           | 2023-07-13 |
|    7 |         3 |        12 |       6 | completed           | 2023-07-13 |
|    8 |         2 |        12 |      12 | completed           | 2023-07-14 |
|    9 |         3 |        10 |      12 | completed           | 2023-07-14 |
|   10 |         4 |        13 |      12 | cancelled_by_driver | 2023-07-14 |
+------+-----------+-----------+---------+---------------------+------------+

Users:
+----------+--------+--------+
| users_id | banned | role   |
+----------+--------+--------+
|        1 | No     | client |
|        2 | Yes    | client |
|        3 | No     | client |
|        4 | No     | client |
|       10 | No     | driver |
|       11 | No     | driver |
|       12 | No     | driver |
|       13 | No     | driver |
+----------+--------+--------+

Рассчитать коэффициент отмены поездки с помощью SQL

Таблица «Поездки» содержит такие сведения, как идентификатор поездки, идентификатор водителя, идентификатор водителя, идентификатор города, статус и дата запроса. Таблица «Пользователи» содержит информацию о пользователе, такую ​​как идентификатор пользователя, статус бана и роль. Чтобы решить эту проблему, мы можем объединить таблицы «Поездки» и «Пользователи» на основе идентификатора водителя и водителя, гарантируя, что и клиент, и водитель не будут забанены. Применяя этот фильтр, мы можем сосредоточиться именно на запросах на поездки с участием незабаненных пользователей.

Далее нам нужно сгруппировать данные по дате запроса и посчитать количество отмененных запросов и общее количество запросов за каждый день.

Во-первых, давайте создадим входные таблицы для нашей базы данных SQL (я использую MySql):

CREATE TABLE Trips (
    id INT,
    client_id INT,
    driver_id INT,
    city_id INT,
    status VARCHAR(20),
    request_at DATE
);

INSERT INTO Trips (id, client_id, driver_id, city_id, status, request_at) VALUES
    (1, 1, 10, 1, 'completed', '2023-07-12'),
    (2, 2, 11, 1, 'cancelled_by_driver', '2023-07-12'),
    (3, 3, 12, 6, 'completed', '2023-07-12'),
    (4, 4, 13, 6, 'cancelled_by_client', '2023-07-12'),
    (5, 1, 10, 1, 'completed', '2023-07-13'),
    (6, 2, 11, 6, 'completed', '2023-07-13'),
    (7, 3, 12, 6, 'completed', '2023-07-13'),
    (8, 2, 12, 12, 'completed', '2023-07-14'),
    (9, 3, 10, 12, 'completed', '2023-07-14'),
    (10, 4, 13, 12, 'cancelled_by_driver', '2023-07-14');

CREATE TABLE Users (
    users_id INT,
    banned VARCHAR(3),
    role VARCHAR(10)
);

INSERT INTO Users (users_id, banned, role) VALUES
    (1, 'No', 'client'),
    (2, 'Yes', 'client'),
    (3, 'No', 'client'),
    (4, 'No', 'client'),
    (10, 'No', 'driver'),
    (11, 'No', 'driver'),
    (12, 'No', 'driver'),
    (13, 'No', 'driver');

Теперь ниже показано, как мы можем рассчитать коэффициент отмены поездки с помощью SQL:

WITH UnbannedTrips AS (
    SELECT
        t.request_at AS Day,
        COUNT(CASE WHEN t.status LIKE 'cancelled%' THEN 1 END) AS canceled_requests,
        COUNT(*) AS total_requests
    FROM
        Trips t
        JOIN Users u1 ON t.client_id = u1.users_id
        JOIN Users u2 ON t.driver_id = u2.users_id
    WHERE
        u1.banned = 'No' AND u2.banned = 'No'
    GROUP BY
        t.request_at
)
SELECT
    Day,
    ROUND(canceled_requests / NULLIF(total_requests, 0), 2) AS `Cancellation Rate`
FROM
    UnbannedTrips
ORDER BY
    Day;
+------------+-------------------+
| Day        | Cancellation Rate |
+------------+-------------------+
| 2023-07-12 |              0.33 |
| 2023-07-13 |              0.00 |
| 2023-07-14 |              0.50 |
+------------+-------------------+
3 rows in set (0.01 sec)

В приведенном выше решении используется Common Table Expression (CTE) с именем UnbannedTrips для расчета коэффициента отмены запросов на поездки с участием незабаненных пользователей. CTE выполняет соединение между таблицами Trips и Users на основе идентификаторов клиента и водителя, фильтруя незабаненных пользователей. Он объединяет количество отмененных запросов и общее количество запросов за день. Затем основной запрос выбирает день и вычисляет коэффициент отмены путем деления отмененных запросов на общее количество запросов, округленное до двух знаков после запятой. Затем результат упорядочивается по дням.

Краткое содержание

Таким образом, наше решение для расчета коэффициента отмены поездок использует Common Table Expression (CTE) для фильтрации поездок с участием незабаненных пользователей. Объединив таблицы «Поездки» и «Пользователи» на основе идентификаторов клиентов и водителей, он подсчитывает количество отмененных и общее количество запросов за каждый день. Затем основной запрос вычисляет коэффициент отмены путем деления отмененных запросов на общее количество запросов, округляя результат до двух знаков после запятой. Надеюсь, вам понравилась эта статья о том, как рассчитать коэффициент отмены поездок с помощью SQL. Не стесняйтесь задавать ценные вопросы в разделе комментариев ниже.


Ссылка на источник

Похожие статьи

Кнопка «Наверх»