Big Query Stackoverflow

1. Cho biết dữ liệu được lấy trong khoảng thời gian nào

SELECT DISTINCT EXTRACT(YEAR FROM creation_date) AS Year 
  FROM `bigquery-public-data.stackoverflow.stackoverflow_posts` 
  ORDER BY Year;

2. Cho biết số lượng bài theo từng post_type_id

SELECT post_type_id, COUNT(*) as total
  FROM `bigquery-public-data.stackoverflow.stackoverflow_posts`
    GROUP BY post_type_id
    ORDER BY total DESC;

3. Cho biết số lượng bài viết theo từng tên loại bài viết

SELECT post_type_id, type, COUNT(*) as total
  FROM `bigquery-public-data.stackoverflow.stackoverflow_posts` 
    JOIN `Shop.PostType` AS B ON post_type_id = B.id
    GROUP BY post_type_id, type
    ORDER BY total DESC;

Pareto Principle

4. Cho biết số lượng bài viết nặc danh

SELECT COUNT(*) FROM `bigquery-public-data.stackoverflow.stackoverflow_posts`
  WHERE owner_user_id IS NULL AND owner_display_name IS NULL;

5. Cho biết top những thành viên đăng nhiều bài viết nhất

SELECT owner_user_id, owner_display_name, COUNT(*) AS Total 
  FROM `bigquery-public-data.stackoverflow.stackoverflow_posts`
  WHERE owner_user_id IS NOT NULL OR owner_display_name IS NOT NULL
  GROUP BY owner_user_id, owner_display_name
  ORDER BY total DESC
  LIMIT 50;

6. Tính tổng lũy tiến theo số lượng bài viết được sắp xếp theo số lượng bài viết giảm dần

WITH CTE AS(
  SELECT owner_user_id, owner_display_name, COUNT(*) AS total 
    FROM `bigquery-public-data.stackoverflow.stackoverflow_posts`
    WHERE owner_user_id IS NOT NULL OR owner_display_name IS NOT NULL
    GROUP BY owner_user_id, owner_display_name
)
SELECT *, SUM(total) OVER(ORDER BY total DESC ROWS BETWEEN UNBOUNDED PRECEDING AND CURRENT ROW) AS Comulative
  FROM CTE 
  ORDER BY total DESC
  LIMIT 50;

7. Tính tổng lũy tiến theo tỉ lệ phần trăm theo số lượng bài viết được sấp xếp giảm dần

DECLARE totalPost INT64;
SET totalPost = (SELECT COUNT(*) AS totalPost FROM `bigquery-public-data.stackoverflow.stackoverflow_posts`
    WHERE owner_user_id IS NOT NULL OR owner_display_name IS NOT NULL);
WITH CTE AS(
  SELECT owner_user_id, owner_display_name, COUNT(*) AS total 
    FROM `bigquery-public-data.stackoverflow.stackoverflow_posts`
    WHERE owner_user_id IS NOT NULL OR owner_display_name IS NOT NULL
    GROUP BY owner_user_id, owner_display_name
)
SELECT *, SUM(total) OVER(ORDER BY total DESC ROWS BETWEEN UNBOUNDED PRECEDING AND CURRENT ROW) / totalPost AS ComulativePercent
  FROM CTE 
  ORDER BY total DESC
  LIMIT 50;

8. Cho biết top những thành viên chiếm số lượng bài viết 80%

DECLARE totalPost INT64;
SET totalPost = (SELECT COUNT(*) AS totalPost FROM `bigquery-public-data.stackoverflow.stackoverflow_posts`
    WHERE owner_user_id IS NOT NULL OR owner_display_name IS NOT NULL);
WITH CTE AS(
  SELECT owner_user_id, owner_display_name, COUNT(*) AS total 
    FROM `bigquery-public-data.stackoverflow.stackoverflow_posts`
    WHERE owner_user_id IS NOT NULL OR owner_display_name IS NOT NULL
    GROUP BY owner_user_id, owner_display_name
), CTE2 AS(
  SELECT *, SUM(total) OVER(ORDER BY total DESC ROWS BETWEEN UNBOUNDED PRECEDING AND CURRENT ROW) / totalPost AS ComulativePercent
    FROM CTE
)SELECT * FROM CTE2
  WHERE ComulativePercent <= 0.8
  ORDER BY total DESC;

