1369. Get the Second Most Recent Activity （問題レベル: Hard）要課金
今日からついにLeetCode Curated SQL 70のHard問題に突入。
今日の問題はこれ。
ユーザー毎に二番目に新しい活動をリストアップするという問題。
ただし、1度しか活動がない場合には1番目の活動をリストする。というのが厄介。
悩みましたが自力で正答できました。提出したコードが下記。
SQL
SELECT a.username, a.activity, a.startDate, a.endDate
FROM (
SELECT username, activity, startDate, endDate, DENSE_RANK() OVER(PARTITION BY username ORDER BY enddate DESC) AS rank
From useractivity) a
WHERE a.rank=2
UNION ALL
SELECT b.username, b.activity, b.startDate, b.endDate
FROM useractivity b
WHERE b.username NOT IN (SELECT username FROM useractivity GROUP BY username HAVING COUNT(username) > 1)
もともとの課題テーブルには重複行があることもあるということで今回はDENSE_RANK()を使ったがROW_NUMBER()でもOKがだったようだ。
今日のポイントは下記の使い分けとする。
| クエリ | 説明 |
|---|---|
| ROW_NUMBER( ) | 行番号を１から振る。同順でも番号は変わる。 |
| RANK( ) | 並び順に順位を付けるが、同順がある場合には同順位になる。次の順位は重複分飛ぶ。例：1位、2位、2位、4位 |
| DENSE_RANK( ) | 並び順に順位を付けるが、同順がある場合には同順位になる。次の順位はそのまま続く。例：1位、2位、2位、3位 |
- 並び順は、このクエリの後に続く
OVER(ORDER BY 変数名)句で指定する。
UNIONを使った解法もあったので理解しやすい解答だと思いますが、短くて勉強になったのは下記。
SQL
SELECT a.username, a.activity, a.startDate, a.endDate
FROM (SELECT *, COUNT(activity) OVER(PARTITION BY username) AS dup,
ROW_NUMBER() OVER(PARTITION BY username ORDER BY startdate DESC) AS rnk FROM UserActivity) a
WHERE a.rnk=2 OR a.dup<2
WHERE a.rnk=2 OR a.dup<2という条件が自分では書けない気がします。
-
LeetCodeの問題は、
MS SQL Serverで解いています。