SQL文（クエリ）

SELECT
  user_id
  , MIN(diff_2_and_3) AS interval_between_2_and_3
FROM
  (
    SELECT
      *
      , DATETIME_DIFF(order_date3, order_date2, DAY) AS diff_2_and_3
    FROM
      (
        SELECT
          user_id
          , order_id
          , NTH_VALUE(order_date, 2)
          OVER (PARTITION BY user_id ORDER BY order_date)
          AS order_date2
          , NTH_VALUE(order_date, 3)
          OVER (PARTITION BY user_id ORDER BY order_date)
          AS order_date3
        FROM
          (
            SELECT
              user_id
              , order_id
              , MIN(date_time) AS order_date
            FROM
              sample.sales
            GROUP BY
              user_id
              , order_id
          )
      )
    WHERE
      order_date2 IS NOT NULL
    AND order_date3 IS NOT NULL
  )
GROUP BY
  user_id
ORDER BY
  interval_between_2_and_3
LIMIT 3

結果テーブル

サポートページに戻る