9. Kiểm tra số lượng đăng bài nhiều nhất cho chiếm trong nhóm 20%

DECLARE totalPost INT64;
SET totalPost = (SELECT COUNT(*) AS totalPost FROM `bigquery-public-data.stackoverflow.stackoverflow_posts`
    WHERE owner_user_id IS NOT NULL OR owner_display_name IS NOT NULL);
WITH CTE AS(
  SELECT owner_user_id, owner_display_name, COUNT(*) AS total 
    FROM `bigquery-public-data.stackoverflow.stackoverflow_posts`
    WHERE owner_user_id IS NOT NULL OR owner_display_name IS NOT NULL
    GROUP BY owner_user_id, owner_display_name
), CTE2 AS(
  SELECT *, SUM(total) OVER(ORDER BY total DESC ROWS BETWEEN UNBOUNDED PRECEDING AND CURRENT ROW) / totalPost AS ComulativePercent
    FROM CTE
)SELECT COUNT(*) / totalPost FROM CTE2
  WHERE ComulativePercent <= 0.8

Tags and Post Relation

10. Tách từng từ trong tags

SELECT id, title ,SPLIT(tags, '|') AS Tag 
  FROM `bigquery-public-data.stackoverflow.stackoverflow_posts`
  WHERE tags IS NOT NULL AND tags <> '' LIMIT 100;

Tách từng từ trong tags thành 1 cột

 WITH CTE AS(
  SELECT SPLIT(tags, '|') AS Tag 
  FROM `bigquery-public-data.stackoverflow.stackoverflow_posts`
  WHERE tags IS NOT NULL AND tags <> ''
)SELECT Word FROM CTE, UNNEST(tag) AS Word LIMIT 100;

11. Cho biết top 10 từ khóa nào phổ biến nhất

 WITH CTE AS(
  SELECT SPLIT(tags, '|') AS Tag FROM `bigquery-public-data.stackoverflow.stackoverflow_posts`
    WHERE tags IS NOT NULL AND tags <> ''
)
SELECT Word, COUNT(Word) AS Total 
  FROM CTE, UNNEST(CTE.Tag) AS Word 
  GROUP BY Word 
  ORDER BY Total DESC
  LIMIT 10;

12. Cho biết top 10 từ khóa phổ biến nhất trong năm gần nhất

 WITH CTE AS(
  SELECT SPLIT(tags, '|') AS Tag FROM `bigquery-public-data.stackoverflow.stackoverflow_posts`
    WHERE tags IS NOT NULL AND tags <> '' AND EXTRACT(YEAR FROM creation_date) = 2016
)
SELECT Word, COUNT(Word) AS Total 
  FROM CTE, UNNEST(CTE.Tag) AS Word 
  GROUP BY Word 
  ORDER BY Total DESC
  LIMIT 10;

13. Cho biết top 5 các từ phổ biến qua các năm

 WITH CTE AS(
  SELECT EXTRACT(YEAR FROM creation_date) AS Year, SPLIT(tags, '|') AS Tag 
    FROM `bigquery-public-data.stackoverflow.stackoverflow_posts`
    WHERE tags IS NOT NULL AND tags <> ''
), CTE2 AS(
  SELECT Year, Word, COUNT(Word) AS Total 
  FROM CTE, UNNEST(CTE.Tag) AS Word 
  GROUP BY Year, Word
), CTE3 AS(
  SELECT ROW_NUMBER() OVER(PARTITION BY CTE2.YEAR ORDER BY Total DESC) AS SrNO, * FROM CTE2
)  
SELECT * FROM CTE3 WHERE SrNO <= 5 ORDER BY CTE3.Year ASC, Total DESC;

14. Rút trích thông tin trong từng bài viết với số từ được tách từ tags rồi lưu thành 1 bảng HashTags

CREATE OR REPLACE TABLE Shop.HashTags AS
  WITH CTE AS(
    SELECT id, EXTRACT(YEAR FROM creation_date) AS year, SPLIT(tags, '|') AS tag
    FROM `bigquery-public-data.stackoverflow.stackoverflow_posts`
    WHERE tags IS NOT NULL AND tags <> ''
  )SELECT id, year, word 
    FROM CTE, UNNEST(tag) AS word;

15. Rút trích thông tin từng bài viết và số lượng từ được tách từ tags lưu thành 1 bảng PostCountWords

