BigQuery中按类别与年份统计新老用户的实现方法问询

Hey there! Let's work through this problem together. The core requirement here is to track new vs. existing users per category and year, where a user counts as new for a category only in the year they first purchased that specific category—even if they bought other categories in earlier years. Here's how to build this in BigQuery:

Step 1: Calculate First Purchase Year per User & Category #

First, we need to identify the earliest year each user made a purchase in every category. This gives us the "new user" year for that user-category pair.

WITH user_category_first_purchase AS (
  SELECT
    user_id,
    category,
    -- Extract the year from the earliest purchase date for the user-category
    EXTRACT(YEAR FROM MIN(date)) AS first_purchase_year
  FROM
    `your-project.dataset.Table_1` -- Replace with your actual table path
  GROUP BY
    user_id,
    category
)

Step 2: Classify & Aggregate New/Existing Users #

Next, we join this first-purchase data back to the original table to label each user's purchase as new or existing for the category, then aggregate by category and year.

SELECT
  t.category,
  EXTRACT(YEAR FROM t.date) AS year,
  -- Count distinct users who are new to the category this year
  COUNT(DISTINCT CASE 
    WHEN EXTRACT(YEAR FROM t.date) = ucfp.first_purchase_year THEN t.user_id 
  END) AS new_users,
  -- Count distinct users who already purchased this category in prior years
  COUNT(DISTINCT CASE 
    WHEN EXTRACT(YEAR FROM t.date) > ucfp.first_purchase_year THEN t.user_id 
  END) AS existing_users,
  -- Optional: Total unique users for the category-year
  COUNT(DISTINCT t.user_id) AS total_users
FROM
  `your-project.dataset.Table_1` t
JOIN
  user_category_first_purchase ucfp
ON
  t.user_id = ucfp.user_id
  AND t.category = ucfp.category
-- Filter to the 2015-2020 time range as specified
WHERE
  EXTRACT(YEAR FROM t.date) BETWEEN 2015 AND 2020
GROUP BY
  t.category,
  EXTRACT(YEAR FROM t.date)
ORDER BY
  t.category,
  year;

Key Notes: #

• Distinct Counts: We use COUNT(DISTINCT user_id) to avoid overcounting users who made multiple purchases in the same category and year.
• Category-Specific New Users: The CTE ensures we only look at the first purchase year for that category—so a user who bought category B in 2015 will still count as new for category A in 2016.
• Time Filter: The WHERE clause restricts results to your requested 2015-2020 window.

内容的提问来源于stack exchange，提问作者MKMK_79