0

Please login or register to vote for this query.

(click on this box to dismiss)

Q&A for professional and enthusiast programmers

WITH
Spikes AS (
  SELECT
    CONVERT(DATE, MAX(U.CreationDate), 101) AS Date
    ,COUNT(U.Id) AS NewCount
  FROM USERS U
  WHERE
    --U.CreationDate > '2021-01-01'
    U.AboutMe LIKE N'%[。，]%'
  GROUP BY CONVERT(DATE, U.CreationDate, 101)
  HAVING COUNT(U.Id) > ##NewProfilesOnDayThreshold:int?70##
  --ORDER BY DateCreated ASC
),
Profiles AS (
  SELECT
    U.CreationDate
    --,U.LastAccessDate -- generally the same as CreationDate
    ,U.Reputation AS REP
    ,U.Id AS [User Link]
    ,U.DisplayName
    ,U.AboutMe
    ,U.Views
    ,U.UpVotes
    ,DownVotes
  FROM Users U
  WHERE U.AboutMe COLLATE Latin1_General_BIN LIKE N'%[。，]%'
)
SELECT * FROM Spikes JOIN Profiles ON
  Spikes.Date = CONVERT(DATE, Profiles.CreationDate, 101)
;

fork query

created nov 14 22 starball