CREATE OR REPLACE TABLE Shop.PostCountWords AS
  SELECT id, COUNT(*) AS total
    FROM Shop.HashTags 
    GROUP BY id;

16. Tìm những bài viết liên quan tính theo độ tương quan những bài viết có số lượng tags giống nhau nhiều nhất

DECLARE postId INT64 DEFAULT 274459;
SELECT A.id, B.id AS Relation, COUNT(*) AS Total 
  FROM `Shop.HashTags` AS A JOIN Shop.HashTags AS B
  ON A.id = postId AND B.id <> postId AND A.word = B.word
  GROUP BY A.id, b.id
  ORDER BY Total DESC
  LIMIT 10;

17. Tìm những bài viết liên quan được tính theo độ đo cosine

DECLARE postId INT64 DEFAULT 274459;
WITH CTE AS(
  SELECT A.id, B.id AS Relation, COUNT(*) AS Total 
  FROM `Shop.HashTags` AS A JOIN Shop.HashTags AS B
  ON A.id = postId AND B.id <> postId AND A.word = B.word
  GROUP BY A.id, b.id
)
SELECT CTE.id, CTE.Relation, CTE.Total / SQRT(C.total * D.total) AS Cosine FROM CTE 
  JOIN `Shop.PostCountWords` AS C ON C.id = postId
  JOIN Shop.PostCountWords AS D ON D.id = CTE.Relation
  ORDER BY Cosine DESC LIMIT 20;

Wordcloud on title

18. Xóa những ký tự đặc biệt trên cột title

SELECT id, title, REGEXP_REPLACE(title, '[^a-zA-z\\s]', '')
  FROM `bigquery-public-data.stackoverflow.stackoverflow_posts`
  WHERE title IS NOT NULL
  LIMIT 100;

Tách tiêu đề bài viết theo từng từ

SELECT id, SPLIT(LOWER(REGEXP_REPLACE(title, '[^a-zA-z\\s]', '')), ' ') as words
    FROM `bigquery-public-data.stackoverflow.stackoverflow_posts`
    WHERE title IS NOT NULL

19. Cho biết thông tin từng bài viết tương ứng với từ được tách từ bài viết đó

WITH CTE AS(
  SELECT id, SPLIT(LOWER(REGEXP_REPLACE(title, '[^a-zA-z\\s]', '')), ' ') as words
    FROM `bigquery-public-data.stackoverflow.stackoverflow_posts`
    WHERE title IS NOT NULL
)SELECT id, word FROM CTE, UNNEST(words) AS Word
  WHERE word <> ''
  LIMIT 100;

20. Rút trích thông tin từng bài viết tương ứng từ được tách từ title bài viết và số lượng từ tương ứng trong bài viết đó

CREATE TABLE Shop.PostTitleWords AS
  WITH CTE AS(
    SELECT id, SPLIT(LOWER(REGEXP_REPLACE(title, '[^a-zA-z\\s]|[\\]\\[\\-\\_]', '')), ' ') as words
      FROM `bigquery-public-data.stackoverflow.stackoverflow_posts`
      WHERE title IS NOT NULL
  )SELECT id, word, COUNT(*) AS total FROM CTE, UNNEST(words) AS Word
    WHERE word <> ''
    GROUP BY id, word;

21. Cho biết từ nào được xuất hiện nhiều nhất trong từng tiêu đề bài viết


SELECT word, SUM(Total) AS Total 
  FROM Shop.PostTitleWords
  GROUP BY word
  ORDER BY Total DESC
  LIMIT 10;

21. Rút trích thông tin từng bài viết tương ứng với những từ được tách từ title nhưng loại bỏ những từ stopword và số lượng từ tương ứng trong bài viết viết đó

CREATE TABLE Shop.PostTitleWordsWithoutStopword AS
  WITH CTE AS(
    SELECT id, SPLIT(LOWER(REGEXP_REPLACE(title, '[^a-zA-z\\s]|[\\]\\[\\-\\_]', '')), ' ') as words
      FROM `bigquery-public-data.stackoverflow.stackoverflow_posts`
      WHERE title IS NOT NULL
  )SELECT id, word, COUNT(*) AS total FROM CTE, UNNEST(words) AS Word
    WHERE word <> '' AND word NOT IN (SELECT word FROM `Shop.Stopword`)
    GROUP BY id, word;

Time Series

24. Cho biết số lượng bài theo tính theo hàng tháng của năm

