wordle_day

1_wordle_day_scheme.sql

CREATE TABLE `wordle_day` (
	`day` INT(11) NOT NULL,
	`uid` INT(11) NOT NULL,
	`attempts` TINYINT(1) NULL DEFAULT NULL,
	`won` TINYINT(1) NULL DEFAULT '0',
	PRIMARY KEY (`day`, `uid`)
)

2_Текущая_стата.sql

-- Топ по непрерывной игре в Вордли
-- Количество непрерывных дней игры, средний процент побед, среднее число попыток

WITH consecutive_days
  AS (SELECT UID,
             DAY,
             attempts,
             won,
             DAY - ROW_NUMBER() OVER (PARTITION BY UID ORDER BY DAY) AS grp
        FROM wordle_day),
     latest_sequence
  AS (SELECT c.uid,
             c.day,
             c.attempts,
             c.won
        FROM consecutive_days c
       INNER JOIN (SELECT UID, MAX(grp) AS max_grp FROM consecutive_days GROUP BY UID) m
          ON c.uid = m.uid
         AND c.grp = m.max_grp)
SELECT UID,
       COUNT(*) AS max_consecutive_days,
       ROUND(AVG(won) * 100) AS avg_win_percentage,
       ROUND(AVG(attempts), 2) AS avg_attempts_per_game
  FROM latest_sequence
 GROUP BY UID
HAVING max_consecutive_days > 1
 ORDER BY max_consecutive_days DESC

3_Полная_стата.sql

-- Всего сыграно в Вордли
-- Количество игр, максимум дней подряд, % побед, среднее количество попыток

SET @curUid := 0, @curStreak := 0, @lastDay := 0;

SELECT 
  a.uid,
  d.total_games_played,
  a.max_consecutive_days,
  b.avg_win_percentage,
  c.avg_attempts_per_game
FROM
(SELECT uid, MAX(consecutive_days) as max_consecutive_days
FROM (
  SELECT
    uid,
    day,
    CASE
      WHEN @curUid = uid AND day = @lastDay + 1 THEN @curStreak := @curStreak + 1
      WHEN @curUid = uid THEN @curStreak := 1
      ELSE @curStreak := 1
    END AS consecutive_days,
    @curUid := uid,
    @lastDay := day
  FROM wordle_day
  ORDER BY uid, day
) AS subquery
GROUP BY uid) a
JOIN
  (SELECT uid, ROUND(AVG(won) * 100) AS avg_win_percentage FROM wordle_day GROUP BY uid) b ON a.uid = b.uid
JOIN
  (SELECT uid, ROUND(AVG(attempts), 2) AS avg_attempts_per_game FROM wordle_day WHERE attempts IS NOT NULL GROUP BY uid) c ON a.uid = c.uid
JOIN 
  (SELECT uid, COUNT(*) AS total_games_played FROM wordle_day GROUP BY uid) d ON a.uid = d.uid
ORDER BY d.total_games_played DESC