Big Query

Cohort Retention

Liệt kê những bài viết kèm theo tác giả theo YearMonth

SELECT id, owner_user_id, owner_display_name, FORMAT_DATETIME('%Y%m', creation_date)  AS yearMonth
    FROM `bigquery-public-data.stackoverflow.stackoverflow_posts`
    WHERE owner_user_id IS NOT NULL OR owner_display_name IS NOT NULL 
  ORDER BY yearMonth
  LIMIT 10

Liệt kê danh sách những tác giả kèm theo ngày đăng bài đầu tiên

WITH CTE AS(
  SELECT id, owner_user_id, owner_display_name, FORMAT_DATETIME('%Y%m', creation_date)  AS yearMonth
    FROM `bigquery-public-data.stackoverflow.stackoverflow_posts`
    WHERE owner_user_id IS NOT NULL OR owner_display_name IS NOT NULL
)SELECT owner_user_id, owner_display_name, MIN(yearMonth) AS FirstPost  
  FROM CTE GROUP BY owner_user_id, owner_display_name

Kết 2 bảng danh sách những bài viết và danh sách những tác giả ngày đăng bài đầu tiên

WITH CTE AS(
  SELECT id, owner_user_id, owner_display_name, FORMAT_DATETIME('%Y%m', creation_date)  AS yearMonth
    FROM `bigquery-public-data.stackoverflow.stackoverflow_posts`
    WHERE owner_user_id IS NOT NULL OR owner_display_name IS NOT NULL
), UserFirstPost AS(
    SELECT owner_user_id, owner_display_name, MIN(yearMonth) AS FirstPost  
    FROM CTE GROUP BY owner_user_id, owner_display_name
)SELECT CTE.*, FirstPost 
  FROM CTE JOIN UserFirstPost ON CTE.owner_user_id = UserFirstPost.owner_user_id AND
    CTE.owner_display_name = UserFirstPost.owner_display_name

Đánh dấu ghi nhận nếu yearMonth trung với ngày đầu tiên đăng bài firstPost của chính tác giả thì đánh dầu là First ngược lại đánh dấu là Repeat

WITH CTE AS(
  SELECT id, owner_user_id, owner_display_name, FORMAT_DATETIME('%Y%m', creation_date)  AS yearMonth
    FROM `bigquery-public-data.stackoverflow.stackoverflow_posts`
    WHERE owner_user_id IS NOT NULL OR owner_display_name IS NOT NULL
), UserFirstPost AS(
    SELECT owner_user_id, owner_display_name, MIN(yearMonth) AS FirstPost  
    FROM CTE GROUP BY owner_user_id, owner_display_name
)SELECT CTE.*, FirstPost, IF(yearMonth = FirstPost, 1, 0) AS First, IF(yearMonth <> FirstPost, 1, 0) AS Repeat
  FROM CTE JOIN UserFirstPost ON CTE.owner_user_id = UserFirstPost.owner_user_id AND
    CTE.owner_display_name = UserFirstPost.owner_display_name

Tống hợp số lượng thành viên mới trong tháng và số lượng thành viên cũ đăng bài

WITH CTE AS(
  SELECT id, owner_user_id, owner_display_name, FORMAT_DATETIME('%Y%m', creation_date)  AS yearMonth
    FROM `bigquery-public-data.stackoverflow.stackoverflow_posts`
    WHERE owner_user_id IS NOT NULL OR owner_display_name IS NOT NULL
), UserFirstPost AS(
    SELECT owner_user_id, owner_display_name, MIN(yearMonth) AS FirstPost  
    FROM CTE GROUP BY owner_user_id, owner_display_name
), UserFirstRepeat AS(
  SELECT CTE.*, FirstPost, IF(yearMonth = FirstPost, 1, 0) AS First, IF(yearMonth <> FirstPost, 1, 0) AS Repeat
    FROM CTE JOIN UserFirstPost ON CTE.owner_user_id = UserFirstPost.owner_user_id AND
      CTE.owner_display_name = UserFirstPost.owner_display_name
)SELECT UserFirstRepeat.yearMonth, SUM(First) AS TotalNew, SUM(Repeat) AS TotalOld 
  FROM UserFirstRepeat group by UserFirstRepeat.yearMonth
  ORDER BY UserFirstRepeat.yearMonth