SELECT FORMAT_TIMESTAMP('%Y%m', creation_date) AS YM, COUNT(*) AS Total 
  FROM `bigquery-public-data.stackoverflow.stackoverflow_posts`
  GROUP BY YM
  ORDER BY YM;

25. Rút trích thông tin số lượng bài viết tính theo hàng tháng của năm kèm theo số thứ tự lưu vào 1 bảng CountPostByMontly

CREATE OR REPLACE TABLE Shop.CountPostByMontly AS
  WITH CTE AS(
    SELECT FORMAT_TIMESTAMP('%Y%m', creation_date) AS YM, COUNT(*) AS Total 
      FROM `bigquery-public-data.stackoverflow.stackoverflow_posts`
      GROUP BY YM
  )
  SELECT ROW_NUMBER() OVER(ORDER BY YM) AS SrNo, * FROM CTE ORDER BY YM;

26. Tính trung bình trượt 4 tháng số lượng bài viết

SELECT ym, Total, AVG(Total) OVER(ORDER BY ym ROWS BETWEEN 3 PRECEDING AND CURRENT ROW) AS MovingAverage
  FROM Shop.CountPostByMontly ORDER BY ym;

27. Tính trung bình trượt 4 tháng không sử function window

SELECT B.*, AVG(A.Total) AS MovingAverage 
  FROM Shop.CountPostByMontly AS A JOIN Shop.CountPostByMontly AS B
  ON A.SrNo BETWEEN B.SrNo - 3 AND B.SrNo
  GROUP BY B.SrNo, B.YM, B.Total
  ORDER BY B.SrNo;

28. Tạo bảng trọng số để tính trung bình trượt theo trong số

CREATE OR REPLACE TABLE Shop.Weighted AS 
  SELECT ROW_NUMBER() OVER() AS Id, W 
    FROM UNNEST([0.1, 0.2, 0.3, 0.4]) AS W;

29. Tính trung bình trượt 4 tháng theo trong số cho ở bảng trên

SELECT B.*, SUM(A.Total)/ SUM(C.W) AS MovingAverage 
  FROM Shop.CountPostByMontly AS A JOIN Shop.CountPostByMontly AS B
  ON A.SrNo BETWEEN B.SrNo - 3 AND B.SrNo
  JOIN `Shop.Weighted` AS C
  ON C.Id + IF(B.SrNo > 4, B.SrNo - 4, 0) = A.SrNo
  GROUP BY B.SrNo, B.YM, B.Total
  ORDER BY B.SrNo;

30. Tính Exponential Smoothing với alpha = 0.1

WITH RECURSIVE CTE AS(
  SELECT *, CAST(Total AS FLOAT64) AS ExponentialSmoothing 
    FROM `Shop.CountPostByMontly` WHERE SrNo = 1
  UNION ALL
  SELECT B.*, 0.1 * B.Total + (1.0 - 0.1) * CTE.ExponentialSmoothing 
    FROM CTE 
    JOIN Shop.CountPostByMontly AS B ON CTE.SrNO = B.SrNo - 1
)SELECT * FROM CTE ORDER BY SrNo;

RFM Analysis

31. Cho biết thời gian hoạt động cuối cùng của các thành viên đăng bài

SELECT owner_user_id, owner_display_name, MAX(creation_date) AS LastActive 
  FROM `bigquery-public-data.stackoverflow.stackoverflow_posts`
  WHERE owner_user_id IS NOT NULL OR owner_display_name IS NOT NULL
  GROUP BY owner_user_id, owner_display_name

32. Cho biết thời gian hoạt động cuối cùng đăng bài rồi tăng thêm 1 ngày

SELECT TIMESTAMP_ADD(MAX(creation_date), INTERVAL 1 DAY)  
    FROM `bigquery-public-data.stackoverflow.stackoverflow_posts`

33. Recency (DAY) - Cho biết số ngày hoạt động gần nhất của từng thành viên đăng bài

DECLARE cd TIMESTAMP DEFAULT(
  SELECT TIMESTAMP_ADD(MAX(creation_date), INTERVAL 1 DAY)  
    FROM `bigquery-public-data.stackoverflow.stackoverflow_posts`
);
SELECT owner_user_id, owner_display_name, TIMESTAMP_DIFF(cd, MAX(creation_date), DAY) AS RecencyDay 
  FROM `bigquery-public-data.stackoverflow.stackoverflow_posts`
  WHERE owner_user_id IS NOT NULL OR owner_display_name IS NOT NULL
  GROUP BY owner_user_id, owner_display_name;

