100日でSQLの達人になる@LeetCode! Day47 <WITHとCASE WHENとLeft JOINで解決>

1248 ワード
SQL leetcode join SQLServer 初心者 SQL テキストリンク

1097. Game Play Analysis V （問題レベル: Hard）要課金

今日の問題はこれ。
日付ごとに、その日がはじめてプレイしたユーザーが次の日もプレイする割合を算出するという問題。
Middle, Hardの問題では珍しい一つのテーブルからの問題。

With句とCASE WHENを使って自力で解決できた。

最終的に提出したコードは下記のとおり。

SQL
WITH cte AS
(SELECT DISTINCT player_id, 
 MIN(event_date) OVER(PARTITION BY player_id) AS date
 FROM activity)
 
SELECT m.date AS install_dt,
COUNT(m.player_id) AS installs,
ROUND(AVG(CASE WHEN m.games_played IS NULL THEN 0.0 ELSE 1.0 END),2) AS Day1_retention
FROM 
(SELECT c.player_id, c.date, a.games_played
FROM cte c
LEFT JOIN activity a
ON a.player_id=c.player_id AND DATEDIFF(day, c.date, a.event_date) = 1) m
GROUP BY m.date

Left JOINで初日の次の日のデータがある場合と無い場合で切り分けるという考え方を使った。

今日のポイントはWITHとCASE WHENとLeft JOINで解決としよう。

  • LeetCodeの問題は、MS SQL Serverで解いています。