👍 Задачка с LeetCode. Расчет коэффициента подтверждения

🔍 Пришло время для следующей задачки с LeetCode.

У нас есть две таблицы:
Signups — информация о регистрации пользователей:
- user_id — уникальный идентификатор пользователя.
- time_stamp — время регистрации.

и Confirmations — информация о подтверждении действий пользователей:
- user_id — идентификатор пользователя.
- time_stamp — время подтверждения.
- action — результат действия (confirmed или timeout).

🌟Нужно: Посчитать коэффициент подтверждения для каждого пользователя — это доля успешных подтверждений (confirmed) от общего числа запросов. Если у пользователя не было запросов, коэффициент равен 0. Результат округляем до двух знаков после запятой.

Задачка уровня Middle. Вроде не сложная, но самое смешное, что я больше намучалась с округлением ROUND. В Юпитере же мы используем pandasql, а тут на сайте нужно Postgresql. Но обо всем поподробнее.

Сначала создадим тестовые данные:

import pandas as pd

signups_data = {
    'user_id': [3, 7, 2, 6],
    'time_stamp': [
        '2020-03-21 10:16:13',
        '2020-01-04 13:57:59',
        '2020-07-29 23:09:44',
        '2020-12-09 10:39:37'
    ]
}
signups = pd.DataFrame(signups_data)

confirmations_data = {
    'user_id': [3, 3, 7, 7, 7, 2, 2],
    'time_stamp': [
        '2021-01-06 03:30:46',
        '2021-07-14 14:00:00',
        '2021-06-12 11:57:29',
        '2021-06-13 12:58:28',
        '2021-06-14 13:59:27',
        '2021-01-22 00:00:00',
        '2021-02-28 23:59:59'
    ],
    'action': [
        'timeout',
        'timeout',
        'confirmed',
        'confirmed',
        'confirmed',
        'confirmed',
        'timeout'
    ]
}
confirmations = pd.DataFrame(confirmations_data)

#Финальный запрос для LeetCode выглядит так:
SELECT 
    tt.user_id, 
    ROUND(count_confirmed::NUMERIC / count_all, 2) AS confirmation_rate
FROM (
    SELECT 
        sd.user_id, 
        COUNT(*) FILTER (WHERE action = 'confirmed') AS count_confirmed,
        COUNT(*) AS count_all
    FROM signups sd
    LEFT JOIN confirmations cd 
    ON cd.user_id = sd.user_id
    GROUP BY 1
) tt;

А вот в Юпитере вместо ROUND(count_confirmed::NUMERIC / count_all, 2) AS confirmation_rate нужно сделать по другому: round(cast(count_confirmed AS FLOAT)/count_all,2) AS confirmation_rate.

✅ Объяснение скрипта
1️⃣. Вложенный запрос:
Считаем общее количество запросов (count_all) и подтвержденных действий (count_confirmed) для каждого пользователя.
Применяем FILTER для выделения только действий confirmed. Можно было и простым where обойтись, но это скучнее, вот и повыпендриваться захотелось. 🤪

2️⃣ Основной запрос:
Делим count_confirmed на count_all для каждого пользователя.
Используем многострадальный ROUND для округления результата до двух знаков после запятой. В случае юпитера через cast для приведения count_confirmed к типу FLOAT перед делением. В случае Leetcode - к типу FLOAT приводим через ::NUMERIC
Теперь деление будет выполняться в дробных числах, а не в целых.

А еще отвлекающий маневр - колонка time_stamp в таблице Signups. Она тут не нужна, но зачем-то есть. Не люблю лишние данные в задачках 😜

✨ Итог
Этот запрос поможет отработать ключевые SQL-концепции, такие как FILTER, LEFT JOIN и группировка, а также построить наглядный расчет метрик.

✅ Попробуйте сами — это отличная практика!