34. Xếp hạng từng thành viên tính theo thứ tự người có số ngày gần nhât được xếp hàng đầu

DECLARE cd TIMESTAMP DEFAULT(
  SELECT TIMESTAMP_ADD(MAX(creation_date), INTERVAL 1 DAY)  
    FROM `bigquery-public-data.stackoverflow.stackoverflow_posts`
);
WITH CTE AS(
  SELECT owner_user_id, owner_display_name, TIMESTAMP_DIFF(cd, MAX(creation_date), DAY) AS RecencyDay 
    FROM `bigquery-public-data.stackoverflow.stackoverflow_posts`
    WHERE owner_user_id IS NOT NULL OR owner_display_name IS NOT NULL
    GROUP BY owner_user_id, owner_display_name
)
SELECT *, DENSE_RANK() OVER(ORDER BY CTE.RecencyDay) 
  FROM CTE;

35. Xác định khoảng cách tổ h

DECLARE cd TIMESTAMP DEFAULT(
  SELECT TIMESTAMP_ADD(MAX(creation_date), INTERVAL 1 DAY)  
    FROM `bigquery-public-data.stackoverflow.stackoverflow_posts`
);
WITH CTE AS(
  SELECT owner_user_id, owner_display_name, TIMESTAMP_DIFF(cd, MAX(creation_date), DAY) AS RecencyDay 
    FROM `bigquery-public-data.stackoverflow.stackoverflow_posts`
    WHERE owner_user_id IS NOT NULL OR owner_display_name IS NOT NULL
    GROUP BY owner_user_id, owner_display_name
), CTE2 AS(
  SELECT *, DENSE_RANK() OVER(ORDER BY CTE.RecencyDay) AS Ranking
    FROM CTE
)SELECT CEILING((MAX(Ranking) - MIN(Ranking)) / 5) AS k 
  FROM CTE2;

36. Tính Recency Score

DECLARE cd TIMESTAMP DEFAULT(
  SELECT TIMESTAMP_ADD(MAX(creation_date), INTERVAL 1 DAY)  
    FROM `bigquery-public-data.stackoverflow.stackoverflow_posts`
);
WITH CTE AS(
  SELECT owner_user_id, owner_display_name, TIMESTAMP_DIFF(cd, MAX(creation_date), DAY) AS RecencyDay 
    FROM `bigquery-public-data.stackoverflow.stackoverflow_posts`
    WHERE owner_user_id IS NOT NULL OR owner_display_name IS NOT NULL
    GROUP BY owner_user_id, owner_display_name
), CTE2 AS(
  SELECT *, DENSE_RANK() OVER(ORDER BY CTE.RecencyDay) AS Ranking
    FROM CTE
), CTE3 AS(
  SELECT CEILING((MAX(Ranking) - MIN(Ranking)) / 5) AS h 
  FROM CTE2
)SELECT CTE2.*, 6 - CEILING(CTE2.Ranking / h) AS RScore FROM CTE2, CTE3;

37. Cho biết số lượng bài post của từng thành viên

SELECT owner_user_id, owner_display_name, COUNT(*) AS Frequency 
  FROM `bigquery-public-data.stackoverflow.stackoverflow_posts`
  WHERE owner_user_id IS NOT NULL OR owner_display_name IS NOT NULL
  GROUP BY owner_user_id, owner_display_name;

38. Xếp hạng thành viên theo thứ tự người có Frequency cao nhất

WITH CTE AS(
  SELECT owner_user_id, owner_display_name, COUNT(*) AS Frequency 
    FROM `bigquery-public-data.stackoverflow.stackoverflow_posts`
    WHERE owner_user_id IS NOT NULL OR owner_display_name IS NOT NULL
    GROUP BY owner_user_id, owner_display_name
)
SELECT CTE.*, DENSE_RANK() OVER(ORDER BY CTE.Frequency DESC) AS Ranking 
  FROM CTE;

39. Tính khoảng cách chia tổ h

