240710_TIL

아무리 사소하더라도 배움이 없는 날은 없다.

SQL 코드카타

문제 링크

WITH result
     AS (SELECT view_date,
                viewer_id,
                Count(DISTINCT article_id) AS cnt
         FROM   views
         GROUP  BY 1,
                   2)
SELECT DISTINCT viewer_id AS "id"
FROM   result
WHERE  cnt > 1
ORDER  BY 1; 

문제 링크

WITH immediate
     AS (SELECT delivery_id
         FROM   delivery
         WHERE  order_date = customer_pref_delivery_date)
SELECT Round(100.0 * Count(delivery_id) / (SELECT Count(DISTINCT delivery_id)
                                           FROM   delivery), 2) AS
       "immediate_percentage"
FROM   immediate;

문제 링크

WITH install
     AS (SELECT player_id,
                Min(event_date) AS "install_dt"
         FROM   activity
         GROUP  BY 1),
     result
     AS (SELECT i.player_id,
                install_dt,
                Date_add(install_dt, interval 1 day) AS "next_day",
                a.games_played
         FROM   install i
                left join activity a
                       ON i.player_id = a.player_id
                          AND Date_add(install_dt, interval 1 day) =
                              a.event_date)
SELECT install_dt,
       Count(DISTINCT player_id)                        AS "installs",
       Round(Count(CASE
                     WHEN games_played IS NOT NULL THEN player_id
                   END) / Count(DISTINCT player_id), 2) AS "Day1_retention"
FROM   result
GROUP  BY 1;

문제 링크
이 문제는 나중에 꼭 다시 풀어보기.
생각지도 못한 방법이 있을 것 같다.

SELECT DISTINCT sub_id                            post_id,
                (SELECT Count(DISTINCT sub_id)
                 FROM   submissions s2
                 WHERE  s1.sub_id = s2.parent_id) number_of_comments
FROM   submissions s1
WHERE  parent_id IS NULL
ORDER  BY sub_id;