SQL文（クエリ）

WITH first AS (
  SELECT
    cid
    , MIN(date_time) AS min_date_time
    , MIN(session_count) AS min_session_count
  FROM
    sample.web_log
  GROUP BY
    cid
  HAVING min_session_count = 1
)
, cvs AS (
  SELECT
    cid
    , MIN(date_time) AS min_cv_date_time
  FROM
    (
      SELECT
        cid
        , date_time
      FROM
        sample.web_log
      WHERE
        page = "/thank_you/"
    )
  GROUP BY
    cid
)
SELECT
  ROUND(AVG(cv_leadtime_day), 0) AS avg_cv_leadtime_day
FROM
  (
    SELECT
      cid
      , min_date_time
      , min_cv_date_time
      , DATETIME_DIFF(min_cv_date_time, min_date_time, DAY) AS cv_leadtime_day
    FROM
      first
      JOIN cvs
      USING
        (cid)
  )

結果テーブル

サポートページに戻る