WITH CTE AS(
  SELECT owner_user_id, owner_display_name, COUNT(*) AS Frequency 
    FROM `bigquery-public-data.stackoverflow.stackoverflow_posts`
    WHERE owner_user_id IS NOT NULL OR owner_display_name IS NOT NULL
    GROUP BY owner_user_id, owner_display_name
), CTE2 AS(
  SELECT CTE.*, DENSE_RANK() OVER(ORDER BY CTE.Frequency DESC) AS Ranking 
    FROM CTE
)SELECT CEILING((MAX(Ranking) - MIN(Ranking)) / 5) AS h 
  FROM CTE2;

40. Tính Frequency Score

WITH CTE AS(
  SELECT owner_user_id, owner_display_name, COUNT(*) AS Frequency 
    FROM `bigquery-public-data.stackoverflow.stackoverflow_posts`
    WHERE owner_user_id IS NOT NULL OR owner_display_name IS NOT NULL
    GROUP BY owner_user_id, owner_display_name
), CTE2 AS(
  SELECT CTE.*, DENSE_RANK() OVER(ORDER BY CTE.Frequency DESC) AS Ranking 
    FROM CTE
), CTE3 AS(
    SELECT CEILING((MAX(Ranking) - MIN(Ranking)) / 5) AS h 
    FROM CTE2
)SELECT CTE2.*, 6 - CEILING(Ranking / h) AS FScore
  FROM CTE2, CTE3;

41. Tính tổng điểm tích lũy của từng thành viên

SELECT owner_user_id, owner_display_name, SUM(score) AS Monetary 
    FROM `bigquery-public-data.stackoverflow.stackoverflow_posts`
    WHERE owner_user_id IS NOT NULL OR owner_display_name IS NOT NULL
    GROUP BY owner_user_id, owner_display_name
    ORDER BY Monetary

42. Xếp hạng từng thành viên theo thứ ưu tiên người có điểm tích lũy cao nhất

WITH CTE AS(
  SELECT owner_user_id, owner_display_name, SUM(score) AS Monetary 
    FROM `bigquery-public-data.stackoverflow.stackoverflow_posts`
    WHERE owner_user_id IS NOT NULL OR owner_display_name IS NOT NULL
    GROUP BY owner_user_id, owner_display_name
    ORDER BY Monetary
)SELECT *, DENSE_RANK() OVER(ORDER BY CTE.Monetary DESC) AS Ranking 
  FROM CTE;

43. Tính khoảng cách chia tổ

WITH CTE AS(
  SELECT owner_user_id, owner_display_name, SUM(score) AS Monetary 
    FROM `bigquery-public-data.stackoverflow.stackoverflow_posts`
    WHERE owner_user_id IS NOT NULL OR owner_display_name IS NOT NULL
    GROUP BY owner_user_id, owner_display_name
    ORDER BY Monetary
), CTE2 AS(
  SELECT *, DENSE_RANK() OVER(ORDER BY CTE.Monetary DESC) AS Ranking 
    FROM CTE
)SELECT CEILING( (MAX(Ranking) - MIN(Ranking)) / 5) AS h 
  FROM CTE2;

44. Tính Monetary Score

WITH CTE AS(
  SELECT owner_user_id, owner_display_name, SUM(score) AS Monetary 
    FROM `bigquery-public-data.stackoverflow.stackoverflow_posts`
    WHERE owner_user_id IS NOT NULL OR owner_display_name IS NOT NULL
    GROUP BY owner_user_id, owner_display_name
    ORDER BY Monetary
), CTE2 AS(
  SELECT *, DENSE_RANK() OVER(ORDER BY CTE.Monetary DESC) AS Ranking 
    FROM CTE
), CTE3 AS(
  SELECT CEILING( (MAX(Ranking) - MIN(Ranking)) / 5) AS h 
    FROM CTE2
)SELECT CTE2.*, 6 - CEILING(Ranking / h) AS MScore FROM CTE2, CTE3;

45. Lưu trữ dữ liệu trên table hoặc view RScore, FScore, MScore

Table ViewRScore

