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で解いています。