排名

用户解题统计

过去一年提交了

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

收藏

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

评论笔记

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

提交记录

提交日期 题目名称 提交代码
2026-01-19 财源广进：开除摸鱼的员工  
WITH 
all_employees AS (
SELECT id as employee_id FROM employees
),
attendance_daily AS (
SELECT 
employee_id,
DATE(punch_time) as work_date,
MIN(punch_time) as first_clock,
MAX(punch_time) as last_clock
FROM attendance
WHERE YEAR(punch_time) = 2025
GROUP BY employee_id, DATE(punch_time)
),
attendance_total AS (
SELECT 
employee_id,
SUM(
TIMESTAMPDIFF(MINUTE, first_clock, last_clock) / 60.0 -
CASE 
WHEN HOUR(first_clock) < 12 AND HOUR(last_clock) >= 14 THEN 2
ELSE 0
END
) as total_hours
FROM attendance_daily
WHERE first_clock != last_clock
GROUP BY employee_id
),
avg_hours AS (
SELECT AVG(total_hours) as avg_val FROM attendance_total
),
attendance_score AS (
SELECT 
ae.employee_id,
CASE 
WHEN at.total_hours IS NULL THEN 1
WHEN at.total_hours < ah.avg_val THEN 1
ELSE 0 
END as score
FROM all_employees ae
LEFT JOIN attendance_total at ON ae.employee_id = at.employee_id
CROSS JOIN avg_hours ah
WHERE CASE 
WHEN at.total_hours IS NULL THEN 1
WHEN at.total_hours < ah.avg_val THEN 1
ELSE 0 
END > 0
),
compensation_score AS (
SELECT 
id as employee_id,
CASE 
WHEN (TIMESTAMPDIFF(YEAR, hire_date, '2026-03-06') + 1) * salary < 10 THEN 3
WHEN (TIMESTAMPDIFF(YEAR, hire_date, '2026-03-06') + 1) * salary < 20 THEN 2
WHEN (TIMESTAMPDIFF(YEAR, hire_date, '2026-03-06') + 1) * salary < 30 THEN 1
ELSE 0
END as score
FROM employees
WHERE CASE 
WHEN (TIMESTAMPDIFF(YEAR, hire_date, '2026-03-06') + 1) * salary < 10 THEN 3
WHEN (TIMESTAMPDIFF(YEAR, hire_date, '2026-03-06') + 1) * salary < 20 THEN 2
WHEN (TIMESTAMPDIFF(YEAR, hire_date, '2026-03-06') + 1) * salary < 30 THEN 1
ELSE 0
END > 0
),
walk_daily AS (
SELECT 
employee_id,
DATE(timestamp) as walk_date,
COUNT(*) as daily_count
FROM access_control
WHERE YEAR(timestamp) = 2025
AND (HOUR(timestamp) < 12 OR HOUR(timestamp) >= 14)
GROUP BY employee_id, DATE(timestamp)
),
walk_stats AS (
SELECT 
employee_id,
COUNT(*) as total_days,
SUM(CASE WHEN daily_count > 3 THEN 1 ELSE 0 END) as high_freq_days
FROM walk_daily
GROUP BY employee_id
),
walk_score AS (
SELECT 
employee_id,
CASE 
WHEN total_days > 70 AND high_freq_days > 25 THEN 3
WHEN total_days > 60 THEN 1
ELSE 0
END as score
FROM walk_stats
WHERE CASE 
WHEN total_days > 70 AND high_freq_days > 25 THEN 3
WHEN total_days > 60 THEN 1
ELSE 0
END > 0
),
emp_flybook_avg AS (
SELECT 
employee_id,
AVG(daily_interaction) as avg_interaction,
AVG(daily_messages) as avg_message,
AVG(daily_documents) as avg_doc
FROM flybook_stats
WHERE stat_date >= 20250131
GROUP BY employee_id
),
company_flybook_avg AS (
SELECT 
AVG(daily_interaction) as avg_interaction,
AVG(daily_messages) as avg_message,
AVG(daily_documents) as avg_doc
FROM flybook_stats
WHERE stat_date >= 20250131
),
flybook_score AS (
SELECT 
ae.employee_id,
CASE 
WHEN emp.employee_id IS NULL THEN 3
WHEN emp.avg_interaction < comp.avg_interaction 
 AND emp.avg_message < comp.avg_message 
 AND emp.avg_doc < comp.avg_doc THEN 3
WHEN (CASE WHEN emp.avg_interaction < comp.avg_interaction THEN 1 ELSE 0 END +
CASE WHEN emp.avg_message < comp.avg_message THEN 1 ELSE 0 END +
CASE WHEN emp.avg_doc < comp.avg_doc THEN 1 ELSE 0 END) = 2 THEN 1
ELSE 0
END as score
FROM all_employees ae
LEFT JOIN emp_flybook_avg emp ON ae.employee_id = emp.employee_id
CROSS JOIN company_flybook_avg comp
WHERE CASE 
WHEN emp.employee_id IS NULL THEN 3
WHEN emp.avg_interaction < comp.avg_interaction 
 AND emp.avg_message < comp.avg_message 
 AND emp.avg_doc < comp.avg_doc THEN 3
WHEN (CASE WHEN emp.avg_interaction < comp.avg_interaction THEN 1 ELSE 0 END +
CASE WHEN emp.avg_message < comp.avg_message THEN 1 ELSE 0 END +
CASE WHEN emp.avg_doc < comp.avg_doc THEN 1 ELSE 0 END) = 2 THEN 1
ELSE 0
END > 0
)
SELECT 
e.id as employee_id,
COALESCE(a.score, 0) + COALESCE(c.score, 0) + 
COALESCE(w.score, 0) + COALESCE(f.score, 0) as score
FROM employees e
LEFT JOIN attendance_score a ON e.id = a.employee_id
LEFT JOIN compensation_score c ON e.id = c.employee_id
LEFT JOIN walk_score w ON e.id = w.employee_id
LEFT JOIN flybook_score f ON e.id = f.employee_id;
2026-01-19 财源广进：开除摸鱼的员工  
WITH 
attendance_daily AS (
SELECT 
employee_id,
DATE(punch_time) as work_date,
MIN(punch_time) as first_clock,
MAX(punch_time) as last_clock
FROM attendance
WHERE YEAR(punch_time) = 2025
GROUP BY employee_id, DATE(punch_time)
),
attendance_total AS (
SELECT 
employee_id,
SUM(
TIMESTAMPDIFF(MINUTE, first_clock, last_clock) / 60.0 -
CASE 
WHEN HOUR(first_clock) < 12 AND HOUR(last_clock) >= 14 THEN 2
ELSE 0
END
) as total_hours
FROM attendance_daily
WHERE first_clock != last_clock
GROUP BY employee_id
),
attendance_score AS (
SELECT 
employee_id,
CASE 
WHEN total_hours < (SELECT AVG(total_hours) FROM attendance_total) 
THEN 1 
ELSE 0 
END as score
FROM attendance_total
),
compensation_score AS (
SELECT 
id as employee_id,
CASE 
WHEN (TIMESTAMPDIFF(YEAR, hire_date, '2026-03-06') + 1) * salary < 10 THEN 3
WHEN (TIMESTAMPDIFF(YEAR, hire_date, '2026-03-06') + 1) * salary < 20 THEN 2
WHEN (TIMESTAMPDIFF(YEAR, hire_date, '2026-03-06') + 1) * salary < 30 THEN 1
ELSE 0
END as score
FROM employees
),
walk_daily AS (
SELECT 
employee_id,
DATE(timestamp) as walk_date,
COUNT(*) as daily_count
FROM access_control
WHERE YEAR(timestamp) = 2025
AND (HOUR(timestamp) < 12 OR HOUR(timestamp) >= 14)
GROUP BY employee_id, DATE(timestamp)
),
walk_stats AS (
SELECT 
employee_id,
COUNT(*) as total_days,
SUM(CASE WHEN daily_count > 3 THEN 1 ELSE 0 END) as high_freq_days
FROM walk_daily
GROUP BY employee_id
),
walk_score AS (
SELECT 
employee_id,
CASE 
WHEN total_days > 70 AND high_freq_days > 25 THEN 3
WHEN total_days > 60 THEN 1
ELSE 0
END as score
FROM walk_stats
),
emp_flybook_avg AS (
SELECT 
employee_id,
AVG(daily_interaction) as avg_interaction,
AVG(daily_messages) as avg_message,
AVG(daily_documents) as avg_doc
FROM flybook_stats
WHERE stat_date >= 20250131
GROUP BY employee_id
),
company_flybook_avg AS (
SELECT 
AVG(daily_interaction) as avg_interaction,
AVG(daily_messages) as avg_message,
AVG(daily_documents) as avg_doc
FROM flybook_stats
WHERE stat_date >= 20250131
),
flybook_score AS (
SELECT 
emp.employee_id,
CASE 
WHEN emp.avg_interaction < comp.avg_interaction 
 AND emp.avg_message < comp.avg_message 
 AND emp.avg_doc < comp.avg_doc THEN 3
WHEN (CASE WHEN emp.avg_interaction < comp.avg_interaction THEN 1 ELSE 0 END +
CASE WHEN emp.avg_message < comp.avg_message THEN 1 ELSE 0 END +
CASE WHEN emp.avg_doc < comp.avg_doc THEN 1 ELSE 0 END) = 2 THEN 1
ELSE 0
END as score
FROM emp_flybook_avg emp
CROSS JOIN company_flybook_avg comp
)
SELECT 
e.id as employee_id,
COALESCE(a.score, 1) + COALESCE(c.score, 0) + 
COALESCE(w.score, 0) + COALESCE(f.score, 3) as score
FROM employees e
LEFT JOIN attendance_score a ON e.id = a.employee_id
LEFT JOIN compensation_score c ON e.id = c.employee_id
LEFT JOIN walk_score w ON e.id = w.employee_id
LEFT JOIN flybook_score f ON e.id = f.employee_id;
2026-01-19 财源广进：开除摸鱼的员工  
WITH 
attendance_daily AS (
SELECT 
employee_id,
DATE(punch_time) as work_date,
MIN(punch_time) as first_clock,
MAX(punch_time) as last_clock
FROM attendance
WHERE YEAR(punch_time) = 2025
GROUP BY employee_id, DATE(punch_time)
),
attendance_total AS (
SELECT 
employee_id,
SUM(
TIMESTAMPDIFF(MINUTE, first_clock, last_clock) / 60.0 -
CASE 
WHEN HOUR(first_clock) < 12 AND HOUR(last_clock) >= 14 THEN 2
ELSE 0
END
) as total_hours
FROM attendance_daily
WHERE first_clock != last_clock
GROUP BY employee_id
),
attendance_score AS (
SELECT 
employee_id,
CASE 
WHEN total_hours < (SELECT AVG(total_hours) FROM attendance_total) 
THEN 1 
ELSE 0 
END as score
FROM attendance_total
),
compensation_score AS (
SELECT 
id as employee_id,
CASE 
WHEN (TIMESTAMPDIFF(YEAR, hire_date, '2026-03-06') + 1) * salary < 10 THEN 3
WHEN (TIMESTAMPDIFF(YEAR, hire_date, '2026-03-06') + 1) * salary < 20 THEN 2
WHEN (TIMESTAMPDIFF(YEAR, hire_date, '2026-03-06') + 1) * salary < 30 THEN 1
ELSE 0
END as score
FROM employees
),
walk_daily AS (
SELECT 
employee_id,
DATE(timestamp) as walk_date,
COUNT(*) as daily_count
FROM access_control
WHERE YEAR(timestamp) = 2025
AND (HOUR(timestamp) < 12 OR HOUR(timestamp) >= 14)
GROUP BY employee_id, DATE(timestamp)
),
walk_stats AS (
SELECT 
employee_id,
COUNT(*) as total_days,
SUM(CASE WHEN daily_count > 3 THEN 1 ELSE 0 END) as high_freq_days
FROM walk_daily
GROUP BY employee_id
),
walk_score AS (
SELECT 
employee_id,
CASE 
WHEN total_days > 70 AND high_freq_days > 25 THEN 3
WHEN total_days > 60 THEN 1
ELSE 0
END as score
FROM walk_stats
),
emp_flybook_avg AS (
SELECT 
employee_id,
AVG(daily_interaction) as avg_interaction,
AVG(daily_messages) as avg_message,
AVG(daily_documents) as avg_doc
FROM flybook_stats
WHERE stat_date >= '2025-01-31'
GROUP BY employee_id
),
company_flybook_avg AS (
SELECT 
AVG(daily_interaction) as avg_interaction,
AVG(daily_messages) as avg_message,
AVG(daily_documents) as avg_doc
FROM flybook_stats
WHERE stat_date >= '2025-01-31'
),
flybook_score AS (
SELECT 
emp.employee_id,
CASE 
WHEN emp.avg_interaction < comp.avg_interaction 
 AND emp.avg_message < comp.avg_message 
 AND emp.avg_doc < comp.avg_doc THEN 3
WHEN (CASE WHEN emp.avg_interaction < comp.avg_interaction THEN 1 ELSE 0 END +
CASE WHEN emp.avg_message < comp.avg_message THEN 1 ELSE 0 END +
CASE WHEN emp.avg_doc < comp.avg_doc THEN 1 ELSE 0 END) = 2 THEN 1
ELSE 0
END as score
FROM emp_flybook_avg emp
CROSS JOIN company_flybook_avg comp
)
SELECT 
e.id as employee_id,
COALESCE(a.score, 0) + COALESCE(c.score, 0) + 
COALESCE(w.score, 0) + COALESCE(f.score, 0) as score
FROM employees e
LEFT JOIN attendance_score a ON e.id = a.employee_id
LEFT JOIN compensation_score c ON e.id = c.employee_id
LEFT JOIN walk_score w ON e.id = w.employee_id
LEFT JOIN flybook_score f ON e.id = f.employee_id
ORDER BY score DESC, e.id;