SQL練習　

Topic：翌日の成功リテンション率を計算する

多くの人がA.comにログインしていますが、A.comの新規ログインユーザーの翌日の成功リテンション率を数えてみてください。

登録テーブル login は以下のように:

+----+---------+-----------+------------+
| id | user_id | client_id | date       |
+----+---------+-----------+------------+
|  1 |       2 |         1 | 2020-10-12 |
|  2 |       3 |         2 | 2020-10-12 |
|  3 |       1 |         2 | 2020-10-12 |
|  4 |       2 |         2 | 2020-10-13 |
|  5 |       4 |         1 | 2020-10-13 |
|  6 |       1 |         2 | 2020-10-13 |
|  7 |       1 |         2 | 2020-10-14 |
+----+---------+-----------+------------+

意味を説明する

1行目は、user_id 2 のユーザーが 2020 年 10 月 12 日にクライアント ID 1 のデバイスを使用して、 Aサイド にログインした。
4行目は、user_id 2 のユーザーが 2020 年 10 月 13 日にクライアント ID 2 のデバイスを使用して、 Aサイド にログインした。

✓質問

✓思考整理

解答の流れは下記の感じですね。

• 翌日の新規ログイン ユーザーの成功維持率をカウントするには、まず式をリストします
• 各ユーザーが最初にログインした日を見つける
• 初日にログインし、翌日もログインした新しいユーザーと、翌日の日付を見つける

順番にやて行きます。

‖翌日の新規ログイン ユーザーの成功維持率をカウントするには、まず式をリストします

(初日にログインし、2 日目にもログインした新規ユーザー) / (合計ユーザー) は、新規ログイン ユーザーの翌日の成功維持率です。

総ユーザー数は簡単に計算できます。下のように

select count(distinct user_id) from login;

‖各ユーザーが最初にログインした日を見つける

各ユーザーが最初にログインした日も複雑ではない、前回の全員の最終ログイン日（二）の練習に似たような計算は出ていたです。ただ、前回のはmax()、今回はmin()です。

select user_id,min(date) from login group by user_id;

‖初日にログインし、翌日もログインした新しいユーザーと、翌日の日付を見つける

ここは、DATE_ADD()　関数を使えば簡単に出来ます。

select user_id,DATE_ADD(min(date),INTERVAL 1 DAY) from login group by user_id;

これで、答えを導き出すために必要な 3 つのパズルのピースがすべて完成しました。

✓解答（mysql）

select 
round(count(distinct user_id)*1.0/(select count(distinct user_id) from login) ,3) as result
from login
where (user_id,date)
in (select user_id,DATE_ADD(min(date),INTERVAL 1 DAY) from login group by user_id);

結果は以下の通り

+--------+
| result |
+--------+
|  0.500 |
+--------+

✓最後に、DDLとDMLは以下に添付しますので、好きな方はご利用ください。

drop table if exists login;
CREATE TABLE `login` (
`id` int(4) NOT NULL,
`user_id` int(4) NOT NULL,
`client_id` int(4) NOT NULL,
`date` date NOT NULL,
PRIMARY KEY (`id`));

INSERT INTO login VALUES
(1,2,1,'2020-10-12'),
(2,3,2,'2020-10-12'),
(3,1,2,'2020-10-12'),
(4,2,2,'2020-10-13'),
(5,4,1,'2020-10-13'),
(6,1,2,'2020-10-13'),
(7,1,2,'2020-10-14');

SQL　全員の最終ログイン日（二）　全員の最終ログイン日（一）