【2022年3月8日更新】初出時にSQL文（クエリ）と結果テーブルに誤りがありました。お詫びして訂正いたします。

SQL文（クエリ）

WITH master AS (
  SELECT user_id
    , date_time
    , IF(product_id = 2, date_time, NULL) AS prod_2_time
    , IF(product_id = 15, date_time, NULL) AS prod_15_time
  FROM sample.sales
  WHERE product_id IN (2,15)
)
 
SELECT DISTINCT user_id
FROM (
  SELECT * FROM
    (
      SELECT user_id
        , MIN(prod_2_time) OVER (PARTITION BY user_id) AS min_prod_2_time
        , MIN(prod_15_time) OVER (PARTITION BY user_id) AS min_prod_15_time
        , MAX(prod_2_time) OVER (PARTITION BY user_id) AS max_prod_2_time
        , MAX(prod_15_time) OVER (PARTITION BY user_id) AS max_prod_15_time
      FROM master
    )
  WHERE
    min_prod_2_time < min_prod_15_time AND date_trunc(min_prod_2_time, year) = date_trunc(min_prod_15_time, year)
  OR
    max_prod_2_time < max_prod_15_time AND date_trunc(max_prod_2_time, year) = date_trunc(max_prod_15_time, year)
)
ORDER BY 1

結果テーブル

サポートページに戻る