如何结合GROUP BY与聚合列值？SQL新手的同期群分析需求咨询

Hey there! It sounds like you’re definitely diving into cohort analysis—specifically, you want to count how many accounts had their first payment in each month. Let’s walk through how to build this query in BigQuery, and also clarify how GROUP BY works with aggregated columns along the way.

Step 1: Get Each Account’s First Payment Month #

First, we need to isolate the earliest month each account made a payment. This ensures we’re only counting each account once, tied to their "cohort" month (the month they first joined/paid). We can use a CTE (Common Table Expression) to do this cleanly:

WITH account_cohorts AS (
  SELECT
    account_id,
    -- Truncate the first payment date to the first day of its month
    DATE_TRUNC(MIN(payment_date), MONTH) AS first_payment_month
  FROM
    `your-project.your-dataset.your-payment-table` -- Replace with your actual table path
  GROUP BY
    account_id
)

Here, MIN(payment_date) grabs the earliest payment date for each account, and DATE_TRUNC(..., MONTH) converts that date to the start of its month (e.g., 2024-03-15 becomes 2024-03-01), so all accounts from the same month are grouped together.

Step 2: Count Accounts per Cohort Month #

Now that we have each account’s cohort month, we can group by that month and count the number of accounts in each group:

SELECT
  first_payment_month AS cohort_month,
  COUNT(account_id) AS total_accounts
FROM
  account_cohorts
GROUP BY
  cohort_month
ORDER BY
  cohort_month;

Understanding GROUP BY with Aggregated Columns #

Let’s break down how this works:

• When you use GROUP BY cohort_month, you’re telling BigQuery to split the data into groups where every row in a group has the same cohort_month value.
• The COUNT(account_id) is an aggregate function that runs per group—it counts how many unique account_id entries are in each cohort month group. Since we already grouped by account_id in the CTE, each row in account_cohorts is a unique account, so COUNT(account_id) works perfectly here (you could also use COUNT(DISTINCT account_id) for extra safety if there’s a chance of duplicate account rows).

Troubleshooting Common Errors #

If you were getting errors before, it’s likely because you tried to aggregate directly on the original table without first isolating each account’s first month. For example, this query would be incorrect:

-- This counts all accounts that paid in the month, NOT first-time accounts
SELECT
  DATE_TRUNC(payment_date, MONTH),
  COUNT(account_id)
FROM
  `your-table`
GROUP BY
  DATE_TRUNC(payment_date, MONTH);

This would count every account that made a payment in the month, even if they’d paid in earlier months. The CTE step fixes this by ensuring each account is only counted once, in their first payment month.

Bonus: Extending Cohort Analysis #

If you want to explore more complex logic (like you mentioned), you could join the cohort CTE back to the original payment table to track retention—for example, how many accounts from each cohort made payments in subsequent months. But that’s a great next step once you’ve got the basic cohort count working.

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