SELECT
p.difficulty,
COUNT(*) AS total_sub,
SUM(s.status = 'accepted') AS ac_cnt,
ROUND(SUM(s.status = 'accepted') / COUNT(*) * 100, 1) AS ac_rate
FROM cf_submission s
JOIN cf_problem p ON p.id = s.problem_id
GROUP BY p.difficulty
ORDER BY ac_rate DESC;
预期：hard 1/1/100.0、medium 5/3/60.0、easy 6/3/50.0。
任务 2：每人最近一次 AC 的题目（窗口）
SELECT username, title, submitted_at
FROM (
SELECT
u.username, p.title, s.submitted_at,
ROW_NUMBER() OVER (PARTITION BY s.user_id ORDER BY s.submitted_at DESC) AS rn
FROM cf_submission s
JOIN cf_user u ON u.id = s.user_id
JOIN cf_problem p ON p.id = s.problem_id
WHERE s.status = 'accepted'
) t
WHERE rn = 1
ORDER BY submitted_at;
WITH days AS (
SELECT user_id, DATE(practiced_at) AS d
FROM cf_practice_record
GROUP BY user_id, DATE(practiced_at)
),
streaks AS (
SELECT
user_id,
d,
DATE_SUB(d, INTERVAL ROW_NUMBER() OVER (PARTITION BY user_id ORDER BY d) DAY) AS grp
FROM days
)
SELECT
user_id,
MIN(d) AS start_day,
MAX(d) AS end_day,
COUNT(*) AS streak_days
FROM streaks
GROUP BY user_id, grp
ORDER BY user_id, streak_days DESC;
WITH RECURSIVE days AS (
SELECT CAST('2025-06-01' AS DATE) AS d
UNION ALL
SELECT d + INTERVAL 1 DAY FROM days WHERE d < CAST('2025-06-07' AS DATE)
)
SELECT
dy.d,
COUNT(s.id) AS cnt
FROM days dy
LEFT JOIN cf_submission s ON DATE(s.submitted_at) = dy.d
GROUP BY dy.d
ORDER BY dy.d;
SELECT
u.username,
COALESCE(SUM(p.difficulty = 'easy'), 0) AS easy_cnt,
COALESCE(SUM(p.difficulty = 'medium'), 0) AS medium_cnt,
COALESCE(SUM(p.difficulty = 'hard'), 0) AS hard_cnt
FROM cf_user u
LEFT JOIN cf_submission s ON s.user_id = u.id
LEFT JOIN cf_problem p ON p.id = s.problem_id
GROUP BY u.id, u.username
ORDER BY u.id;
-- 先确认索引存在（第 11 课建的；TRUNCATE 不会删索引）
SHOW INDEX FROM cf_submission;
-- 若没有则补建：CREATE INDEX idx_user_status ON cf_submission (user_id, status);
EXPLAIN
SELECT user_id, status FROM cf_submission
WHERE user_id = 1 AND status = 'accepted';
-- 预期：type=ref、key=idx_user_status、Extra=Using index（覆盖）
EXPLAIN
SELECT * FROM cf_submission WHERE DATE(submitted_at) = '2025-06-01';
-- 预期：type=ALL（函数包裹失效），改写为范围条件后变 range