DECLARE cd TIMESTAMP DEFAULT(
  SELECT TIMESTAMP_ADD(MAX(creation_date), INTERVAL 1 DAY)  
    FROM `bigquery-public-data.stackoverflow.stackoverflow_posts`
);
CREATE OR REPLACE TABLE Shop.ViewRScore AS
  WITH CTE AS(
    SELECT owner_user_id, owner_display_name, TIMESTAMP_DIFF(cd, MAX(creation_date), DAY) AS RecencyDay 
      FROM `bigquery-public-data.stackoverflow.stackoverflow_posts`
      WHERE owner_user_id IS NOT NULL OR owner_display_name IS NOT NULL
      GROUP BY owner_user_id, owner_display_name
  ), CTE2 AS(
    SELECT *, DENSE_RANK() OVER(ORDER BY CTE.RecencyDay) AS Ranking
      FROM CTE
  ), CTE3 AS(
    SELECT CEILING((MAX(Ranking) - MIN(Ranking)) / 5) AS h 
    FROM CTE2
  )SELECT ROW_NUMBER() OVER(ORDER BY owner_user_id, owner_display_name) AS SrNo,
    CTE2.*, 6 - CEILING(CTE2.Ranking / h) AS RScore 
  FROM CTE2, CTE3;

Table ViewFScore

CREATE OR REPLACE TABLE Shop.ViewFScore AS
  WITH CTE AS(
    SELECT owner_user_id, owner_display_name, COUNT(*) AS Frequency 
      FROM `bigquery-public-data.stackoverflow.stackoverflow_posts`
      WHERE owner_user_id IS NOT NULL OR owner_display_name IS NOT NULL
      GROUP BY owner_user_id, owner_display_name
  ), CTE2 AS(
    SELECT CTE.*, DENSE_RANK() OVER(ORDER BY CTE.Frequency DESC) AS Ranking 
      FROM CTE
  ), CTE3 AS(
      SELECT CEILING((MAX(Ranking) - MIN(Ranking)) / 5) AS h 
      FROM CTE2
  )SELECT ROW_NUMBER() OVER(ORDER BY owner_user_id, owner_display_name) AS SrNo, 
    CTE2.*, 6 - CEILING(Ranking / h) AS FScore
  FROM CTE2, CTE3;

Table ViewMScore

CREATE OR REPLACE TABLE Shop.ViewMSCore AS
  WITH CTE AS(
    SELECT owner_user_id, owner_display_name, SUM(score) AS Monetary 
      FROM `bigquery-public-data.stackoverflow.stackoverflow_posts`
      WHERE owner_user_id IS NOT NULL OR owner_display_name IS NOT NULL
      GROUP BY owner_user_id, owner_display_name
      ORDER BY Monetary
  ), CTE2 AS(
    SELECT *, DENSE_RANK() OVER(ORDER BY CTE.Monetary DESC) AS Ranking 
      FROM CTE
  ), CTE3 AS(
    SELECT CEILING( (MAX(Ranking) - MIN(Ranking)) / 5) AS h 
      FROM CTE2
  )SELECT ROW_NUMBER() OVER(ORDER BY owner_user_id, owner_display_name) SrNo, 
    CTE2.*, 
    6 - CEILING(Ranking / h) AS MScore 
  FROM CTE2, CTE3;

46. Tổng hợp tính RFM Score

SELECT R.owner_user_id, 
  R.owner_display_name, 
  R.RScore, 
  F.FScore, 
  M.MScore, 
  (R.RScore + F.FScore + M.MScore) / 3.0 AS RFMScore  
FROM `Shop.ViewRScore` AS R
    JOIN `Shop.ViewFScore` AS F ON R.SrNO = F.SrNo
    JOIN `Shop.ViewMSCore` AS M ON R.SrNo = M.SrNo;

47. Tạo bảng để phân nhóm RFM

		1	3	4	5
Frequency Score	5	Can't Lose Them	Loyal Customer		Champoins
	4	At Risk	Loyal Customer		Champoins
	3	At Risk	Need Attention	Potential Loyalists
	2	Hibernate	About to Sleep	Potential Loyalists
	1	Hibernate	About to Sleep	Promissing	New Customers
Recency Score

48. Phân nhóm thành viên

SELECT R.RScore, F.FScore, Name, COUNT(*) AS Total
  FROM `Shop.ViewRScore` AS R JOIN `Shop.ViewFScore` AS F 
    ON R.SrNo = F.SrNo JOIN `Shop.rfm` 
    ON R.RScore = RS AND F.FScore = FS
  GROUP BY R.RScore, F.FScore, Name
  ORDER BY R.RScore, F.FScore;

SELECT Name, COUNT(*) AS Total
  FROM `Shop.ViewRScore` AS R JOIN `Shop.ViewFScore` AS F 
    ON R.SrNo = F.SrNo JOIN `Shop.rfm` 
    ON R.RScore = RS AND F.FScore = FS
  GROUP BY Name;