データ分析部SQL課題

[junishitsuka]

このSQL課題はGunosyのデータ分析部配属になった社員、アルバイトに手始めにやってもらうSQL課題の、データをマスキング、簡易化したものです。

課題は3問あります。

1. 記事ごとにCTRを計算してCTRが高い順に抽出せよ

2016-08-01 のインプとクリックを対象とする
ただしインプ・クリックはともに1記事に対して、1ユーザー1回までとして計算せよ（ログは1記事に対して1ユーザーで複数存在する場合がある）

clicks テーブル	説明	サンプル
user_id	クリックしたユーザーのID	223
article_id	クリックした記事のID	335
created_at	クリックした時間	'2016-08-01 14:32:57'

impressions テーブル	説明	サンプル
user_id	インプしたユーザーのID	223
article_id	インプした記事のID	335
created_at	インプした時間	'2016-08-01 14:32:57'

2. 特定日に登録したユーザーの7日後継続率を算出せよ

2016-08-01 に登録したユーザーを対象とする
7日後継続率 = 登録日から7日後にログインしたユーザー数 / 登録ユーザー数

users テーブル	説明	サンプル
user_id	ユーザーのID	223
os	ユーザーのOS	ios or android
created_at	ユーザーがアプリをインストールした時間	'2016-08-01 14:32:57'

logins テーブル	説明	サンプル
user_id	インプしたユーザーのID	223
created_at	ユーザーがログインした時間	'2016-08-01 14:32:57'

3. 対象日にログインしたユーザーの平均記事クリック数を計算せよ

2016-08-01 を対象日として、1記事に対してユーザーが複数回クリックしても1回とカウントする
ログインしても1記事もクリックしないユーザーがいることに注意

テーブルは前回までに使用したテーブルを使用せよ

[junishitsuka]

値の算出方法は多々あるので、解答は参考までに。

解答1.

SELECT
    clicks.article_id,
    clicks.click_uu,
    imps.imp_uu,
    1.0 * clicks.click_uu / imps.imp_uu as ctr
FROM (
  SELECT
      article_id,
      COUNT(DISTINCT user_id) as click_uu
  FROM
      clicks
  WHERE
      created_at >= '2016-08-01 00:00:00'
      AND created_at < '2016-08-02 00:00:00'
  GROUP BY
      article_id
) AS clicks
JOIN (
    SELECT
        article_id,
        COUNT(DISTINCT user_id) as imp_uu
    FROM
        impressions
    WHERE
      created_at >= '2016-08-01 00:00:00'
      AND created_at < '2016-08-02 00:00:00'
    GROUP BY
        article_id
) AS imps
ON clicks.article_id = imps.article_id
ORDER BY ctr DESC;

解答2.

SELECT
    COUNT(DISTINCT u.user_id) AS register_number,
    COUNT(DISTINCT l.user_id) AS active_number,
    1.0 * COUNT(DISTINCT l.user_id) / COUNT(DISTINCT u.user_id) AS retention_rate
FROM
    users AS u
LEFT JOIN (
    SELECT
        DISTINCT user_id
    FROM
        logins
    WHERE
        created_at >= '2016-08-08 00:00:00'
        AND created_at < '2016-08-09 00:00:00'
) AS l
ON
    u.user_id = l.user_id
WHERE
    u.created_at >= '2016-08-01 00:00:00'
    AND u.created_at < '2016-08-02 00:00:00'

解答3.

SELECT
    daily_logins.user_number,
    daily_clicks.click_number,
    1.0 * daily_clicks.click_number / daily_logins.user_number AS average_click_number
FROM (
    SELECT
        COUNT(DISTINCT user_id) AS user_number
    FROM
        logins
    WHERE
        created_at >= '2016-08-01 00:00:00'
        AND created_at < '2016-08-02 00:00:00'
) AS daily_logins
JOIN (
    SELECT
        SUM(click_number) AS click_number
    FROM (
        SELECT
            user_id,
            COUNT(DISTINCT article_id) AS click_number
        FROM
            clicks
        WHERE
            created_at >= '2016-08-01 00:00:00'
            AND created_at < '2016-08-02 00:00:00'
        GROUP BY
            user_id
    )
) AS daily_clicks
ON 1 = 1