Tasa de confirmaci贸n

confirmation-rate

La tasa de confirmaci贸n de un usuario representa el n煤mero de mensajes 'confirmados' dividido por el n煤mero total de mensajes de confirmaci贸n solicitados. La tasa de confirmaci贸n de un usuario que no solicit贸 ning煤n mensaje de confirmaci贸n es 0. Redondea la tasa de confirmaci贸n a dos decimales. Escribe una soluci贸n para encontrar la tasa de confirmaci贸n para cada usuario. Regresa el resultado en cualquier 贸rden.

#database

Tabla: Signups

+----------------+----------+
| Column Name    | Type     |
+----------------+----------+
| user_id        | int      |
| time_stamp     | datetime |
+----------------+----------+

- `user_id` es una columna con valores 煤nicos para esta tabla.
- Cada fila contiene informaci贸n del tiempo de inscripci贸n para el usuario con ID `user_id`.

Tabla: Confirmations

+----------------+----------+
| Column Name    | Type     |
+----------------+----------+
| user_id        | int      |
| time_stamp     | datetime |
| action         | ENUM     |
+----------------+----------+

- (`user_id`, `time_stamp`) la combinaci贸n de estas dos columbas es la llave primaria (la cual contiene valores 煤nicos) para esta tabla.
- `user_id` es una llave foranea (columna referente) hac铆a la tabla Signups.
- `action` es un `ENUM (categor铆a)` de tipo ('confirmed', 'timeout')
- Cada fila de esta tabla indica que el usuario con ID `user_id` ha solicitado un mensaje de confirmaci贸n en
  la fecha `time_stamp` y ese mensaje de confirmaci贸n fue confirmado ('confirmed') o expirado sin confirmarse
  ('timeout').

El formato del resultado se muestra en el siguiente ejemplo.

Ejemplo 1:

Entrada:

Tabla Signups:
+---------+---------------------+
| user_id | time_stamp          |
+---------+---------------------+
| 3       | 2020-03-21 10:16:13 |
| 7       | 2020-01-04 13:57:59 |
| 2       | 2020-07-29 23:09:44 |
| 6       | 2020-12-09 10:39:37 |
+---------+---------------------+

Tabla Confirmations:
+---------+---------------------+-----------+
| user_id | time_stamp          | action    |
+---------+---------------------+-----------+
| 3       | 2021-01-06 03:30:46 | timeout   |
| 3       | 2021-07-14 14:00:00 | timeout   |
| 7       | 2021-06-12 11:57:29 | confirmed |
| 7       | 2021-06-13 12:58:28 | confirmed |
| 7       | 2021-06-14 13:59:27 | confirmed |
| 2       | 2021-01-22 00:00:00 | confirmed |
| 2       | 2021-02-28 23:59:59 | timeout   |
+---------+---------------------+-----------+

Salida:

+---------+-------------------+
| user_id | confirmation_rate |
+---------+-------------------+
| 6       | 0.00              |
| 3       | 0.00              |
| 7       | 1.00              |
| 2       | 0.50              |
+---------+-------------------+

Explicaci贸n:
- Usuario 6 no solicit贸 ning煤n mensaje de confirmaci贸n. Su tasa de confirmaci贸n es 0.
- Usuario 3 hizo 2 solicitudes y ambas expiraron. Su tasa de confirmaci贸n es 0.
- Usuario 7 hizo 3 solicitudes y todas fueron confirmadas. Su tasa de confirmaci贸n es 1.
- Usuario 2 hizo 2 solicitudes, de las cuales una fue confirmada y la otra explir贸. Su tasa de confirmaci贸n es
  1 / 2 = 0.5.

Soluci贸n

WITH data AS (
    SELECT s.user_id,
    COUNT(*) AS total,
    SUM(ifnull(c.action='confirmed', 0)) AS confirmed_cnt
    FROM signups s LEFT JOIN confirmations c ON s.user_id = c.user_id
    GROUP BY s.user_id
) SELECT user_id, ROUND(confirmed_cnt / total, 2) AS confirmation_rate FROM data;

slackmart blog 漏 2024