排名

用户解题统计

过去一年提交了

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

收藏

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

评论笔记

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

提交记录

提交日期 题目名称 提交代码
2026-01-17 财源广进：开除摸鱼的员工  
WITH 
daily_attendance AS (
SELECT 
employee_id,
DATE(punch_time) AS check_date,
TIMESTAMPDIFF(MINUTE, MIN(punch_time), MAX(punch_time)) / 60.0 AS daily_hours
FROM attendance
WHERE YEAR(punch_time) = 2025
GROUP BY employee_id, DATE(punch_time)
HAVING COUNT(*) > 1
),
employee_avg_hours AS (
SELECT 
employee_id,
AVG(daily_hours) AS avg_daily_hours
FROM daily_attendance
GROUP BY employee_id
),
global_avg_hours AS (
SELECT COALESCE(AVG(avg_daily_hours), 0) AS global_avg_daily_hours
FROM employee_avg_hours
),
attendance_score AS (
SELECT 
e.id AS employee_id,
CASE 
WHEN COALESCE(eah.avg_daily_hours, 0) = 0 THEN 1
WHEN eah.avg_daily_hours < gah.global_avg_daily_hours THEN 1
ELSE 0
END AS attendance_score
FROM employees e
CROSS JOIN global_avg_hours gah
LEFT JOIN employee_avg_hours eah ON e.id = eah.employee_id
),
compensation_score AS (
SELECT 
id AS employee_id,
CASE 
WHEN (DATEDIFF('2026-03-06', hire_date) / 365.0 + 1) * salary / 12 < 10 THEN 3
WHEN (DATEDIFF('2026-03-06', hire_date) / 365.0 + 1) * salary / 12 < 20 THEN 2
WHEN (DATEDIFF('2026-03-06', hire_date) / 365.0 + 1) * salary / 12 < 30 THEN 1
ELSE 0
END AS compensation_score
FROM employees
),
daily_walk_summary AS (
SELECT 
employee_id,
DATE(timestamp) AS access_date,
COUNT(*) AS total_daily_walks,
CASE WHEN COUNT(*) > 3 THEN 1 ELSE 0 END AS is_over_3_walks
FROM access_control
WHERE YEAR(timestamp) = 2025
AND (
(HOUR(timestamp) >= 9 AND HOUR(timestamp) < 12)
OR (HOUR(timestamp) >= 14 AND HOUR(timestamp) < 18)
)
GROUP BY employee_id, DATE(timestamp)
),
employee_walk_stats AS (
SELECT 
employee_id,
COUNT(DISTINCT access_date) AS walk_days,
SUM(is_over_3_walks) AS days_over_3_walks
FROM daily_walk_summary
GROUP BY employee_id
),
walking_score AS (
SELECT 
e.id AS employee_id,
CASE 
WHEN COALESCE(ews.walk_days, 0) > 70 AND COALESCE(ews.days_over_3_walks, 0) > 25 THEN 3
WHEN COALESCE(ews.walk_days, 0) > 60 THEN 1
ELSE 0
END AS walking_score
FROM employees e
LEFT JOIN employee_walk_stats ews ON e.id = ews.employee_id
),
flybook_after_launch AS (
SELECT 
employee_id,
daily_interaction,
daily_messages,
daily_documents
FROM flybook_stats
WHERE stat_date >= '2025-01-31'
),
avg_stats AS (
SELECT 
COALESCE(AVG(daily_interaction), 0) AS avg_interaction_count,
COALESCE(AVG(daily_messages), 0) AS avg_message_count,
COALESCE(AVG(daily_documents), 0) AS avg_daily_document_count
FROM flybook_after_launch
),
employee_flybook_stats AS (
SELECT 
employee_id,
AVG(daily_interaction) AS avg_interaction_count,
AVG(daily_messages) AS avg_message_count,
AVG(daily_documents) AS avg_daily_document_count
FROM flybook_after_launch
GROUP BY employee_id
),
document_score AS (
SELECT 
e.id AS employee_id,
CASE 
WHEN COALESCE(efs.avg_interaction_count, 0) < avs.avg_interaction_count
 AND COALESCE(efs.avg_message_count, 0) < avs.avg_message_count
 AND COALESCE(efs.avg_daily_document_count, 0) < avs.avg_daily_document_count THEN 3
WHEN (
(COALESCE(efs.avg_interaction_count, 0) < avs.avg_interaction_count AND COALESCE(efs.avg_message_count, 0) < avs.avg_message_count)
OR (COALESCE(efs.avg_interaction_count, 0) < avs.avg_interaction_count AND COALESCE(efs.avg_daily_document_count, 0) < avs.avg_daily_document_count)
OR (COALESCE(efs.avg_message_count, 0) < avs.avg_message_count AND COALESCE(efs.avg_daily_document_count, 0) < avs.avg_daily_document_count)
) THEN 1
ELSE 0
END AS document_score
FROM employees e
CROSS JOIN avg_stats avs
LEFT JOIN employee_flybook_stats efs ON e.id = efs.employee_id
)
SELECT 
e.id AS employee_id,
COALESCE(att.attendance_score, 0) + 
COALESCE(comp.compensation_score, 0) + 
COALESCE(walk.walking_score, 0) + 
COALESCE(doc.document_score, 0) AS score
FROM employees e
LEFT JOIN attendance_score att ON e.id = att.employee_id
LEFT JOIN compensation_score comp ON e.id = comp.employee_id
LEFT JOIN walking_score walk ON e.id = walk.employee_id
LEFT JOIN document_score doc ON e.id = doc.employee_id
;
2026-01-17 财源广进：开除摸鱼的员工  
WITH 
daily_attendance AS (
SELECT 
employee_id,
DATE(punch_time) AS check_date,
COUNT(*) AS punch_count,
MIN(punch_time) AS first_punch,
MAX(punch_time) AS last_punch,
TIMESTAMPDIFF(MINUTE, MIN(punch_time), MAX(punch_time)) / 60.0 AS daily_hours
FROM attendance
WHERE YEAR(punch_time) = 2025
GROUP BY employee_id, DATE(punch_time)
HAVING punch_count > 1
),
employee_avg_hours AS (
SELECT 
employee_id,
AVG(daily_hours) AS avg_daily_hours
FROM daily_attendance
GROUP BY employee_id
),
all_employee_avg_hours AS (
SELECT 
e.id AS employee_id,
COALESCE(eah.avg_daily_hours, 0) AS avg_daily_hours
FROM employees e
LEFT JOIN employee_avg_hours eah ON e.id = eah.employee_id
),
global_avg_hours AS (
SELECT AVG(avg_daily_hours) AS global_avg_daily_hours
FROM employee_avg_hours
),
attendance_score AS (
SELECT 
aeah.employee_id,
aeah.avg_daily_hours,
gah.global_avg_daily_hours,
CASE 
WHEN aeah.avg_daily_hours > 0 AND aeah.avg_daily_hours < gah.global_avg_daily_hours THEN 1
WHEN aeah.avg_daily_hours = 0 THEN 1
ELSE 0
END AS attendance_score
FROM all_employee_avg_hours aeah
CROSS JOIN global_avg_hours gah
),
compensation_calc AS (
SELECT 
id AS employee_id,
hire_date,
salary,
DATEDIFF('2026-03-06', hire_date) / 365.0 AS work_years,
(DATEDIFF('2026-03-06', hire_date) / 365.0 + 1) * salary / 12 AS compensation_amount
FROM employees
),
compensation_score AS (
SELECT 
employee_id,
hire_date,
salary,
work_years,
compensation_amount,
CASE 
WHEN compensation_amount < 10 THEN 3
WHEN compensation_amount < 20 THEN 2
WHEN compensation_amount < 30 THEN 1
ELSE 0
END AS compensation_score
FROM compensation_calc
),
daily_walk_summary AS (
SELECT 
employee_id,
DATE(timestamp) AS access_date,
COUNT(*) AS total_daily_walks,
CASE WHEN COUNT(*) > 3 THEN 1 ELSE 0 END AS is_over_3_walks
FROM access_control
WHERE YEAR(timestamp) = 2025
AND (
(HOUR(timestamp) >= 9 AND HOUR(timestamp) < 12)
OR (HOUR(timestamp) >= 14 AND HOUR(timestamp) < 18)
)
GROUP BY employee_id, DATE(timestamp)
),
employee_walk_stats AS (
SELECT 
employee_id,
COUNT(DISTINCT access_date) AS walk_days,
SUM(is_over_3_walks) AS days_over_3_walks
FROM daily_walk_summary
GROUP BY employee_id
),
walking_score AS (
SELECT 
e.id AS employee_id,
COALESCE(ews.walk_days, 0) AS walk_days,
COALESCE(ews.days_over_3_walks, 0) AS days_over_3_walks,
CASE 
WHEN COALESCE(ews.walk_days, 0) > 70 AND COALESCE(ews.days_over_3_walks, 0) > 25 THEN 3
WHEN COALESCE(ews.walk_days, 0) > 60 THEN 1
ELSE 0
END AS walking_score
FROM employees e
LEFT JOIN employee_walk_stats ews ON e.id = ews.employee_id
),
flybook_after_launch AS (
SELECT 
employee_id,
daily_interaction,
daily_messages,
daily_documents
FROM flybook_stats
WHERE stat_date >= '2025-01-31'
),
avg_stats AS (
SELECT 
CASE WHEN COUNT(*) > 0 THEN AVG(daily_interaction) ELSE 0 END AS avg_interaction_count,
CASE WHEN COUNT(*) > 0 THEN AVG(daily_messages) ELSE 0 END AS avg_message_count,
CASE WHEN COUNT(*) > 0 THEN AVG(daily_documents) ELSE 0 END AS avg_daily_document_count
FROM flybook_after_launch
),
employee_flybook_stats AS (
SELECT 
employee_id,
AVG(daily_interaction) AS avg_interaction_count,
AVG(daily_messages) AS avg_message_count,
AVG(daily_documents) AS avg_daily_document_count
FROM flybook_after_launch
GROUP BY employee_id
),
employee_score_calc AS (
SELECT 
e.id AS employee_id,
COALESCE(efs.avg_interaction_count, 0) AS avg_interaction_count,
COALESCE(efs.avg_message_count, 0) AS avg_message_count,
COALESCE(efs.avg_daily_document_count, 0) AS avg_daily_document_count,
avs.avg_interaction_count AS global_avg_interaction_count,
avs.avg_message_count AS global_avg_message_count,
avs.avg_daily_document_count AS global_avg_daily_document_count
FROM employees e
CROSS JOIN avg_stats avs
LEFT JOIN employee_flybook_stats efs ON e.id = efs.employee_id
),
document_score AS (
SELECT 
employee_id,
avg_interaction_count,
avg_message_count,
avg_daily_document_count,
CASE 
WHEN avg_interaction_count < global_avg_interaction_count
 AND avg_message_count < global_avg_message_count
 AND avg_daily_document_count < global_avg_daily_document_count THEN 3
WHEN (
(avg_interaction_count < global_avg_interaction_count AND avg_message_count < global_avg_message_count)
OR (avg_interaction_count < global_avg_interaction_count AND avg_daily_document_count < global_avg_daily_document_count)
OR (avg_message_count < global_avg_message_count AND avg_daily_document_count < global_avg_daily_document_count)
) THEN 1
ELSE 0
END AS document_score
FROM employee_score_calc
)
SELECT 
e.id AS employee_id,
COALESCE(att.attendance_score, 0) + 
COALESCE(comp.compensation_score, 0) + 
COALESCE(walk.walking_score, 0) + 
COALESCE(doc.document_score, 0) AS score
FROM employees e
LEFT JOIN attendance_score att ON e.id = att.employee_id
LEFT JOIN compensation_score comp ON e.id = comp.employee_id
LEFT JOIN walking_score walk ON e.id = walk.employee_id
LEFT JOIN document_score doc ON e.id = doc.employee_id
;
2026-01-17 财源广进：开除摸鱼的员工  
WITH 
daily_attendance AS (
SELECT 
employee_id,
DATE(punch_time) AS check_date,
COUNT(*) AS punch_count,
MIN(punch_time) AS first_punch,
MAX(punch_time) AS last_punch,
TIMESTAMPDIFF(MINUTE, MIN(punch_time), MAX(punch_time)) / 60.0 AS daily_hours
FROM attendance
WHERE YEAR(punch_time) = 2025
GROUP BY employee_id, DATE(punch_time)
HAVING punch_count > 1
),
employee_avg_hours AS (
SELECT 
employee_id,
AVG(daily_hours) AS avg_daily_hours
FROM daily_attendance
GROUP BY employee_id
),
all_employee_avg_hours AS (
SELECT 
e.id AS employee_id,
COALESCE(eah.avg_daily_hours, 0) AS avg_daily_hours
FROM employees e
LEFT JOIN employee_avg_hours eah ON e.id = eah.employee_id
),
global_avg_hours AS (
SELECT AVG(avg_daily_hours) AS global_avg_daily_hours
FROM employee_avg_hours
),
attendance_score AS (
SELECT 
aeah.employee_id,
aeah.avg_daily_hours,
gah.global_avg_daily_hours,
CASE 
WHEN aeah.avg_daily_hours > 0 AND aeah.avg_daily_hours < gah.global_avg_daily_hours THEN 1
WHEN aeah.avg_daily_hours = 0 THEN 1
ELSE 0
END AS attendance_score
FROM all_employee_avg_hours aeah
CROSS JOIN global_avg_hours gah
),
compensation_calc AS (
SELECT 
id AS employee_id,
hire_date,
salary,
DATEDIFF('2026-03-06', hire_date) / 365.0 AS work_years,
(DATEDIFF('2026-03-06', hire_date) / 365.0 + 1) * salary AS compensation_amount
FROM employees
),
compensation_score AS (
SELECT 
employee_id,
hire_date,
salary,
work_years,
compensation_amount,
CASE 
WHEN compensation_amount < 10 THEN 3
WHEN compensation_amount < 20 THEN 2
WHEN compensation_amount < 30 THEN 1
ELSE 0
END AS compensation_score
FROM compensation_calc
),
daily_walk_summary AS (
SELECT 
employee_id,
DATE(timestamp) AS access_date,
COUNT(*) AS total_daily_walks,
CASE WHEN COUNT(*) > 3 THEN 1 ELSE 0 END AS is_over_3_walks
FROM access_control
WHERE YEAR(timestamp) = 2025
AND (
(HOUR(timestamp) >= 9 AND HOUR(timestamp) < 12)
OR (HOUR(timestamp) >= 14 AND HOUR(timestamp) < 18)
)
GROUP BY employee_id, DATE(timestamp)
),
employee_walk_stats AS (
SELECT 
employee_id,
COUNT(DISTINCT access_date) AS walk_days,
SUM(is_over_3_walks) AS days_over_3_walks
FROM daily_walk_summary
GROUP BY employee_id
),
walking_score AS (
SELECT 
e.id AS employee_id,
COALESCE(ews.walk_days, 0) AS walk_days,
COALESCE(ews.days_over_3_walks, 0) AS days_over_3_walks,
CASE 
WHEN COALESCE(ews.walk_days, 0) > 70 AND COALESCE(ews.days_over_3_walks, 0) > 25 THEN 3
WHEN COALESCE(ews.walk_days, 0) > 60 THEN 1
ELSE 0
END AS walking_score
FROM employees e
LEFT JOIN employee_walk_stats ews ON e.id = ews.employee_id
),
flybook_after_launch AS (
SELECT 
employee_id,
daily_interaction,
daily_messages,
daily_documents
FROM flybook_stats
WHERE stat_date >= '2025-01-31'
),
avg_stats AS (
SELECT 
CASE WHEN COUNT(*) > 0 THEN AVG(daily_interaction) ELSE 0 END AS avg_interaction_count,
CASE WHEN COUNT(*) > 0 THEN AVG(daily_messages) ELSE 0 END AS avg_message_count,
CASE WHEN COUNT(*) > 0 THEN AVG(daily_documents) ELSE 0 END AS avg_daily_document_count
FROM flybook_after_launch
),
employee_flybook_stats AS (
SELECT 
employee_id,
AVG(daily_interaction) AS avg_interaction_count,
AVG(daily_messages) AS avg_message_count,
AVG(daily_documents) AS avg_daily_document_count
FROM flybook_after_launch
GROUP BY employee_id
),
employee_score_calc AS (
SELECT 
e.id AS employee_id,
COALESCE(efs.avg_interaction_count, 0) AS avg_interaction_count,
COALESCE(efs.avg_message_count, 0) AS avg_message_count,
COALESCE(efs.avg_daily_document_count, 0) AS avg_daily_document_count,
avs.avg_interaction_count AS global_avg_interaction_count,
avs.avg_message_count AS global_avg_message_count,
avs.avg_daily_document_count AS global_avg_daily_document_count
FROM employees e
CROSS JOIN avg_stats avs
LEFT JOIN employee_flybook_stats efs ON e.id = efs.employee_id
),
document_score AS (
SELECT 
employee_id,
avg_interaction_count,
avg_message_count,
avg_daily_document_count,
CASE 
WHEN avg_interaction_count < global_avg_interaction_count
 AND avg_message_count < global_avg_message_count
 AND avg_daily_document_count < global_avg_daily_document_count THEN 3
WHEN (
(avg_interaction_count < global_avg_interaction_count AND avg_message_count < global_avg_message_count)
OR (avg_interaction_count < global_avg_interaction_count AND avg_daily_document_count < global_avg_daily_document_count)
OR (avg_message_count < global_avg_message_count AND avg_daily_document_count < global_avg_daily_document_count)
) THEN 1
ELSE 0
END AS document_score
FROM employee_score_calc
)
SELECT 
e.id AS employee_id,
COALESCE(att.attendance_score, 0) + 
COALESCE(comp.compensation_score, 0) + 
COALESCE(walk.walking_score, 0) + 
COALESCE(doc.document_score, 0) AS score
FROM employees e
LEFT JOIN attendance_score att ON e.id = att.employee_id
LEFT JOIN compensation_score comp ON e.id = comp.employee_id
LEFT JOIN walking_score walk ON e.id = walk.employee_id
LEFT JOIN document_score doc ON e.id = doc.employee_id
;
2026-01-17 财源广进：开除摸鱼的员工  
WITH 
daily_attendance AS (
SELECT 
employee_id,
DATE(punch_time) AS check_date,
COUNT(*) AS punch_count,
MIN(punch_time) AS first_punch,
MAX(punch_time) AS last_punch,
TIMESTAMPDIFF(MINUTE, MIN(punch_time), MAX(punch_time)) / 60.0 AS daily_hours
FROM attendance
WHERE YEAR(punch_time) = 2025
GROUP BY employee_id, DATE(punch_time)
HAVING punch_count > 1
),
employee_avg_hours AS (
SELECT 
employee_id,
AVG(daily_hours) AS avg_daily_hours
FROM daily_attendance
GROUP BY employee_id
),
all_employee_avg_hours AS (
SELECT 
e.id AS employee_id,
COALESCE(eah.avg_daily_hours, 0) AS avg_daily_hours
FROM employees e
LEFT JOIN employee_avg_hours eah ON e.id = eah.employee_id
),
global_avg_hours AS (
SELECT AVG(avg_daily_hours) AS global_avg_daily_hours
FROM all_employee_avg_hours
),
attendance_score AS (
SELECT 
aeah.employee_id,
aeah.avg_daily_hours,
gah.global_avg_daily_hours,
CASE 
WHEN aeah.avg_daily_hours < gah.global_avg_daily_hours THEN 1
ELSE 0
END AS attendance_score
FROM all_employee_avg_hours aeah
CROSS JOIN global_avg_hours gah
),
compensation_calc AS (
SELECT 
id AS employee_id,
hire_date,
salary,
DATEDIFF('2026-03-06', hire_date) / 365.0 AS work_years,
(DATEDIFF('2026-03-06', hire_date) / 365.0 + 1) * salary AS compensation_amount
FROM employees
),
compensation_score AS (
SELECT 
employee_id,
hire_date,
salary,
work_years,
compensation_amount,
CASE 
WHEN compensation_amount < 10 THEN 3
WHEN compensation_amount < 20 THEN 2
WHEN compensation_amount < 30 THEN 1
ELSE 0
END AS compensation_score
FROM compensation_calc
),
daily_walk_summary AS (
SELECT 
employee_id,
DATE(timestamp) AS access_date,
COUNT(*) AS total_daily_walks,
CASE WHEN COUNT(*) > 3 THEN 1 ELSE 0 END AS is_over_3_walks
FROM access_control
WHERE YEAR(timestamp) = 2025
AND (
(HOUR(timestamp) >= 9 AND HOUR(timestamp) < 12)
OR (HOUR(timestamp) >= 14 AND HOUR(timestamp) < 18)
)
GROUP BY employee_id, DATE(timestamp)
),
employee_walk_stats AS (
SELECT 
employee_id,
COUNT(DISTINCT access_date) AS walk_days,
SUM(is_over_3_walks) AS days_over_3_walks
FROM daily_walk_summary
GROUP BY employee_id
),
walking_score AS (
SELECT 
e.id AS employee_id,
COALESCE(ews.walk_days, 0) AS walk_days,
COALESCE(ews.days_over_3_walks, 0) AS days_over_3_walks,
CASE 
WHEN COALESCE(ews.walk_days, 0) > 70 AND COALESCE(ews.days_over_3_walks, 0) > 25 THEN 3
WHEN COALESCE(ews.walk_days, 0) > 60 THEN 1
ELSE 0
END AS walking_score
FROM employees e
LEFT JOIN employee_walk_stats ews ON e.id = ews.employee_id
),
flybook_after_launch AS (
SELECT 
employee_id,
daily_interaction,
daily_messages,
daily_documents
FROM flybook_stats
WHERE stat_date >= '2025-01-31'
),
avg_stats AS (
SELECT 
CASE WHEN COUNT(*) > 0 THEN AVG(daily_interaction) ELSE 0 END AS avg_interaction_count,
CASE WHEN COUNT(*) > 0 THEN AVG(daily_messages) ELSE 0 END AS avg_message_count,
CASE WHEN COUNT(*) > 0 THEN AVG(daily_documents) ELSE 0 END AS avg_daily_document_count
FROM flybook_after_launch
),
employee_flybook_stats AS (
SELECT 
employee_id,
AVG(daily_interaction) AS avg_interaction_count,
AVG(daily_messages) AS avg_message_count,
AVG(daily_documents) AS avg_daily_document_count
FROM flybook_after_launch
GROUP BY employee_id
),
employee_score_calc AS (
SELECT 
e.id AS employee_id,
COALESCE(efs.avg_interaction_count, 0) AS avg_interaction_count,
COALESCE(efs.avg_message_count, 0) AS avg_message_count,
COALESCE(efs.avg_daily_document_count, 0) AS avg_daily_document_count,
avs.avg_interaction_count AS global_avg_interaction_count,
avs.avg_message_count AS global_avg_message_count,
avs.avg_daily_document_count AS global_avg_daily_document_count
FROM employees e
CROSS JOIN avg_stats avs
LEFT JOIN employee_flybook_stats efs ON e.id = efs.employee_id
),
document_score AS (
SELECT 
employee_id,
avg_interaction_count,
avg_message_count,
avg_daily_document_count,
CASE 
WHEN avg_interaction_count < global_avg_interaction_count
 AND avg_message_count < global_avg_message_count
 AND avg_daily_document_count < global_avg_daily_document_count THEN 3
WHEN (
(avg_interaction_count < global_avg_interaction_count AND avg_message_count < global_avg_message_count)
OR (avg_interaction_count < global_avg_interaction_count AND avg_daily_document_count < global_avg_daily_document_count)
OR (avg_message_count < global_avg_message_count AND avg_daily_document_count < global_avg_daily_document_count)
) THEN 1
ELSE 0
END AS document_score
FROM employee_score_calc
)
SELECT 
e.id AS employee_id,
COALESCE(att.attendance_score, 0) + 
COALESCE(comp.compensation_score, 0) + 
COALESCE(walk.walking_score, 0) + 
COALESCE(doc.document_score, 0) AS score
FROM employees e
LEFT JOIN attendance_score att ON e.id = att.employee_id
LEFT JOIN compensation_score comp ON e.id = comp.employee_id
LEFT JOIN walking_score walk ON e.id = walk.employee_id
LEFT JOIN document_score doc ON e.id = doc.employee_id
;
2026-01-17 财源广进：开除摸鱼的员工  
WITH 
daily_attendance AS (
SELECT 
employee_id,
DATE(punch_time) AS check_date,
COUNT(*) AS punch_count,
MIN(punch_time) AS first_punch,
MAX(punch_time) AS last_punch
FROM attendance
WHERE YEAR(punch_time) = 2025
GROUP BY employee_id, DATE(punch_time)
HAVING punch_count > 1
),
employee_total_hours AS (
SELECT 
employee_id,
SUM(TIMESTAMPDIFF(MINUTE, first_punch, last_punch) / 60.0) AS total_hours
FROM daily_attendance
GROUP BY employee_id
),
all_employee_hours AS (
SELECT 
e.id AS employee_id,
COALESCE(eth.total_hours, 0) AS total_hours
FROM employees e
LEFT JOIN employee_total_hours eth ON e.id = eth.employee_id
),
avg_hours AS (
SELECT AVG(total_hours) AS avg_total_hours
FROM all_employee_hours
),
attendance_score AS (
SELECT 
aeh.employee_id,
aeh.total_hours,
avh.avg_total_hours,
CASE 
WHEN aeh.total_hours < avh.avg_total_hours THEN 1
ELSE 0
END AS attendance_score
FROM all_employee_hours aeh
CROSS JOIN avg_hours avh
),
compensation_calc AS (
SELECT 
id AS employee_id,
hire_date,
salary,
DATEDIFF('2026-03-06', hire_date) / 365.0 AS work_years,
(DATEDIFF('2026-03-06', hire_date) / 365.0 + 1) * salary AS compensation_amount
FROM employees
),
compensation_score AS (
SELECT 
employee_id,
hire_date,
salary,
work_years,
compensation_amount,
CASE 
WHEN compensation_amount < 10 THEN 3
WHEN compensation_amount < 20 THEN 2
WHEN compensation_amount < 30 THEN 1
ELSE 0
END AS compensation_score
FROM compensation_calc
),
daily_walk_summary AS (
SELECT 
employee_id,
DATE(timestamp) AS access_date,
COUNT(*) AS total_daily_walks,
CASE WHEN COUNT(*) > 3 THEN 1 ELSE 0 END AS is_over_3_walks
FROM access_control
WHERE YEAR(timestamp) = 2025
AND (
(HOUR(timestamp) >= 9 AND HOUR(timestamp) < 12)
OR (HOUR(timestamp) >= 14 AND HOUR(timestamp) < 18)
)
GROUP BY employee_id, DATE(timestamp)
),
employee_walk_stats AS (
SELECT 
employee_id,
COUNT(DISTINCT access_date) AS walk_days,
SUM(is_over_3_walks) AS days_over_3_walks
FROM daily_walk_summary
GROUP BY employee_id
),
walking_score AS (
SELECT 
e.id AS employee_id,
COALESCE(ews.walk_days, 0) AS walk_days,
COALESCE(ews.days_over_3_walks, 0) AS days_over_3_walks,
CASE 
WHEN COALESCE(ews.walk_days, 0) > 70 AND COALESCE(ews.days_over_3_walks, 0) > 25 THEN 3
WHEN COALESCE(ews.walk_days, 0) > 60 THEN 1
ELSE 0
END AS walking_score
FROM employees e
LEFT JOIN employee_walk_stats ews ON e.id = ews.employee_id
),
flybook_after_launch AS (
SELECT 
employee_id,
daily_interaction,
daily_messages,
daily_documents
FROM flybook_stats
WHERE stat_date >= '2025-01-31'
),
avg_stats AS (
SELECT 
CASE WHEN COUNT(*) > 0 THEN AVG(daily_interaction) ELSE 0 END AS avg_interaction_count,
CASE WHEN COUNT(*) > 0 THEN AVG(daily_messages) ELSE 0 END AS avg_message_count,
CASE WHEN COUNT(*) > 0 THEN AVG(daily_documents) ELSE 0 END AS avg_daily_document_count
FROM flybook_after_launch
),
employee_flybook_stats AS (
SELECT 
employee_id,
AVG(daily_interaction) AS avg_interaction_count,
AVG(daily_messages) AS avg_message_count,
AVG(daily_documents) AS avg_daily_document_count
FROM flybook_after_launch
GROUP BY employee_id
),
employee_score_calc AS (
SELECT 
e.id AS employee_id,
COALESCE(efs.avg_interaction_count, 0) AS avg_interaction_count,
COALESCE(efs.avg_message_count, 0) AS avg_message_count,
COALESCE(efs.avg_daily_document_count, 0) AS avg_daily_document_count,
avs.avg_interaction_count AS global_avg_interaction_count,
avs.avg_message_count AS global_avg_message_count,
avs.avg_daily_document_count AS global_avg_daily_document_count
FROM employees e
CROSS JOIN avg_stats avs
LEFT JOIN employee_flybook_stats efs ON e.id = efs.employee_id
),
document_score AS (
SELECT 
employee_id,
avg_interaction_count,
avg_message_count,
avg_daily_document_count,
CASE 
WHEN avg_interaction_count < global_avg_interaction_count
 AND avg_message_count < global_avg_message_count
 AND avg_daily_document_count < global_avg_daily_document_count THEN 3
WHEN (
(avg_interaction_count < global_avg_interaction_count AND avg_message_count < global_avg_message_count)
OR (avg_interaction_count < global_avg_interaction_count AND avg_daily_document_count < global_avg_daily_document_count)
OR (avg_message_count < global_avg_message_count AND avg_daily_document_count < global_avg_daily_document_count)
) THEN 1
ELSE 0
END AS document_score
FROM employee_score_calc
)
SELECT 
e.id AS employee_id,
COALESCE(att.attendance_score, 0) + 
COALESCE(comp.compensation_score, 0) + 
COALESCE(walk.walking_score, 0) + 
COALESCE(doc.document_score, 0) AS score
FROM employees e
LEFT JOIN attendance_score att ON e.id = att.employee_id
LEFT JOIN compensation_score comp ON e.id = comp.employee_id
LEFT JOIN walking_score walk ON e.id = walk.employee_id
LEFT JOIN document_score doc ON e.id = doc.employee_id
;
2026-01-17 财源广进：开除摸鱼的员工  
WITH 
daily_attendance AS (
SELECT 
employee_id,
DATE(punch_time) AS check_date,
COUNT(*) AS punch_count,
MIN(punch_time) AS first_punch,
MAX(punch_time) AS last_punch
FROM attendance
WHERE YEAR(punch_time) = 2025
GROUP BY employee_id, DATE(punch_time)
HAVING punch_count > 1
),
employee_total_hours AS (
SELECT 
employee_id,
SUM(TIMESTAMPDIFF(HOUR, first_punch, last_punch)) AS total_hours
FROM daily_attendance
GROUP BY employee_id
),
all_employee_hours AS (
SELECT 
e.id AS employee_id,
COALESCE(eth.total_hours, 0) AS total_hours
FROM employees e
LEFT JOIN employee_total_hours eth ON e.id = eth.employee_id
),
avg_hours AS (
SELECT AVG(total_hours) AS avg_total_hours
FROM all_employee_hours
),
attendance_score AS (
SELECT 
aeh.employee_id,
aeh.total_hours,
avh.avg_total_hours,
CASE 
WHEN aeh.total_hours < avh.avg_total_hours THEN 1
ELSE 0
END AS attendance_score
FROM all_employee_hours aeh
CROSS JOIN avg_hours avh
),
compensation_calc AS (
SELECT 
id AS employee_id,
hire_date,
salary,
DATEDIFF('2026-03-06', hire_date) / 365.0 AS work_years,
(DATEDIFF('2026-03-06', hire_date) / 365.0 + 1) * salary AS compensation_amount
FROM employees
),
compensation_score AS (
SELECT 
employee_id,
hire_date,
salary,
work_years,
compensation_amount,
CASE 
WHEN compensation_amount < 10 THEN 3
WHEN compensation_amount < 20 THEN 2
WHEN compensation_amount < 30 THEN 1
ELSE 0
END AS compensation_score
FROM compensation_calc
),
daily_walk_summary AS (
SELECT 
employee_id,
DATE(timestamp) AS access_date,
COUNT(*) AS total_daily_walks,
CASE WHEN COUNT(*) > 3 THEN 1 ELSE 0 END AS is_over_3_walks
FROM access_control
WHERE YEAR(timestamp) = 2025
AND (
(HOUR(timestamp) >= 9 AND HOUR(timestamp) < 12)
OR (HOUR(timestamp) >= 14 AND HOUR(timestamp) < 18)
)
GROUP BY employee_id, DATE(timestamp)
),
employee_walk_stats AS (
SELECT 
employee_id,
COUNT(DISTINCT access_date) AS walk_days,
SUM(is_over_3_walks) AS days_over_3_walks
FROM daily_walk_summary
GROUP BY employee_id
),
walking_score AS (
SELECT 
e.id AS employee_id,
COALESCE(ews.walk_days, 0) AS walk_days,
COALESCE(ews.days_over_3_walks, 0) AS days_over_3_walks,
CASE 
WHEN COALESCE(ews.walk_days, 0) > 70 AND COALESCE(ews.days_over_3_walks, 0) > 25 THEN 3
WHEN COALESCE(ews.walk_days, 0) > 60 THEN 1
ELSE 0
END AS walking_score
FROM employees e
LEFT JOIN employee_walk_stats ews ON e.id = ews.employee_id
),
flybook_after_launch AS (
SELECT 
employee_id,
daily_interaction,
daily_messages,
daily_documents
FROM flybook_stats
WHERE stat_date >= '2025-01-31'
),
avg_stats AS (
SELECT 
CASE WHEN COUNT(*) > 0 THEN AVG(daily_interaction) ELSE 0 END AS avg_interaction_count,
CASE WHEN COUNT(*) > 0 THEN AVG(daily_messages) ELSE 0 END AS avg_message_count,
CASE WHEN COUNT(*) > 0 THEN AVG(daily_documents) ELSE 0 END AS avg_daily_document_count
FROM flybook_after_launch
),
employee_flybook_stats AS (
SELECT 
employee_id,
AVG(daily_interaction) AS avg_interaction_count,
AVG(daily_messages) AS avg_message_count,
AVG(daily_documents) AS avg_daily_document_count
FROM flybook_after_launch
GROUP BY employee_id
),
employee_score_calc AS (
SELECT 
e.id AS employee_id,
COALESCE(efs.avg_interaction_count, 0) AS avg_interaction_count,
COALESCE(efs.avg_message_count, 0) AS avg_message_count,
COALESCE(efs.avg_daily_document_count, 0) AS avg_daily_document_count,
avs.avg_interaction_count AS global_avg_interaction_count,
avs.avg_message_count AS global_avg_message_count,
avs.avg_daily_document_count AS global_avg_daily_document_count
FROM employees e
CROSS JOIN avg_stats avs
LEFT JOIN employee_flybook_stats efs ON e.id = efs.employee_id
),
document_score AS (
SELECT 
employee_id,
avg_interaction_count,
avg_message_count,
avg_daily_document_count,
CASE 
WHEN avg_interaction_count < global_avg_interaction_count
 AND avg_message_count < global_avg_message_count
 AND avg_daily_document_count < global_avg_daily_document_count THEN 3
WHEN (
(avg_interaction_count < global_avg_interaction_count AND avg_message_count < global_avg_message_count)
OR (avg_interaction_count < global_avg_interaction_count AND avg_daily_document_count < global_avg_daily_document_count)
OR (avg_message_count < global_avg_message_count AND avg_daily_document_count < global_avg_daily_document_count)
) THEN 1
ELSE 0
END AS document_score
FROM employee_score_calc
)
SELECT 
e.id AS employee_id,
COALESCE(att.attendance_score, 0) + 
COALESCE(comp.compensation_score, 0) + 
COALESCE(walk.walking_score, 0) + 
COALESCE(doc.document_score, 0) AS score
FROM employees e
LEFT JOIN attendance_score att ON e.id = att.employee_id
LEFT JOIN compensation_score comp ON e.id = comp.employee_id
LEFT JOIN walking_score walk ON e.id = walk.employee_id
LEFT JOIN document_score doc ON e.id = doc.employee_id
;
2026-01-17 财源广进：开除摸鱼的员工  
WITH 
daily_attendance AS (
SELECT 
employee_id,
DATE(punch_time) AS check_date,
COUNT(*) AS punch_count,
MIN(punch_time) AS first_punch,
MAX(punch_time) AS last_punch
FROM attendance
WHERE YEAR(punch_time) = 2025
AND (HOUR(punch_time) >= 9 AND HOUR(punch_time) < 12 OR HOUR(punch_time) >= 14 AND HOUR(punch_time) < 18)
GROUP BY employee_id, DATE(punch_time)
HAVING punch_count > 1
),
employee_total_hours AS (
SELECT 
employee_id,
SUM(TIMESTAMPDIFF(HOUR, first_punch, last_punch)) AS total_hours
FROM daily_attendance
GROUP BY employee_id
),
all_employee_hours AS (
SELECT 
e.id AS employee_id,
COALESCE(eth.total_hours, 0) AS total_hours
FROM employees e
LEFT JOIN employee_total_hours eth ON e.id = eth.employee_id
),
avg_hours AS (
SELECT AVG(total_hours) AS avg_total_hours
FROM all_employee_hours
),
attendance_score AS (
SELECT 
aeh.employee_id,
aeh.total_hours,
avh.avg_total_hours,
CASE 
WHEN aeh.total_hours < avh.avg_total_hours THEN 1
ELSE 0
END AS attendance_score
FROM all_employee_hours aeh
CROSS JOIN avg_hours avh
),
compensation_calc AS (
SELECT 
id AS employee_id,
hire_date,
salary,
DATEDIFF('2026-03-06', hire_date) / 365.0 AS work_years,
(DATEDIFF('2026-03-06', hire_date) / 365.0 + 1) * salary AS compensation_amount
FROM employees
),
compensation_score AS (
SELECT 
employee_id,
hire_date,
salary,
work_years,
compensation_amount,
CASE 
WHEN compensation_amount < 10 THEN 3
WHEN compensation_amount < 20 THEN 2
WHEN compensation_amount < 30 THEN 1
ELSE 0
END AS compensation_score
FROM compensation_calc
),
daily_walk_summary AS (
SELECT 
employee_id,
DATE(timestamp) AS access_date,
COUNT(*) AS total_daily_walks,
CASE WHEN COUNT(*) > 3 THEN 1 ELSE 0 END AS is_over_3_walks
FROM access_control
WHERE YEAR(timestamp) = 2025
AND (
(HOUR(timestamp) >= 9 AND HOUR(timestamp) < 12)
OR (HOUR(timestamp) >= 14 AND HOUR(timestamp) < 18)
)
GROUP BY employee_id, DATE(timestamp)
),
employee_walk_stats AS (
SELECT 
employee_id,
COUNT(DISTINCT access_date) AS walk_days,
SUM(is_over_3_walks) AS days_over_3_walks
FROM daily_walk_summary
GROUP BY employee_id
),
walking_score AS (
SELECT 
e.id AS employee_id,
COALESCE(ews.walk_days, 0) AS walk_days,
COALESCE(ews.days_over_3_walks, 0) AS days_over_3_walks,
CASE 
WHEN COALESCE(ews.walk_days, 0) > 70 AND COALESCE(ews.days_over_3_walks, 0) > 25 THEN 3
WHEN COALESCE(ews.walk_days, 0) > 60 THEN 1
ELSE 0
END AS walking_score
FROM employees e
LEFT JOIN employee_walk_stats ews ON e.id = ews.employee_id
),
flybook_after_launch AS (
SELECT 
employee_id,
daily_interaction,
daily_messages,
daily_documents
FROM flybook_stats
WHERE stat_date >= '2025-01-31'
),
avg_stats AS (
SELECT 
CASE WHEN COUNT(*) > 0 THEN AVG(daily_interaction) ELSE 0 END AS avg_interaction_count,
CASE WHEN COUNT(*) > 0 THEN AVG(daily_messages) ELSE 0 END AS avg_message_count,
CASE WHEN COUNT(*) > 0 THEN AVG(daily_documents) ELSE 0 END AS avg_daily_document_count
FROM flybook_after_launch
),
employee_flybook_stats AS (
SELECT 
employee_id,
AVG(daily_interaction) AS avg_interaction_count,
AVG(daily_messages) AS avg_message_count,
AVG(daily_documents) AS avg_daily_document_count
FROM flybook_after_launch
GROUP BY employee_id
),
employee_score_calc AS (
SELECT 
e.id AS employee_id,
COALESCE(efs.avg_interaction_count, 0) AS avg_interaction_count,
COALESCE(efs.avg_message_count, 0) AS avg_message_count,
COALESCE(efs.avg_daily_document_count, 0) AS avg_daily_document_count,
avs.avg_interaction_count AS global_avg_interaction_count,
avs.avg_message_count AS global_avg_message_count,
avs.avg_daily_document_count AS global_avg_daily_document_count
FROM employees e
CROSS JOIN avg_stats avs
LEFT JOIN employee_flybook_stats efs ON e.id = efs.employee_id
),
document_score AS (
SELECT 
employee_id,
avg_interaction_count,
avg_message_count,
avg_daily_document_count,
CASE 
WHEN avg_interaction_count < global_avg_interaction_count
 AND avg_message_count < global_avg_message_count
 AND avg_daily_document_count < global_avg_daily_document_count THEN 3
WHEN (
(avg_interaction_count < global_avg_interaction_count AND avg_message_count < global_avg_message_count)
OR (avg_interaction_count < global_avg_interaction_count AND avg_daily_document_count < global_avg_daily_document_count)
OR (avg_message_count < global_avg_message_count AND avg_daily_document_count < global_avg_daily_document_count)
) THEN 1
ELSE 0
END AS document_score
FROM employee_score_calc
)
SELECT 
e.id AS employee_id,
COALESCE(att.attendance_score, 0) + 
COALESCE(comp.compensation_score, 0) + 
COALESCE(walk.walking_score, 0) + 
COALESCE(doc.document_score, 0) AS score
FROM employees e
LEFT JOIN attendance_score att ON e.id = att.employee_id
LEFT JOIN compensation_score comp ON e.id = comp.employee_id
LEFT JOIN walking_score walk ON e.id = walk.employee_id
LEFT JOIN document_score doc ON e.id = doc.employee_id
;
2026-01-17 财源广进：开除摸鱼的员工  
WITH 
daily_attendance AS (
SELECT 
employee_id,
DATE(punch_time) AS check_date,
COUNT(*) AS punch_count,
SUM(CASE WHEN HOUR(punch_time) >= 9 AND HOUR(punch_time) < 12 THEN 1 ELSE 0 END) AS has_morning,
SUM(CASE WHEN HOUR(punch_time) >= 14 AND HOUR(punch_time) < 18 THEN 1 ELSE 0 END) AS has_afternoon
FROM attendance
WHERE YEAR(punch_time) = 2025
AND (HOUR(punch_time) >= 9 AND HOUR(punch_time) < 12 OR HOUR(punch_time) >= 14 AND HOUR(punch_time) < 18)
GROUP BY employee_id, DATE(punch_time)
HAVING punch_count > 1
),
employee_total_hours AS (
SELECT 
employee_id,
SUM(CASE WHEN has_morning > 0 THEN 3 ELSE 0 END + CASE WHEN has_afternoon > 0 THEN 4 ELSE 0 END) AS total_hours
FROM daily_attendance
GROUP BY employee_id
),
all_employee_hours AS (
SELECT 
e.id AS employee_id,
COALESCE(eth.total_hours, 0) AS total_hours
FROM employees e
LEFT JOIN employee_total_hours eth ON e.id = eth.employee_id
),
avg_hours AS (
SELECT AVG(total_hours) AS avg_total_hours
FROM all_employee_hours
),
attendance_score AS (
SELECT 
aeh.employee_id,
aeh.total_hours,
avh.avg_total_hours,
CASE 
WHEN aeh.total_hours < avh.avg_total_hours THEN 1
ELSE 0
END AS attendance_score
FROM all_employee_hours aeh
CROSS JOIN avg_hours avh
),
compensation_calc AS (
SELECT 
id AS employee_id,
hire_date,
salary,
DATEDIFF('2026-03-06', hire_date) / 365.0 AS work_years,
(DATEDIFF('2026-03-06', hire_date) / 365.0 + 1) * salary AS compensation_amount
FROM employees
),
compensation_score AS (
SELECT 
employee_id,
hire_date,
salary,
work_years,
compensation_amount,
CASE 
WHEN compensation_amount < 10 THEN 3
WHEN compensation_amount < 20 THEN 2
WHEN compensation_amount < 30 THEN 1
ELSE 0
END AS compensation_score
FROM compensation_calc
),
daily_walk_summary AS (
SELECT 
employee_id,
DATE(timestamp) AS access_date,
COUNT(*) AS total_daily_walks,
CASE WHEN COUNT(*) > 3 THEN 1 ELSE 0 END AS is_over_3_walks
FROM access_control
WHERE YEAR(timestamp) = 2025
AND (
(HOUR(timestamp) >= 9 AND HOUR(timestamp) < 12)
OR (HOUR(timestamp) >= 14 AND HOUR(timestamp) < 18)
)
GROUP BY employee_id, DATE(timestamp)
),
employee_walk_stats AS (
SELECT 
employee_id,
COUNT(DISTINCT access_date) AS walk_days,
SUM(is_over_3_walks) AS days_over_3_walks
FROM daily_walk_summary
GROUP BY employee_id
),
walking_score AS (
SELECT 
e.id AS employee_id,
COALESCE(ews.walk_days, 0) AS walk_days,
COALESCE(ews.days_over_3_walks, 0) AS days_over_3_walks,
CASE 
WHEN COALESCE(ews.walk_days, 0) > 70 AND COALESCE(ews.days_over_3_walks, 0) > 25 THEN 3
WHEN COALESCE(ews.walk_days, 0) > 60 THEN 1
ELSE 0
END AS walking_score
FROM employees e
LEFT JOIN employee_walk_stats ews ON e.id = ews.employee_id
),
flybook_after_launch AS (
SELECT 
employee_id,
daily_interaction,
daily_messages,
daily_documents
FROM flybook_stats
WHERE stat_date >= '2025-01-31'
),
avg_stats AS (
SELECT 
CASE WHEN COUNT(*) > 0 THEN AVG(daily_interaction) ELSE 0 END AS avg_interaction_count,
CASE WHEN COUNT(*) > 0 THEN AVG(daily_messages) ELSE 0 END AS avg_message_count,
CASE WHEN COUNT(*) > 0 THEN AVG(daily_documents) ELSE 0 END AS avg_daily_document_count
FROM flybook_after_launch
),
employee_flybook_stats AS (
SELECT 
employee_id,
AVG(daily_interaction) AS avg_interaction_count,
AVG(daily_messages) AS avg_message_count,
AVG(daily_documents) AS avg_daily_document_count
FROM flybook_after_launch
GROUP BY employee_id
),
employee_score_calc AS (
SELECT 
e.id AS employee_id,
COALESCE(efs.avg_interaction_count, 0) AS avg_interaction_count,
COALESCE(efs.avg_message_count, 0) AS avg_message_count,
COALESCE(efs.avg_daily_document_count, 0) AS avg_daily_document_count,
avs.avg_interaction_count AS global_avg_interaction_count,
avs.avg_message_count AS global_avg_message_count,
avs.avg_daily_document_count AS global_avg_daily_document_count
FROM employees e
CROSS JOIN avg_stats avs
LEFT JOIN employee_flybook_stats efs ON e.id = efs.employee_id
),
document_score AS (
SELECT 
employee_id,
avg_interaction_count,
avg_message_count,
avg_daily_document_count,
CASE 
WHEN avg_interaction_count < global_avg_interaction_count
 AND avg_message_count < global_avg_message_count
 AND avg_daily_document_count < global_avg_daily_document_count THEN 3
WHEN (
(avg_interaction_count < global_avg_interaction_count AND avg_message_count < global_avg_message_count)
OR (avg_interaction_count < global_avg_interaction_count AND avg_daily_document_count < global_avg_daily_document_count)
OR (avg_message_count < global_avg_message_count AND avg_daily_document_count < global_avg_daily_document_count)
) THEN 1
ELSE 0
END AS document_score
FROM employee_score_calc
)
SELECT 
e.id AS employee_id,
COALESCE(att.attendance_score, 0) + 
COALESCE(comp.compensation_score, 0) + 
COALESCE(walk.walking_score, 0) + 
COALESCE(doc.document_score, 0) AS score
FROM employees e
LEFT JOIN attendance_score att ON e.id = att.employee_id
LEFT JOIN compensation_score comp ON e.id = comp.employee_id
LEFT JOIN walking_score walk ON e.id = walk.employee_id
LEFT JOIN document_score doc ON e.id = doc.employee_id
;
2026-01-17 财源广进：开除摸鱼的员工  
WITH 
daily_attendance AS (
SELECT 
employee_id,
DATE(punch_time) AS check_date,
SUM(CASE WHEN HOUR(punch_time) >= 9 AND HOUR(punch_time) < 12 THEN 1 ELSE 0 END) AS has_morning,
SUM(CASE WHEN HOUR(punch_time) >= 14 AND HOUR(punch_time) < 18 THEN 1 ELSE 0 END) AS has_afternoon
FROM attendance
WHERE YEAR(punch_time) = 2025
AND (HOUR(punch_time) >= 9 AND HOUR(punch_time) < 12 OR HOUR(punch_time) >= 14 AND HOUR(punch_time) < 18)
GROUP BY employee_id, DATE(punch_time)
HAVING has_morning > 0 OR has_afternoon > 0
),
employee_total_hours AS (
SELECT 
employee_id,
SUM(CASE WHEN has_morning > 0 THEN 3 ELSE 0 END + CASE WHEN has_afternoon > 0 THEN 4 ELSE 0 END) AS total_hours
FROM daily_attendance
GROUP BY employee_id
),
all_employee_hours AS (
SELECT 
e.id AS employee_id,
COALESCE(eth.total_hours, 0) AS total_hours
FROM employees e
LEFT JOIN employee_total_hours eth ON e.id = eth.employee_id
),
avg_hours AS (
SELECT AVG(total_hours) AS avg_total_hours
FROM all_employee_hours
),
attendance_score AS (
SELECT 
aeh.employee_id,
aeh.total_hours,
avh.avg_total_hours,
CASE 
WHEN aeh.total_hours < avh.avg_total_hours THEN 1
ELSE 0
END AS attendance_score
FROM all_employee_hours aeh
CROSS JOIN avg_hours avh
),
compensation_calc AS (
SELECT 
id AS employee_id,
hire_date,
salary,
DATEDIFF('2026-03-06', hire_date) / 365.0 AS work_years,
(DATEDIFF('2026-03-06', hire_date) / 365.0 + 1) * salary AS compensation_amount
FROM employees
),
compensation_score AS (
SELECT 
employee_id,
hire_date,
salary,
work_years,
compensation_amount,
CASE 
WHEN compensation_amount < 10 THEN 3
WHEN compensation_amount < 20 THEN 2
WHEN compensation_amount < 30 THEN 1
ELSE 0
END AS compensation_score
FROM compensation_calc
),
daily_walk_summary AS (
SELECT 
employee_id,
DATE(timestamp) AS access_date,
COUNT(*) AS total_daily_walks,
CASE WHEN COUNT(*) > 3 THEN 1 ELSE 0 END AS is_over_3_walks
FROM access_control
WHERE YEAR(timestamp) = 2025
AND (
(HOUR(timestamp) >= 9 AND HOUR(timestamp) < 12)
OR (HOUR(timestamp) >= 14 AND HOUR(timestamp) < 18)
)
GROUP BY employee_id, DATE(timestamp)
),
employee_walk_stats AS (
SELECT 
employee_id,
COUNT(DISTINCT access_date) AS walk_days,
SUM(is_over_3_walks) AS days_over_3_walks
FROM daily_walk_summary
GROUP BY employee_id
),
walking_score AS (
SELECT 
e.id AS employee_id,
COALESCE(ews.walk_days, 0) AS walk_days,
COALESCE(ews.days_over_3_walks, 0) AS days_over_3_walks,
CASE 
WHEN COALESCE(ews.walk_days, 0) > 70 AND COALESCE(ews.days_over_3_walks, 0) > 25 THEN 3
WHEN COALESCE(ews.walk_days, 0) > 60 THEN 1
ELSE 0
END AS walking_score
FROM employees e
LEFT JOIN employee_walk_stats ews ON e.id = ews.employee_id
),
flybook_after_launch AS (
SELECT 
employee_id,
daily_interaction,
daily_messages,
daily_documents
FROM flybook_stats
WHERE stat_date >= '2025-01-31'
),
avg_stats AS (
SELECT 
CASE WHEN COUNT(*) > 0 THEN AVG(daily_interaction) ELSE 0 END AS avg_interaction_count,
CASE WHEN COUNT(*) > 0 THEN AVG(daily_messages) ELSE 0 END AS avg_message_count,
CASE WHEN COUNT(*) > 0 THEN AVG(daily_documents) ELSE 0 END AS avg_daily_document_count
FROM flybook_after_launch
),
employee_flybook_stats AS (
SELECT 
employee_id,
AVG(daily_interaction) AS avg_interaction_count,
AVG(daily_messages) AS avg_message_count,
AVG(daily_documents) AS avg_daily_document_count
FROM flybook_after_launch
GROUP BY employee_id
),
employee_score_calc AS (
SELECT 
e.id AS employee_id,
COALESCE(efs.avg_interaction_count, 0) AS avg_interaction_count,
COALESCE(efs.avg_message_count, 0) AS avg_message_count,
COALESCE(efs.avg_daily_document_count, 0) AS avg_daily_document_count,
avs.avg_interaction_count AS global_avg_interaction_count,
avs.avg_message_count AS global_avg_message_count,
avs.avg_daily_document_count AS global_avg_daily_document_count
FROM employees e
CROSS JOIN avg_stats avs
LEFT JOIN employee_flybook_stats efs ON e.id = efs.employee_id
),
document_score AS (
SELECT 
employee_id,
avg_interaction_count,
avg_message_count,
avg_daily_document_count,
CASE 
WHEN avg_interaction_count < global_avg_interaction_count
 AND avg_message_count < global_avg_message_count
 AND avg_daily_document_count < global_avg_daily_document_count THEN 3
WHEN (
(avg_interaction_count < global_avg_interaction_count AND avg_message_count < global_avg_message_count)
OR (avg_interaction_count < global_avg_interaction_count AND avg_daily_document_count < global_avg_daily_document_count)
OR (avg_message_count < global_avg_message_count AND avg_daily_document_count < global_avg_daily_document_count)
) THEN 1
ELSE 0
END AS document_score
FROM employee_score_calc
)
SELECT 
e.id AS employee_id,
COALESCE(att.attendance_score, 0) + 
COALESCE(comp.compensation_score, 0) + 
COALESCE(walk.walking_score, 0) + 
COALESCE(doc.document_score, 0) AS score
FROM employees e
LEFT JOIN attendance_score att ON e.id = att.employee_id
LEFT JOIN compensation_score comp ON e.id = comp.employee_id
LEFT JOIN walking_score walk ON e.id = walk.employee_id
LEFT JOIN document_score doc ON e.id = doc.employee_id
;
2026-01-17 财源广进：开除摸鱼的员工  
WITH 
daily_attendance AS (
SELECT 
employee_id,
DATE(punch_time) AS check_date,
SUM(CASE WHEN HOUR(punch_time) >= 9 AND HOUR(punch_time) < 12 THEN 1 ELSE 0 END) AS has_morning,
SUM(CASE WHEN HOUR(punch_time) >= 14 AND HOUR(punch_time) < 18 THEN 1 ELSE 0 END) AS has_afternoon
FROM attendance
WHERE YEAR(punch_time) = 2025
AND (HOUR(punch_time) >= 9 AND HOUR(punch_time) < 12 OR HOUR(punch_time) >= 14 AND HOUR(punch_time) < 18)
GROUP BY employee_id, DATE(punch_time)
HAVING has_morning > 0 OR has_afternoon > 0
),
employee_total_hours AS (
SELECT 
employee_id,
SUM(CASE WHEN has_morning > 0 THEN 3 ELSE 0 END + CASE WHEN has_afternoon > 0 THEN 4 ELSE 0 END) AS total_hours
FROM daily_attendance
GROUP BY employee_id
),
avg_hours AS (
SELECT AVG(total_hours) AS avg_total_hours
FROM employee_total_hours
),
attendance_score AS (
SELECT 
e.id AS employee_id,
COALESCE(eth.total_hours, 0) AS total_hours,
avh.avg_total_hours,
CASE 
WHEN COALESCE(eth.total_hours, 0) < avh.avg_total_hours THEN 1
ELSE 0
END AS attendance_score
FROM employees e
CROSS JOIN avg_hours avh
LEFT JOIN employee_total_hours eth ON e.id = eth.employee_id
),
compensation_calc AS (
SELECT 
id AS employee_id,
hire_date,
salary,
DATEDIFF('2026-03-06', hire_date) / 365.0 AS work_years,
(DATEDIFF('2026-03-06', hire_date) / 365.0 + 1) * salary AS compensation_amount
FROM employees
),
compensation_score AS (
SELECT 
employee_id,
hire_date,
salary,
work_years,
compensation_amount,
CASE 
WHEN compensation_amount < 10 THEN 3
WHEN compensation_amount < 20 THEN 2
WHEN compensation_amount < 30 THEN 1
ELSE 0
END AS compensation_score
FROM compensation_calc
),
daily_walk_summary AS (
SELECT 
employee_id,
DATE(timestamp) AS access_date,
COUNT(*) AS total_daily_walks,
CASE WHEN COUNT(*) > 3 THEN 1 ELSE 0 END AS is_over_3_walks
FROM access_control
WHERE YEAR(timestamp) = 2025
AND (
(HOUR(timestamp) >= 9 AND HOUR(timestamp) < 12)
OR (HOUR(timestamp) >= 14 AND HOUR(timestamp) < 18)
)
GROUP BY employee_id, DATE(timestamp)
),
employee_walk_stats AS (
SELECT 
employee_id,
COUNT(DISTINCT access_date) AS walk_days,
SUM(is_over_3_walks) AS days_over_3_walks
FROM daily_walk_summary
GROUP BY employee_id
),
walking_score AS (
SELECT 
e.id AS employee_id,
COALESCE(ews.walk_days, 0) AS walk_days,
COALESCE(ews.days_over_3_walks, 0) AS days_over_3_walks,
CASE 
WHEN COALESCE(ews.walk_days, 0) > 70 AND COALESCE(ews.days_over_3_walks, 0) > 25 THEN 3
WHEN COALESCE(ews.walk_days, 0) > 60 THEN 1
ELSE 0
END AS walking_score
FROM employees e
LEFT JOIN employee_walk_stats ews ON e.id = ews.employee_id
),
flybook_after_launch AS (
SELECT 
employee_id,
daily_interaction,
daily_messages,
daily_documents
FROM flybook_stats
WHERE stat_date >= '2025-01-31'
),
avg_stats AS (
SELECT 
AVG(daily_interaction) AS avg_interaction_count,
AVG(daily_messages) AS avg_message_count,
AVG(daily_documents) AS avg_daily_document_count
FROM flybook_after_launch
),
employee_flybook_stats AS (
SELECT 
employee_id,
AVG(daily_interaction) AS avg_interaction_count,
AVG(daily_messages) AS avg_message_count,
AVG(daily_documents) AS avg_daily_document_count
FROM flybook_after_launch
GROUP BY employee_id
),
employee_score_calc AS (
SELECT 
e.id AS employee_id,
COALESCE(efs.avg_interaction_count, 0) AS avg_interaction_count,
COALESCE(efs.avg_message_count, 0) AS avg_message_count,
COALESCE(efs.avg_daily_document_count, 0) AS avg_daily_document_count,
avs.avg_interaction_count AS global_avg_interaction_count,
avs.avg_message_count AS global_avg_message_count,
avs.avg_daily_document_count AS global_avg_daily_document_count
FROM employees e
CROSS JOIN avg_stats avs
LEFT JOIN employee_flybook_stats efs ON e.id = efs.employee_id
),
document_score AS (
SELECT 
employee_id,
avg_interaction_count,
avg_message_count,
avg_daily_document_count,
CASE 
WHEN avg_interaction_count < global_avg_interaction_count
 AND avg_message_count < global_avg_message_count
 AND avg_daily_document_count < global_avg_daily_document_count THEN 3
WHEN (
(avg_interaction_count < global_avg_interaction_count AND avg_message_count < global_avg_message_count)
OR (avg_interaction_count < global_avg_interaction_count AND avg_daily_document_count < global_avg_daily_document_count)
OR (avg_message_count < global_avg_message_count AND avg_daily_document_count < global_avg_daily_document_count)
) THEN 1
ELSE 0
END AS document_score
FROM employee_score_calc
)
SELECT 
e.id AS employee_id,
COALESCE(att.attendance_score, 0) + 
COALESCE(comp.compensation_score, 0) + 
COALESCE(walk.walking_score, 0) + 
COALESCE(doc.document_score, 0) AS score
FROM employees e
LEFT JOIN attendance_score att ON e.id = att.employee_id
LEFT JOIN compensation_score comp ON e.id = comp.employee_id
LEFT JOIN walking_score walk ON e.id = walk.employee_id
LEFT JOIN document_score doc ON e.id = doc.employee_id
;