問題要件

注文情報テーブル(order_info)から、初回注文日の翌日に再注文したユーザーの割合を計算します。結果は小数点以下1桁でパーセンテージ表示します。

期待する出力

使用するテーブル

注文情報テーブル：order_info

解決方法

各ユーザーの初回注文日を特定し、翌日に注文があったかどうかを判定します。以下に5つの解法を示します。

解法1: 初回注文日と全注文日の比較

SELECT CONCAT(
  ROUND(
    COUNT(DISTINCT t2.user_id) / COUNT(DISTINCT t1.user_id), 
    1
  ) * 100, 
  '%'
) AS percentage
FROM (
  SELECT 
    user_id,
    MIN(create_date) AS first_date
  FROM order_info
  GROUP BY user_id
) t1
LEFT JOIN (
  SELECT 
    user_id,
    create_date
  FROM order_info
  GROUP BY user_id, create_date
) t2
ON t1.user_id = t2.user_id 
AND DATEDIFF(t2.create_date, t1.first_date) = 1

解法2: ウィンドウ関数を使用した初回注文日の特定

SELECT CONCAT(
  ROUND(
    COUNT(DISTINCT IF(DATEDIFF(create_date, first_date) = 1, user_id, NULL)) / 
    COUNT(DISTINCT user_id),
    1
  ) * 100,
  '%'
) AS percentage
FROM (
  SELECT 
    user_id,
    create_date,
    MIN(create_date) OVER (PARTITION BY user_id ORDER BY create_date) AS first_date
  FROM (
    SELECT 
      user_id,
      create_date
    FROM order_info
    GROUP BY user_id, create_date
  ) t1
) t2

解法3: LEAD関数で次の注文日を取得

SELECT CONCAT(
  ROUND(
    SUM(IF(DATEDIFF(next_date, create_date) = 1, 1, 0)) / 
    COUNT(DISTINCT user_id),
    1
  ) * 100,
  '%'
) AS percentage
FROM (
  SELECT 
    user_id,
    create_date,
    ROW_NUMBER() OVER (PARTITION BY user_id ORDER BY create_date) AS rn,
    LEAD(create_date, 1, '9999-12-31') OVER (PARTITION BY user_id ORDER BY create_date) AS next_date
  FROM (
    SELECT 
      user_id,
      create_date
    FROM order_info
    GROUP BY user_id, create_date
  ) t1
) t2
WHERE rn = 1

解法4: 連続注文日の特定

SELECT CONCAT(
  ROUND(
    COUNT(DISTINCT t5.user_id) / COUNT(DISTINCT t1.user_id),
    1
  ) * 100,
  '%'
) AS percentage
FROM (
  SELECT user_id
  FROM order_info
  GROUP BY user_id
) t1
LEFT JOIN (
  SELECT user_id
  FROM (
    SELECT 
      user_id,
      DATE_SUB(create_date, rn - 1) AS flag,
      first_date
    FROM (
      SELECT 
        user_id,
        create_date,
        ROW_NUMBER() OVER (PARTITION BY user_id ORDER BY create_date) AS rn,
        MIN(create_date) OVER (PARTITION BY user_id) AS first_date
      FROM (
        SELECT 
          user_id,
          create_date
        FROM order_info
        GROUP BY user_id, create_date
      ) t2
    ) t3
  ) t4
  WHERE flag = first_date
  GROUP BY user_id, flag
  HAVING COUNT(1) >= 2
) t5
ON t1.user_id = t5.user_id

解法5: 初回と2回目の注文日の比較

SELECT CONCAT(
  ROUND(
    SUM(IF(DATEDIFF(second_date, first_date) = 1, 1, 0)) / 
    COUNT(DISTINCT user_id),
    1
  ) * 100,
  '%'
) AS percentage
FROM (
  SELECT 
    user_id,
    MIN(create_date) AS first_date,
    MAX(create_date) AS second_date
  FROM (
    SELECT 
      user_id,
      create_date,
      ROW_NUMBER() OVER (PARTITION BY user_id ORDER BY create_date) AS rn
    FROM (
      SELECT 
        user_id,
        create_date
      FROM order_info
      GROUP BY user_id, create_date
    ) t1
  ) t2
  WHERE rn <= 2
  GROUP BY user_id
) t3