排名

用户解题统计

过去一年提交了

勋章 ①金银铜：在竞赛中获得第一二三名；②好习惯：自然月10天提交；③里程碑：解决1/2/5/10/20/50/100/200题；④每周打卡挑战：完成每周5题，每年1月1日清零。

错题集 数据思维刷题中答错的题目

模块 知识点 题目 你的答案 正确答案 操作
暂无错题，继续保持！

收藏

收藏日期 题目名称 解决状态
没有收藏的题目。

评论笔记

评论日期 题目名称 评论内容 站长评论
没有评论过的题目。

提交记录

提交日期 题目名称 提交代码
2026-04-06 财源广进：开除摸鱼的员工  
SELECT
e.id AS employee_id,
COALESCE(a.attendance_score, 0) + COALESCE(c.compensation_score, 0) + COALESCE(w.walk_score, 0) + COALESCE(f.doc_score, 0) AS score
FROM employees e
LEFT JOIN (
SELECT
employee_id,
CASE WHEN emp_avg_hours < overall_avg_hours THEN 1 ELSE 0 END AS attendance_score
FROM (
SELECT
employee_id,
AVG(daily_hours) AS emp_avg_hours,
AVG(AVG(daily_hours)) OVER () AS overall_avg_hours
FROM (
SELECT
employee_id,
DATE(punch_time) AS punch_date,
CASE WHEN COUNT(punch_time) < 2 THEN 0 ELSE TIMESTAMPDIFF(MINUTE, MIN(punch_time), MAX(punch_time)) / 60 END AS daily_hours
FROM attendance
WHERE YEAR(punch_time) = 2025
GROUP BY employee_id, DATE(punch_time)
) AS daily
GROUP BY employee_id
) AS avg_att
) AS a ON e.id = a.employee_id
LEFT JOIN (
SELECT
employee_id,
CASE
WHEN compensation < 10 THEN 3
WHEN compensation < 20 THEN 2
WHEN compensation < 30 THEN 1
ELSE 0
END AS compensation_score
FROM (
SELECT
id AS employee_id,
(FLOOR(TIMESTAMPDIFF(MONTH, STR_TO_DATE(hire_date, '%Y%m%d'), '2026-03-06') / 12) + 1) * (salary / 12) AS compensation
FROM employees
) AS comp_calc
) AS c ON e.id = c.employee_id
LEFT JOIN (
SELECT
employee_id,
CASE
WHEN total_days > 70 AND days_over_3 > 25 THEN -3
WHEN total_days > 60 THEN -1
ELSE 0
END AS walk_score
FROM (
SELECT
employee_id,
COUNT(DISTINCT walk_date) AS total_days,
COUNT(DISTINCT CASE WHEN daily_walk_count > 3 THEN walk_date END) AS days_over_3
FROM (
SELECT
employee_id,
DATE(timestamp) AS walk_date,
COUNT(*) AS daily_walk_count
FROM access_control
WHERE YEAR(timestamp) = 2025 AND TIME(timestamp) NOT BETWEEN '12:00:00' AND '14:00:00'
GROUP BY employee_id, DATE(timestamp)
) AS filtered_walk
GROUP BY employee_id
) AS walk_stats
) AS w ON e.id = w.employee_id
LEFT JOIN (
SELECT
employee_id,
CASE
WHEN (IF(emp_avg_interaction < overall_avg_interaction, 1, 0) + IF(emp_avg_messages < overall_avg_messages, 1, 0) + IF(emp_avg_documents < overall_avg_documents, 1, 0)) = 3 THEN 3
WHEN (IF(emp_avg_interaction < overall_avg_interaction, 1, 0) + IF(emp_avg_messages < overall_avg_messages, 1, 0) + IF(emp_avg_documents < overall_avg_documents, 1, 0)) = 2 THEN 1
ELSE 0
END AS doc_score
FROM (
SELECT
employee_id,
AVG(daily_interaction) AS emp_avg_interaction,
AVG(daily_messages) AS emp_avg_messages,
AVG(daily_documents) AS emp_avg_documents,
AVG(AVG(daily_interaction)) OVER () AS overall_avg_interaction,
AVG(AVG(daily_messages)) OVER () AS overall_avg_messages,
AVG(AVG(daily_documents)) OVER () AS overall_avg_documents
FROM (
SELECT
employee_id,
daily_interaction,
daily_messages,
daily_documents
FROM flybook_stats
WHERE stat_date >= 20250131 AND YEAR(STR_TO_DATE(stat_date, '%Y%m%d')) = 2025
) AS filtered_fb
GROUP BY employee_id
) AS fb_avg
) AS f ON e.id = f.employee_id
ORDER BY score DESC;
2026-04-06 财源广进：开除摸鱼的员工  
SELECT
e.id AS employee_id,
COALESCE(a.attendance_score, 0) + COALESCE(c.compensation_score, 0) + COALESCE(w.walk_score, 0) + COALESCE(f.doc_score, 0) AS score
FROM employees e
LEFT JOIN (
SELECT
employee_id,
CASE WHEN emp_avg_hours < overall_avg_hours THEN 1 ELSE 0 END AS attendance_score
FROM (
SELECT
employee_id,
AVG(daily_hours) AS emp_avg_hours,
AVG(AVG(daily_hours)) OVER () AS overall_avg_hours
FROM (
SELECT
employee_id,
DATE(punch_time) AS punch_date,
CASE WHEN COUNT(punch_time) < 2 THEN 0 ELSE TIMESTAMPDIFF(MINUTE, MIN(punch_time), MAX(punch_time)) / 60 END AS daily_hours
FROM attendance
WHERE YEAR(punch_time) = 2025
GROUP BY employee_id, DATE(punch_time)
) AS daily
GROUP BY employee_id
) AS avg_att
) AS a ON e.id = a.employee_id
LEFT JOIN (
SELECT
employee_id,
CASE
WHEN compensation >= 30 THEN 3
WHEN compensation >= 20 THEN 2
WHEN compensation >= 10 THEN 1
ELSE 0
END AS compensation_score
FROM (
SELECT
id AS employee_id,
(FLOOR(TIMESTAMPDIFF(MONTH, STR_TO_DATE(hire_date, '%Y%m%d'), '2026-03-06') / 12) + 1) * (salary / 12) AS compensation
FROM employees
) AS comp_calc
) AS c ON e.id = c.employee_id
LEFT JOIN (
SELECT
employee_id,
CASE
WHEN total_days > 70 AND days_over_3 > 25 THEN -3
WHEN total_days > 60 THEN -1
ELSE 0
END AS walk_score
FROM (
SELECT
employee_id,
COUNT(DISTINCT walk_date) AS total_days,
COUNT(DISTINCT CASE WHEN daily_walk_count > 3 THEN walk_date END) AS days_over_3
FROM (
SELECT
employee_id,
DATE(timestamp) AS walk_date,
COUNT(*) AS daily_walk_count
FROM access_control
WHERE YEAR(timestamp) = 2025 AND TIME(timestamp) NOT BETWEEN '12:00:00' AND '14:00:00'
GROUP BY employee_id, DATE(timestamp)
) AS filtered_walk
GROUP BY employee_id
) AS walk_stats
) AS w ON e.id = w.employee_id
LEFT JOIN (
SELECT
employee_id,
CASE
WHEN (IF(emp_avg_interaction < overall_avg_interaction, 1, 0) + IF(emp_avg_messages < overall_avg_messages, 1, 0) + IF(emp_avg_documents < overall_avg_documents, 1, 0)) = 3 THEN 3
WHEN (IF(emp_avg_interaction < overall_avg_interaction, 1, 0) + IF(emp_avg_messages < overall_avg_messages, 1, 0) + IF(emp_avg_documents < overall_avg_documents, 1, 0)) = 2 THEN 1
ELSE 0
END AS doc_score
FROM (
SELECT
employee_id,
AVG(daily_interaction) AS emp_avg_interaction,
AVG(daily_messages) AS emp_avg_messages,
AVG(daily_documents) AS emp_avg_documents,
AVG(AVG(daily_interaction)) OVER () AS overall_avg_interaction,
AVG(AVG(daily_messages)) OVER () AS overall_avg_messages,
AVG(AVG(daily_documents)) OVER () AS overall_avg_documents
FROM (
SELECT
employee_id,
daily_interaction,
daily_messages,
daily_documents
FROM flybook_stats
WHERE stat_date >= 20250131 AND YEAR(STR_TO_DATE(stat_date, '%Y%m%d')) = 2025
) AS filtered_fb
GROUP BY employee_id
) AS fb_avg
) AS f ON e.id = f.employee_id
ORDER BY score ASC;