式でCOUNT（）を複数回使用してOVERを実装するにはどうすればよいですか？

LeetCodeの問題を解決するために書いているクエリについて質問があります。ここに問題があります：

広告

+---------------+---------+
| Column Name   | Type    |
+---------------+---------+
| ad_id         | int     |
| user_id       | int     |
| action        | enum    |
+---------------+---------+

（ad_id、user_id）は、このテーブルの主キーです。

このテーブルの各行には、広告のID、ユーザーのID、およびこの広告に関してこのユーザーが実行したアクションが含まれています。アクション列は、（「クリック」、「表示」、「無視」）の列挙型です。

会社が広告を実行していて、各広告のパフォーマンスを計算したいと考えています。

広告のパフォーマンスは、クリックスルー率（CTR）を使用して測定されます。

CTR = {0広告クリックがない場合、広告クリック/（広告クリック+広告ビュー）それ以外の場合

各広告のクリック率を見つけるためのSQLクエリを記述します。

クリック率を小数点以下2桁に丸めます。同点の場合は、結果テーブルをctrで降順で、ad_idで昇順で並べ替えます。

クエリ結果の形式は次の例です。

広告テーブル：

+-------+---------+---------+
| ad_id | user_id | action  |
+-------+---------+---------+
| 1     | 1       | Clicked |
| 2     | 2       | Clicked |
| 3     | 3       | Viewed  |
| 5     | 5       | Ignored |
| 1     | 7       | Ignored |
| 2     | 7       | Viewed  |
| 3     | 5       | Clicked |
| 1     | 4       | Viewed  |
| 2     | 11      | Viewed  |
| 1     | 2       | Clicked |
+-------+---------+---------+

これがサンプルデータと私の試みた解決策のフィドルです。以下に再現された解決策の試み：

SELECT DISTINCT t.ad_id, ROUND(
    IF(
        COUNT(c.ad_id) OVER (PARTITION BY t.ad_id) = 0, 
        0,
      COUNT(c.ad_id) OVER (PARTITION BY t.ad_id) * 100 / ( COUNT(c.ad_id) OVER (PARTITION BY t.ad_id) + COUNT(v.ad_id) OVER (PARTITION BY t.ad_id) )
    ), 2) as ctr
FROM Ads as t
LEFT JOIN Ads as c ON c.ad_id=t.ad_id AND c.user_id=t.user_id AND c.action='Clicked'
LEFT JOIN Ads as v ON v.ad_id=t.ad_id AND v.user_id=t.user_id AND v.action='Viewed'
GROUP BY t.ad_id, c.ad_id, v.ad_id
ORDER BY ctr DESC, t.ad_id

このクエリの結果：

ad_id   ctr
1   50.00
2   50.00
3   50.00
5   0.00

正しい結果は次のようになります。

ad_id ctr
1, 66.67
3, 50.00
2, 33.33
5, 0.00

サンプルデータを見ると、COUNT（）は実際にはt.ad_idによるパーティション分割ではないと思います。50％のCT​​Rの結果は、計算ですべての「クリック」インスタンスとすべての「表示」インスタンスをカウントするCTR計算によって説明できます。（一方、CTR計算内のOVERステートメント（条件ではなく計算だけ）を削除しても、私の仮説が示唆するように、上記の結果は生成されません。したがって、これについてはよくわかりません。）

OVERの使い方に問題はありますか？私の論理はここに欠陥がありますか？

また、ボーナスの質問があります。JOINはサブクエリを使用するよりも高速であると想定しているため、ここではJOINを使用することを選択しています。これは公正な仮定ですか？私はデータアナリスト1のインタビューのために勉強しています-私がJOINとサブクエリを使用してもインタビュアーは気にかけると思いますか？

編集：forpasの説明のおかげで、私はオリジナルよりもはるかに簡単な解決策を思いつくことができました。以下の彼の回答にあるforpasの解決策は、テーブル内のNULLを明示的に処理するため、依然として望ましいと思います。

SELECT ad_id, ROUND(IF(
    SUM(action='Clicked') = 0,
    0,
    SUM(action='Clicked') * 100 / ( SUM(action='Clicked') + SUM(action='Viewed'))
), 2) as ctr
FROM Ads
GROUP BY ad_id
ORDER BY ctr DESC, ad_id