leetcode_sql_solutions/1322. Ads Performance.sql at main · iamrafiul/leetcode_sql_solutions · GitHub

1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
28
29
30
31
32
33
34
35
36
37
38
39
# With sub-query, easier to read
SELECT
    cte.ad_id,
    ROUND(
        IFNULL(
            (cte.clicked / (cte.clicked + cte.viewed)) * 100
        , 0)
    , 2) AS ctr
FROM (
    SELECT
        ad_id,
        SUM(CASE
            WHEN action = 'Viewed' THEN 1 ELSE 0
        END) AS viewed,
        SUM(CASE
            WHEN action = 'Clicked' THEN 1 ELSE 0
        END) AS clicked
    FROM ads
    GROUP BY ad_id
) cte
ORDER BY ctr DESC, ad_id ASC;


# Without sub-query, concise
SELECT
    ad_id,
    ROUND(
        IFNULL(
            SUM(CASE
                WHEN action = 'Clicked' THEN 1 ELSE 0
            END) /
            (
                SUM(CASE WHEN action = 'Viewed' THEN 1 ELSE 0 END) + SUM(CASE WHEN action = 'Clicked' THEN 1 ELSE 0 END)
            ) * 100
        , 0)
    , 2) AS ctr
FROM ads
GROUP BY ad_id
ORDER BY ctr DESC, ad_id ASC;