SQL文（クエリ）

WITH sales AS (
  SELECT
    product_id
    , SUM(quantity) AS sum_qty
    , SUM(revenue) AS sum_rev
  FROM
    sample.sales
  GROUP BY
    product_id
)
, web AS (
  SELECT
    CAST(REGEXP_EXTRACT(page, r"^/prod/prod_id_(\d\d?)") AS INT64) AS product_id
    , COUNT(*) AS pageview
  FROM
    sample.web_log
  GROUP BY
    product_id
  HAVING product_id IS NOT NULL
)
SELECT
  product_id
  , sum_qty
  , sum_rev
  , pageview
FROM
  sales
  JOIN web
  USING
    (product_id)
ORDER BY
  pageview DESC
LIMIT 3

結果テーブル

サポートページに戻る