SQL文（クエリ）

WITH master AS (
  SELECT
    user_id
    , COUNT(DISTINCT order_id) AS orders
    , SUM(revenue) AS total_revenue
    , MAX(date_time) AS recent_order_date_time
  FROM
    sample.sales
  GROUP BY
    user_id
)
SELECT
  COUNT(DISTINCT user_id) AS users
FROM
  (
    SELECT
      user_id
      , recency + frequency + monetary AS total_score
    FROM
      (
        SELECT
          user_id
          , NTILE(5) OVER (ORDER BY recent_order_date_time DESC)
          AS recency
          , NTILE(5) OVER (ORDER BY orders DESC)
          AS frequency
          , NTILE(5) OVER (ORDER BY total_revenue DESC)
          AS monetary
        FROM
          master
      )
  )
GROUP BY
  total_score
HAVING total_score = 3

結果テーブル

※「23」「24」「25」「26」「27」のいずれも正解になり得ます。

サポートページに戻る