排名

用户解题统计

过去一年提交了

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

收藏

收藏日期 题目名称 解决状态
2025-01-09 窗口函数（2）所有前一和每类前一，继续搞懂排序窗口函数  已解决

评论笔记

评论日期 题目名称 评论内容 站长评论
2025-02-19 抖音面试真题（4）T+1月留存  
答案应该要包括12月
 
啥也没说
2025-01-09 一线城市历年平均气温  
我知道我的问题出现在哪里了。
我一直以为 format( col ,2 ) 和 cast( col as decimal(4,2 ) ) 的结果是完全一样的，其实不对。
问题出在 2013 年 shenzhen 原本的值是 23.125 分别用这两个公式一个 是 23.12 一个是 23.13.后面探究了一下

SELECT cast('23.125' as decimal(4,2 )); -- 23.13
SELECT cast(23.125 as decimal(4,2 ));  -- 23.13
SELECT  format('23.125',2); -- 23.12
SELECT  format(23.125,2); -- 23.13

我原本的脚本在传递给 FORMAT 函数的是一个字符串而不是数字。MySQL 首先需要将这个字符串转换成一个数值类型来处理这种隐式转换在某些环境或者并不可靠。

而且我发现一个 有意思的事情 format('23.125',2)：
当倒数第2位是奇数 比如 23.115，23.135，23.155 ... 结果会四舍五入 即 23.12，23.14，23.16
当倒数第2位是偶数 比如 23.125，23.145，23.165 ... 结果不会四舍五入 也是 23.12，23.14，23.16

估计是因为隐式转换的原因，具体的我没有搞明白，希望有看到这里的大佬可以解惑一下！
 
你的研究很仔细哈哈哈。舍入目的还是建议使用round，mysql一直有点疯，不太稳定。
2025-01-09 一线城市历年平均气温  
输出的是和输出示例是一样的呀，问题在哪？
select 
	year(dt) as Y
	, format(avg(REGEXP_SUBSTR(if(city = 'beijing', tmp_h, null), '-?[0-9]+')), 2)as beijing
	, format(avg(REGEXP_SUBSTR(if(city = 'shanghai', tmp_h, null), '-?[0-9]+')), 2)as shanghai
	, format(avg(REGEXP_SUBSTR(if(city = 'shenzhen', tmp_h, null), '-?[0-9]+')), 2)as shenzhen
	, format(avg(REGEXP_SUBSTR(if(city = 'guangzhou', tmp_h, null), '-?[0-9]+')), 2)as guangzhou
from weather_rcd_china
where 
	year(dt) between 2011 and 2022
	and city in ('beijing','shanghai','shenzhen','guangzhou')
group by 1
order by 1
 
啥也没说
2025-01-09 城市平均最高气温  
ROUND() 函数本身不会保证总是显示两位小数
可以使用 FORMAT() 函数或 DECIMAL 数据类型来控制输出格式
 
啥也没说
2025-01-09 滴滴面试真题（3）UTC转化后的本地时间呼叫高峰期  
提交不对呢，参考答案提交也不对
 
参考答案当然不对咯，毕竟只能参考嘛 巴西跟UTC时间到底差了多少呢？
2025-01-09 快手面试真题（3）同时在线人数峰值时点  
首先输出实例和提议不符，我做的结果和参考答案是一样的，提交是错误，参考答案提交也是错误
 
已修改，可以再试试
2025-01-09 快手面试真题（2）同时在线人数峰值  
记录一下第一次算峰值，想了很久，不知道还有没有其他的简单方法
with data1 as(
	select usr_id, live_id, enter_time as time1, 1 as flag1
	from ks_live_t1
	union all
	select usr_id, live_id, leave_time as time1, -1 as flag1
	from ks_live_t1
), 
data2 as(
	select 
		live_id
		, sum(flag1) over(partition by live_id order by time1 ) as online_users
	from data1
)
select t1.live_id,t2.live_nm, max(online_users) as max_online_users
from data2 t1 left join ks_live_t2 t2 on t1.live_id = t2.live_id
group by 1, 2
order by 3 desc
 
啥也没说
2025-01-09 10月1日后再也没活跃过的用户  
如果活跃的定义就是登录的话，那么只需要判断最后一次登录时间是否 <=2024-10-01 不就可以吗
select count(1) as inactive_user_count
from(
	select usr_id, max(login_time)
	from user_login_log
	group by 1
	having max(login_time) <= '2024-10-01'
) tmp
 
同样妙不可言
2025-01-09 窗口函数（4）越来越喜欢召妓，窗口函数用累计（2）  
答案只包含2023的记录，没有2024，建议修改题目或答案
 
啥也没说
2025-01-09 窗口函数（2）所有前一和每类前一，继续搞懂排序窗口函数  
需求描述是：消费笔数Top1 题目又是前二，相互间不一致，建议统一
 
已改，笔芯！
2025-01-08 时间日期（4）阶段综合-按月统计日花费，一天都不要浪费  
输出示例表格的第一个字段 rx_mon 前面少了字母t 容易误导
 
已调整答案校验逻辑，不再care 字段名称。
2025-01-08 小结（1）大数据早就能扫黄，找足证据不慌张  
问一下 区间限定[23:00:00, 03:00:00) 为什么答案 between 0 and 3 不是 between 0 and 2 呀？
 
啥也没说
2025-01-08 小结（1）大数据早就能扫黄，找足证据不慌张  
题目给定时间区间[23:00:00, 03:00:00) 为什么要 between 0 and 3 不应该  between 0 and 2 吗
 
已修改～

提交记录

提交日期 题目名称 提交代码
2026-01-16 财源广进：开除摸鱼的员工  
WITH 
attendance_hours AS (
SELECT 
employee_id,
DATE(punch_time) AS punch_date,
TIMESTAMPDIFF(HOUR, 
MIN(punch_time), 
MAX(punch_time)
) AS total_hours
FROM attendance
WHERE YEAR(punch_time) = 2025
GROUP BY employee_id, DATE(punch_time)
),
avg_attendance AS (
SELECT AVG(emp_total.total_hours) AS avg_hours
FROM (
SELECT employee_id, SUM(total_hours) AS total_hours
FROM attendance_hours
GROUP BY employee_id
) emp_total
),
attendance_score AS (
SELECT 
emp_sum.employee_id,
CASE 
WHEN emp_sum.total_hours < (SELECT avg_hours FROM avg_attendance) 
THEN 1 
ELSE 0 
END AS score_attendance
FROM (
SELECT employee_id, SUM(total_hours) AS total_hours
FROM attendance_hours
GROUP BY employee_id
) emp_sum
),
compensation_score AS (
SELECT 
id AS employee_id,
CASE 
WHEN compensation < 10 THEN 3
WHEN compensation < 20 THEN 2
WHEN compensation < 30 THEN 1
ELSE 0
END AS score_compensation
FROM (
SELECT 
id,
(salary / 12) * (
 CASE 
WHEN TIMESTAMPDIFF(MONTH, hire_date, '2026-03-09') < 6 THEN 0.5
WHEN TIMESTAMPDIFF(MONTH, hire_date, '2026-03-09') % 12 = 0 
THEN TIMESTAMPDIFF(MONTH, hire_date, '2026-03-09') / 12
ELSE CEILING(TIMESTAMPDIFF(MONTH, hire_date, '2026-03-09') / 12.0 * 2) / 2
END + 1
) AS compensation
FROM employees
) comp
),
daily_walks AS (
SELECT 
employee_id,
DATE(timestamp) AS walk_date,
COUNT(*) AS walk_count
FROM access_control
WHERE YEAR(timestamp) = 2025
AND HOUR(timestamp)BETWEEN 9 AND 11 OR HOUR(timestamp)BETWEEN 14 AND 17
GROUP BY employee_id, DATE(timestamp)
),
walk_summary AS (
SELECT 
employee_id,
COUNT(DISTINCT walk_date) AS total_walk_days,
SUM(CASE WHEN walk_count > 3 THEN 1 ELSE 0 END) AS days_over_3_walks
FROM daily_walks
GROUP BY employee_id
),
walk_score AS (
SELECT 
employee_id,
CASE
WHEN total_walk_days > 70 AND days_over_3_walks > 25 THEN 3
WHEN total_walk_days > 60 THEN 1
ELSE 0
END AS score_walk
FROM walk_summary
),
flybook_avg AS (
SELECT 
employee_id,
AVG(daily_interaction) AS avg_interaction,
AVG(daily_messages) AS avg_messages,
AVG(daily_documents) AS avg_documents
FROM flybook_stats
WHERE stat_date >= '2025-01-31'
GROUP BY employee_id
),
flybook_overall_avg AS (
SELECT 
AVG(daily_interaction) AS overall_avg_interaction,
AVG(daily_messages) AS overall_avg_messages,
AVG(daily_documents) AS overall_avg_documents
FROM flybook_stats
WHERE stat_date >= '2025-01-31'
),
document_score AS (
SELECT 
fa.employee_id,
CASE
WHEN (fa.avg_interaction < foa.overall_avg_interaction AND
fa.avg_messages < foa.overall_avg_messages AND
fa.avg_documents < foa.overall_avg_documents) THEN 3
WHEN (CASE WHEN fa.avg_interaction < foa.overall_avg_interaction THEN 1 ELSE 0 END +
CASE WHEN fa.avg_messages < foa.overall_avg_messages THEN 1 ELSE 0 END +
CASE WHEN fa.avg_documents < foa.overall_avg_documents THEN 1 ELSE 0 END) = 2 THEN 1
ELSE 0
END AS score_document
FROM flybook_avg fa
CROSS JOIN flybook_overall_avg foa
),
performance_score AS (
SELECT 
employee_id,
1 AS score_performance
FROM performance
WHERE result = 'M'
GROUP BY employee_id
)
SELECT 
e.id AS employee_id,
(COALESCE(ats.score_attendance, 0) + 
 COALESCE(cs.score_compensation, 0) + 
 COALESCE(ws.score_walk, 0) + 
 COALESCE(ds.score_document, 0) +
 COALESCE(ps.score_performance, 0)) AS score
FROM employees e
LEFT JOIN attendance_score ats ON e.id = ats.employee_id
LEFT JOIN compensation_score cs ON e.id = cs.employee_id
LEFT JOIN walk_score ws ON e.id = ws.employee_id
LEFT JOIN document_score ds ON e.id = ds.employee_id
LEFT JOIN performance_score ps ON e.id = ps.employee_id
ORDER BY employee_id ASC;
2026-01-16 财源广进：开除摸鱼的员工  
WITH 
attendance_hours AS (
SELECT 
employee_id,
DATE(punch_time) AS punch_date,
TIMESTAMPDIFF(HOUR, 
MIN(punch_time), 
MAX(punch_time)
) AS total_hours
FROM attendance
WHERE YEAR(punch_time) = 2025
GROUP BY employee_id, DATE(punch_time)
),
avg_attendance AS (
SELECT AVG(emp_total.total_hours) AS avg_hours
FROM (
SELECT employee_id, SUM(total_hours) AS total_hours
FROM attendance_hours
GROUP BY employee_id
) emp_total
),
attendance_score AS (
SELECT 
emp_sum.employee_id,
CASE 
WHEN emp_sum.total_hours <= (SELECT avg_hours FROM avg_attendance) 
THEN 1 
ELSE 0 
END AS score_attendance
FROM (
SELECT employee_id, SUM(total_hours) AS total_hours
FROM attendance_hours
GROUP BY employee_id
) emp_sum
),
compensation_score AS (
SELECT 
id AS employee_id,
CASE 
WHEN compensation < 10 THEN 3
WHEN compensation < 20 THEN 2
WHEN compensation < 30 THEN 1
ELSE 0
END AS score_compensation
FROM (
SELECT 
id,
(salary / 12) * (
 CASE 
WHEN TIMESTAMPDIFF(MONTH, hire_date, '2026-03-09') < 6 THEN 0.5
WHEN TIMESTAMPDIFF(MONTH, hire_date, '2026-03-09') % 12 = 0 
THEN TIMESTAMPDIFF(MONTH, hire_date, '2026-03-09') / 12
ELSE CEILING(TIMESTAMPDIFF(MONTH, hire_date, '2026-03-09') / 12.0 * 2) / 2
END + 1
) AS compensation
FROM employees
) comp
),
daily_walks AS (
SELECT 
employee_id,
DATE(timestamp) AS walk_date,
COUNT(*) AS walk_count
FROM access_control
WHERE YEAR(timestamp) = 2025
AND HOUR(timestamp)BETWEEN 9 AND 11 OR HOUR(timestamp)BETWEEN 14 AND 17
GROUP BY employee_id, DATE(timestamp)
),
walk_summary AS (
SELECT 
employee_id,
COUNT(DISTINCT walk_date) AS total_walk_days,
SUM(CASE WHEN walk_count > 3 THEN 1 ELSE 0 END) AS days_over_3_walks
FROM daily_walks
GROUP BY employee_id
),
walk_score AS (
SELECT 
employee_id,
CASE
WHEN total_walk_days > 70 AND days_over_3_walks > 25 THEN 3
WHEN total_walk_days > 60 THEN 1
ELSE 0
END AS score_walk
FROM walk_summary
),
flybook_avg AS (
SELECT 
employee_id,
AVG(daily_interaction) AS avg_interaction,
AVG(daily_messages) AS avg_messages,
AVG(daily_documents) AS avg_documents
FROM flybook_stats
WHERE stat_date >= '2025-01-31'
GROUP BY employee_id
),
flybook_overall_avg AS (
SELECT 
AVG(daily_interaction) AS overall_avg_interaction,
AVG(daily_messages) AS overall_avg_messages,
AVG(daily_documents) AS overall_avg_documents
FROM flybook_stats
WHERE stat_date >= '2025-01-31'
),
document_score AS (
SELECT 
fa.employee_id,
CASE
WHEN (fa.avg_interaction < foa.overall_avg_interaction AND
fa.avg_messages < foa.overall_avg_messages AND
fa.avg_documents < foa.overall_avg_documents) THEN 3
WHEN (CASE WHEN fa.avg_interaction < foa.overall_avg_interaction THEN 1 ELSE 0 END +
CASE WHEN fa.avg_messages < foa.overall_avg_messages THEN 1 ELSE 0 END +
CASE WHEN fa.avg_documents < foa.overall_avg_documents THEN 1 ELSE 0 END) = 2 THEN 1
ELSE 0
END AS score_document
FROM flybook_avg fa
CROSS JOIN flybook_overall_avg foa
)
SELECT 
e.id AS employee_id,
(COALESCE(ats.score_attendance, 0) + 
 COALESCE(cs.score_compensation, 0) + 
 COALESCE(ws.score_walk, 0) + 
 COALESCE(ds.score_document, 0)) AS score
FROM employees e
LEFT JOIN attendance_score ats ON e.id = ats.employee_id
LEFT JOIN compensation_score cs ON e.id = cs.employee_id
LEFT JOIN walk_score ws ON e.id = ws.employee_id
LEFT JOIN document_score ds ON e.id = ds.employee_id
ORDER BY employee_id ASC;
2026-01-16 财源广进：开除摸鱼的员工  
WITH 
attendance_hours AS (
SELECT 
employee_id,
DATE(punch_time) AS punch_date,
TIMESTAMPDIFF(HOUR, 
MIN(punch_time), 
MAX(punch_time)
) AS total_hours
FROM attendance
WHERE YEAR(punch_time) = 2025
GROUP BY employee_id, DATE(punch_time)
),
avg_attendance AS (
SELECT AVG(emp_total.total_hours) AS avg_hours
FROM (
SELECT employee_id, SUM(total_hours) AS total_hours
FROM attendance_hours
GROUP BY employee_id
) emp_total
),
attendance_score AS (
SELECT 
emp_sum.employee_id,
CASE 
WHEN emp_sum.total_hours < (SELECT avg_hours FROM avg_attendance) 
THEN 1 
ELSE 0 
END AS score_attendance
FROM (
SELECT employee_id, SUM(total_hours) AS total_hours
FROM attendance_hours
GROUP BY employee_id
) emp_sum
),
compensation_score AS (
SELECT 
id AS employee_id,
CASE 
WHEN compensation < 10 THEN 3
WHEN compensation < 20 THEN 2
WHEN compensation < 30 THEN 1
ELSE 0
END AS score_compensation
FROM (
SELECT 
id,
(salary / 12) * (
 CASE 
WHEN TIMESTAMPDIFF(MONTH, hire_date, '2026-03-09') < 6 THEN 0.5
WHEN TIMESTAMPDIFF(MONTH, hire_date, '2026-03-09') % 12 = 0 
THEN TIMESTAMPDIFF(MONTH, hire_date, '2026-03-09') / 12
ELSE CEILING(TIMESTAMPDIFF(MONTH, hire_date, '2026-03-09') / 12.0 * 2) / 2
END + 1
) AS compensation
FROM employees
) comp
),
daily_walks AS (
SELECT 
employee_id,
DATE(timestamp) AS walk_date,
COUNT(*) AS walk_count
FROM access_control
WHERE YEAR(timestamp) = 2025
AND HOUR(timestamp)BETWEEN 9 AND 11 OR HOUR(timestamp)BETWEEN 14 AND 17
GROUP BY employee_id, DATE(timestamp)
),
walk_summary AS (
SELECT 
employee_id,
COUNT(DISTINCT walk_date) AS total_walk_days,
SUM(CASE WHEN walk_count > 3 THEN 1 ELSE 0 END) AS days_over_3_walks
FROM daily_walks
GROUP BY employee_id
),
walk_score AS (
SELECT 
employee_id,
CASE
WHEN total_walk_days > 70 AND days_over_3_walks > 25 THEN 3
WHEN total_walk_days > 60 THEN 1
ELSE 0
END AS score_walk
FROM walk_summary
),
flybook_avg AS (
SELECT 
employee_id,
AVG(daily_interaction) AS avg_interaction,
AVG(daily_messages) AS avg_messages,
AVG(daily_documents) AS avg_documents
FROM flybook_stats
WHERE stat_date >= '2025-01-31'
GROUP BY employee_id
),
flybook_overall_avg AS (
SELECT 
AVG(daily_interaction) AS overall_avg_interaction,
AVG(daily_messages) AS overall_avg_messages,
AVG(daily_documents) AS overall_avg_documents
FROM flybook_stats
WHERE stat_date >= '2025-01-31'
),
document_score AS (
SELECT 
fa.employee_id,
CASE
WHEN (fa.avg_interaction < foa.overall_avg_interaction AND
fa.avg_messages < foa.overall_avg_messages AND
fa.avg_documents < foa.overall_avg_documents) THEN 3
WHEN (CASE WHEN fa.avg_interaction < foa.overall_avg_interaction THEN 1 ELSE 0 END +
CASE WHEN fa.avg_messages < foa.overall_avg_messages THEN 1 ELSE 0 END +
CASE WHEN fa.avg_documents < foa.overall_avg_documents THEN 1 ELSE 0 END) = 2 THEN 1
ELSE 0
END AS score_document
FROM flybook_avg fa
CROSS JOIN flybook_overall_avg foa
)
SELECT 
e.id AS employee_id,
(COALESCE(ats.score_attendance, 0) + 
 COALESCE(cs.score_compensation, 0) + 
 COALESCE(ws.score_walk, 0) + 
 COALESCE(ds.score_document, 0)) AS score
FROM employees e
LEFT JOIN attendance_score ats ON e.id = ats.employee_id
LEFT JOIN compensation_score cs ON e.id = cs.employee_id
LEFT JOIN walk_score ws ON e.id = ws.employee_id
LEFT JOIN document_score ds ON e.id = ds.employee_id
ORDER BY employee_id ASC;
2026-01-16 财源广进：开除摸鱼的员工  
WITH 
attendance_hours AS (
SELECT 
employee_id,
DATE(punch_time) AS punch_date,
TIMESTAMPDIFF(HOUR, 
MIN(punch_time), 
MAX(punch_time)
) AS total_hours
FROM attendance
WHERE YEAR(punch_time) = 2025
GROUP BY employee_id, DATE(punch_time)
),
avg_attendance AS (
SELECT AVG(emp_total.total_hours) AS avg_hours
FROM (
SELECT employee_id, SUM(total_hours) AS total_hours
FROM attendance_hours
GROUP BY employee_id
) emp_total
),
attendance_score AS (
SELECT 
emp_sum.employee_id,
CASE 
WHEN emp_sum.total_hours < (SELECT avg_hours FROM avg_attendance) 
THEN 1 
ELSE 0 
END AS score_attendance
FROM (
SELECT employee_id, SUM(total_hours) AS total_hours
FROM attendance_hours
GROUP BY employee_id
) emp_sum
),
compensation_score AS (
SELECT 
id AS employee_id,
CASE 
WHEN compensation < 10 THEN 3
WHEN compensation < 20 THEN 2
WHEN compensation < 30 THEN 1
ELSE 0
END AS score_compensation
FROM (
SELECT 
id,
(salary / 12) * (
 CASE 
WHEN TIMESTAMPDIFF(MONTH, hire_date, '2026-03-06') < 6 THEN 0.5
WHEN TIMESTAMPDIFF(MONTH, hire_date, '2026-03-06') % 12 = 0 
THEN TIMESTAMPDIFF(MONTH, hire_date, '2026-03-06') / 12
ELSE CEILING(TIMESTAMPDIFF(MONTH, hire_date, '2026-03-06') / 12.0 * 2) / 2
END + 1
) AS compensation
FROM employees
) comp
),
daily_walks AS (
SELECT 
employee_id,
DATE(timestamp) AS walk_date,
COUNT(*) AS walk_count
FROM access_control
WHERE YEAR(timestamp) = 2025
AND HOUR(timestamp)BETWEEN 9 AND 11 OR HOUR(timestamp)BETWEEN 14 AND 17
GROUP BY employee_id, DATE(timestamp)
),
walk_summary AS (
SELECT 
employee_id,
COUNT(DISTINCT walk_date) AS total_walk_days,
SUM(CASE WHEN walk_count > 3 THEN 1 ELSE 0 END) AS days_over_3_walks
FROM daily_walks
GROUP BY employee_id
),
walk_score AS (
SELECT 
employee_id,
CASE
WHEN total_walk_days > 70 AND days_over_3_walks > 25 THEN 3
WHEN total_walk_days > 60 THEN 1
ELSE 0
END AS score_walk
FROM walk_summary
),
flybook_avg AS (
SELECT 
employee_id,
AVG(daily_interaction) AS avg_interaction,
AVG(daily_messages) AS avg_messages,
AVG(daily_documents) AS avg_documents
FROM flybook_stats
WHERE stat_date >= '2026-01-31'
GROUP BY employee_id
),
flybook_overall_avg AS (
SELECT 
AVG(daily_interaction) AS overall_avg_interaction,
AVG(daily_messages) AS overall_avg_messages,
AVG(daily_documents) AS overall_avg_documents
FROM flybook_stats
WHERE stat_date >= '2026-01-31'
),
document_score AS (
SELECT 
fa.employee_id,
CASE
WHEN (fa.avg_interaction < foa.overall_avg_interaction AND
fa.avg_messages < foa.overall_avg_messages AND
fa.avg_documents < foa.overall_avg_documents) THEN 3
WHEN (CASE WHEN fa.avg_interaction < foa.overall_avg_interaction THEN 1 ELSE 0 END +
CASE WHEN fa.avg_messages < foa.overall_avg_messages THEN 1 ELSE 0 END +
CASE WHEN fa.avg_documents < foa.overall_avg_documents THEN 1 ELSE 0 END) = 2 THEN 1
ELSE 0
END AS score_document
FROM flybook_avg fa
CROSS JOIN flybook_overall_avg foa
)
SELECT 
e.id AS employee_id,
(COALESCE(ats.score_attendance, 0) + 
 COALESCE(cs.score_compensation, 0) + 
 COALESCE(ws.score_walk, 0) + 
 COALESCE(ds.score_document, 0)) AS score
FROM employees e
LEFT JOIN attendance_score ats ON e.id = ats.employee_id
LEFT JOIN compensation_score cs ON e.id = cs.employee_id
LEFT JOIN walk_score ws ON e.id = ws.employee_id
LEFT JOIN document_score ds ON e.id = ds.employee_id
ORDER BY employee_id ASC;
2026-01-16 财源广进：开除摸鱼的员工  
WITH 
attendance_hours AS (
SELECT 
employee_id,
DATE(punch_time) AS punch_date,
TIMESTAMPDIFF(HOUR, 
MIN(punch_time), 
MAX(punch_time)
) AS total_hours
FROM attendance
WHERE YEAR(punch_time) = 2025
GROUP BY employee_id, DATE(punch_time)
),
avg_attendance AS (
SELECT AVG(emp_total.total_hours) AS avg_hours
FROM (
SELECT employee_id, SUM(total_hours) AS total_hours
FROM attendance_hours
GROUP BY employee_id
) emp_total
),
attendance_score AS (
SELECT 
emp_sum.employee_id,
CASE 
WHEN emp_sum.total_hours < (SELECT avg_hours FROM avg_attendance) 
THEN 1 
ELSE 0 
END AS score_attendance
FROM (
SELECT employee_id, SUM(total_hours) AS total_hours
FROM attendance_hours
GROUP BY employee_id
) emp_sum
),
compensation_score AS (
SELECT 
id AS employee_id,
CASE
WHEN (TIMESTAMPDIFF(YEAR, hire_date, '2026-03-06') + 1) * (salary / 12) < 10 THEN 3
WHEN (TIMESTAMPDIFF(YEAR, hire_date, '2026-03-06') + 1) * (salary / 12) < 20 THEN 2
WHEN (TIMESTAMPDIFF(YEAR, hire_date, '2026-03-06') + 1) * (salary / 12) < 30 THEN 1
ELSE 0
END AS score_compensation
FROM employees
),
daily_walks AS (
SELECT 
employee_id,
DATE(timestamp) AS walk_date,
COUNT(*) AS walk_count
FROM access_control
WHERE YEAR(timestamp) = 2025
AND HOUR(timestamp)BETWEEN 9 AND 11 OR HOUR(timestamp)BETWEEN 14 AND 17
GROUP BY employee_id, DATE(timestamp)
),
walk_summary AS (
SELECT 
employee_id,
COUNT(DISTINCT walk_date) AS total_walk_days,
SUM(CASE WHEN walk_count > 3 THEN 1 ELSE 0 END) AS days_over_3_walks
FROM daily_walks
GROUP BY employee_id
),
walk_score AS (
SELECT 
employee_id,
CASE
WHEN total_walk_days > 70 AND days_over_3_walks > 25 THEN 3
WHEN total_walk_days > 60 THEN 1
ELSE 0
END AS score_walk
FROM walk_summary
),
flybook_avg AS (
SELECT 
employee_id,
AVG(daily_interaction) AS avg_interaction,
AVG(daily_messages) AS avg_messages,
AVG(daily_documents) AS avg_documents
FROM flybook_stats
WHERE stat_date >= '2025-01-31'
GROUP BY employee_id
),
flybook_overall_avg AS (
SELECT 
AVG(daily_interaction) AS overall_avg_interaction,
AVG(daily_messages) AS overall_avg_messages,
AVG(daily_documents) AS overall_avg_documents
FROM flybook_stats
WHERE stat_date >= '2025-01-31'
),
document_score AS (
SELECT 
fa.employee_id,
CASE
WHEN (fa.avg_interaction < foa.overall_avg_interaction AND
fa.avg_messages < foa.overall_avg_messages AND
fa.avg_documents < foa.overall_avg_documents) THEN 3
WHEN (CASE WHEN fa.avg_interaction < foa.overall_avg_interaction THEN 1 ELSE 0 END +
CASE WHEN fa.avg_messages < foa.overall_avg_messages THEN 1 ELSE 0 END +
CASE WHEN fa.avg_documents < foa.overall_avg_documents THEN 1 ELSE 0 END) = 2 THEN 1
ELSE 0
END AS score_document
FROM flybook_avg fa
CROSS JOIN flybook_overall_avg foa
)
SELECT 
e.id AS employee_id,
(COALESCE(ats.score_attendance, 0) + 
 COALESCE(cs.score_compensation, 0) + 
 COALESCE(ws.score_walk, 0) + 
 COALESCE(ds.score_document, 0)) AS score
FROM employees e
LEFT JOIN attendance_score ats ON e.id = ats.employee_id
LEFT JOIN compensation_score cs ON e.id = cs.employee_id
LEFT JOIN walk_score ws ON e.id = ws.employee_id
LEFT JOIN document_score ds ON e.id = ds.employee_id
ORDER BY employee_id ASC;
2026-01-16 财源广进：开除摸鱼的员工  
WITH 
attendance_hours AS (
SELECT 
employee_id,
DATE(punch_time) AS punch_date,
TIMESTAMPDIFF(HOUR, 
MIN(punch_time), 
MAX(punch_time)
) AS total_hours
FROM attendance
WHERE YEAR(punch_time) = 2025
GROUP BY employee_id, DATE(punch_time)
),
avg_attendance AS (
SELECT AVG(emp_total.total_hours) AS avg_hours
FROM (
SELECT employee_id, SUM(total_hours) AS total_hours
FROM attendance_hours
GROUP BY employee_id
) emp_total
),
attendance_score AS (
SELECT 
emp_sum.employee_id,
CASE 
WHEN emp_sum.total_hours < (SELECT avg_hours FROM avg_attendance) 
THEN 1 
ELSE 0 
END AS score_attendance
FROM (
SELECT employee_id, SUM(total_hours) AS total_hours
FROM attendance_hours
GROUP BY employee_id
) emp_sum
),
compensation_score AS (
SELECT 
id AS employee_id,
CASE 
WHEN compensation < 10 THEN 3
WHEN compensation < 20 THEN 2
WHEN compensation < 30 THEN 1
ELSE 0
END AS score_compensation
FROM (
SELECT 
id,
(salary / 12) * (
 CASE 
WHEN TIMESTAMPDIFF(MONTH, hire_date, '2026-03-06') < 6 THEN 0.5
WHEN TIMESTAMPDIFF(MONTH, hire_date, '2026-03-06') % 12 = 0 
THEN TIMESTAMPDIFF(MONTH, hire_date, '2026-03-06') / 12
ELSE CEILING(TIMESTAMPDIFF(MONTH, hire_date, '2026-03-06') / 12.0 * 2) / 2
END + 1
) AS compensation
FROM employees
) comp
),
daily_walks AS (
SELECT 
employee_id,
DATE(timestamp) AS walk_date,
COUNT(*) AS walk_count
FROM access_control
WHERE YEAR(timestamp) = 2025
AND HOUR(timestamp)BETWEEN 9 AND 11 OR HOUR(timestamp)BETWEEN 14 AND 17
GROUP BY employee_id, DATE(timestamp)
),
walk_summary AS (
SELECT 
employee_id,
COUNT(DISTINCT walk_date) AS total_walk_days,
SUM(CASE WHEN walk_count > 3 THEN 1 ELSE 0 END) AS days_over_3_walks
FROM daily_walks
GROUP BY employee_id
),
walk_score AS (
SELECT 
employee_id,
CASE
WHEN total_walk_days > 70 AND days_over_3_walks > 25 THEN 3
WHEN total_walk_days > 60 THEN 1
ELSE 0
END AS score_walk
FROM walk_summary
),
flybook_avg AS (
SELECT 
employee_id,
AVG(daily_interaction) AS avg_interaction,
AVG(daily_messages) AS avg_messages,
AVG(daily_documents) AS avg_documents
FROM flybook_stats
WHERE stat_date >= '2025-01-31'
GROUP BY employee_id
),
flybook_overall_avg AS (
SELECT 
AVG(daily_interaction) AS overall_avg_interaction,
AVG(daily_messages) AS overall_avg_messages,
AVG(daily_documents) AS overall_avg_documents
FROM flybook_stats
WHERE stat_date >= '2025-01-31'
),
document_score AS (
SELECT 
fa.employee_id,
CASE
WHEN (fa.avg_interaction < foa.overall_avg_interaction AND
fa.avg_messages < foa.overall_avg_messages AND
fa.avg_documents < foa.overall_avg_documents) THEN 3
WHEN (CASE WHEN fa.avg_interaction < foa.overall_avg_interaction THEN 1 ELSE 0 END +
CASE WHEN fa.avg_messages < foa.overall_avg_messages THEN 1 ELSE 0 END +
CASE WHEN fa.avg_documents < foa.overall_avg_documents THEN 1 ELSE 0 END) = 2 THEN 1
ELSE 0
END AS score_document
FROM flybook_avg fa
CROSS JOIN flybook_overall_avg foa
)
SELECT 
e.id AS employee_id,
(COALESCE(ats.score_attendance, 0) + 
 COALESCE(cs.score_compensation, 0) + 
 COALESCE(ws.score_walk, 0) + 
 COALESCE(ds.score_document, 0)) AS score
FROM employees e
LEFT JOIN attendance_score ats ON e.id = ats.employee_id
LEFT JOIN compensation_score cs ON e.id = cs.employee_id
LEFT JOIN walk_score ws ON e.id = ws.employee_id
LEFT JOIN document_score ds ON e.id = ds.employee_id
ORDER BY employee_id ASC;
2026-01-16 财源广进：开除摸鱼的员工  
WITH 
attendance_hours AS (
SELECT 
employee_id,
DATE(punch_time) AS punch_date,
TIMESTAMPDIFF(HOUR, 
MIN(punch_time), 
MAX(punch_time)
) AS total_hours
FROM attendance
WHERE YEAR(punch_time) = 2025
GROUP BY employee_id, DATE(punch_time)
),
avg_attendance AS (
SELECT AVG(emp_total.total_hours) AS avg_hours
FROM (
SELECT employee_id, SUM(total_hours) AS total_hours
FROM attendance_hours
GROUP BY employee_id
) emp_total
),
attendance_score AS (
SELECT 
emp_sum.employee_id,
CASE 
WHEN emp_sum.total_hours < (SELECT avg_hours FROM avg_attendance) 
THEN 1 
ELSE 0 
END AS score_attendance
FROM (
SELECT employee_id, SUM(total_hours) AS total_hours
FROM attendance_hours
GROUP BY employee_id
) emp_sum
),
compensation_score AS (
SELECT 
id AS employee_id,
CASE 
WHEN compensation < 10 THEN 3
WHEN compensation < 20 THEN 2
WHEN compensation < 30 THEN 1
ELSE 0
END AS score_compensation
FROM (
SELECT 
id,
(salary / 12) * (
CASE 
WHEN TIMESTAMPDIFF(MONTH, hire_date, CURDATE()) < 6 THEN 0.5
WHEN TIMESTAMPDIFF(MONTH, hire_date, CURDATE()) % 12 = 0 
THEN TIMESTAMPDIFF(MONTH, hire_date, CURDATE()) / 12
ELSE CEILING(TIMESTAMPDIFF(MONTH, hire_date, CURDATE()) / 12.0 * 2) / 2
END + 1
) AS compensation
FROM employees
) comp
),
daily_walks AS (
SELECT 
employee_id,
DATE(timestamp) AS walk_date,
COUNT(*) AS walk_count
FROM access_control
WHERE YEAR(timestamp) = 2025
AND HOUR(timestamp)BETWEEN 9 AND 11 OR HOUR(timestamp)BETWEEN 14 AND 17
GROUP BY employee_id, DATE(timestamp)
),
walk_summary AS (
SELECT 
employee_id,
COUNT(DISTINCT walk_date) AS total_walk_days,
SUM(CASE WHEN walk_count > 3 THEN 1 ELSE 0 END) AS days_over_3_walks
FROM daily_walks
GROUP BY employee_id
),
walk_score AS (
SELECT 
employee_id,
CASE
WHEN total_walk_days > 70 AND days_over_3_walks > 25 THEN 3
WHEN total_walk_days > 60 THEN 1
ELSE 0
END AS score_walk
FROM walk_summary
),
flybook_avg AS (
SELECT 
employee_id,
AVG(daily_interaction) AS avg_interaction,
AVG(daily_messages) AS avg_messages,
AVG(daily_documents) AS avg_documents
FROM flybook_stats
WHERE stat_date >= '2025-01-31'
GROUP BY employee_id
),
flybook_overall_avg AS (
SELECT 
AVG(daily_interaction) AS overall_avg_interaction,
AVG(daily_messages) AS overall_avg_messages,
AVG(daily_documents) AS overall_avg_documents
FROM flybook_stats
WHERE stat_date >= '2025-01-31'
),
document_score AS (
SELECT 
fa.employee_id,
CASE
WHEN (fa.avg_interaction < foa.overall_avg_interaction AND
fa.avg_messages < foa.overall_avg_messages AND
fa.avg_documents < foa.overall_avg_documents) THEN 3
WHEN (CASE WHEN fa.avg_interaction < foa.overall_avg_interaction THEN 1 ELSE 0 END +
CASE WHEN fa.avg_messages < foa.overall_avg_messages THEN 1 ELSE 0 END +
CASE WHEN fa.avg_documents < foa.overall_avg_documents THEN 1 ELSE 0 END) = 2 THEN 1
ELSE 0
END AS score_document
FROM flybook_avg fa
CROSS JOIN flybook_overall_avg foa
)
SELECT 
e.id AS employee_id,
(COALESCE(ats.score_attendance, 0) + 
 COALESCE(cs.score_compensation, 0) + 
 COALESCE(ws.score_walk, 0) + 
 COALESCE(ds.score_document, 0)) AS score
FROM employees e
LEFT JOIN attendance_score ats ON e.id = ats.employee_id
LEFT JOIN compensation_score cs ON e.id = cs.employee_id
LEFT JOIN walk_score ws ON e.id = ws.employee_id
LEFT JOIN document_score ds ON e.id = ds.employee_id
ORDER BY employee_id ASC;
2026-01-16 财源广进：开除摸鱼的员工  
WITH 
attendance_hours AS (
SELECT 
employee_id,
DATE(punch_time) AS punch_date,
TIMESTAMPDIFF(HOUR, 
MIN(punch_time), 
MAX(punch_time)
) AS total_hours
FROM attendance
WHERE YEAR(punch_time) = 2025
GROUP BY employee_id, DATE(punch_time)
),
avg_attendance AS (
SELECT AVG(emp_total.total_hours) AS avg_hours
FROM (
SELECT employee_id, SUM(total_hours) AS total_hours
FROM attendance_hours
GROUP BY employee_id
) emp_total
),
attendance_score AS (
SELECT 
emp_sum.employee_id,
CASE 
WHEN emp_sum.total_hours < (SELECT avg_hours FROM avg_attendance) 
THEN 1 
ELSE 0 
END AS score_attendance
FROM (
SELECT employee_id, SUM(total_hours) AS total_hours
FROM attendance_hours
GROUP BY employee_id
) emp_sum
),
compensation_score AS (
SELECT 
id AS employee_id,
CASE 
WHEN compensation < 10 THEN 3
WHEN compensation < 20 THEN 2
WHEN compensation < 30 THEN 1
ELSE 0
END AS score_compensation
FROM (
SELECT 
id,
(salary / 12) * (
CASE 
WHEN TIMESTAMPDIFF(MONTH, hire_date, CURDATE()) < 6 THEN 0.5
WHEN TIMESTAMPDIFF(MONTH, hire_date, CURDATE()) % 12 = 0 
THEN TIMESTAMPDIFF(MONTH, hire_date, CURDATE()) / 12
ELSE CEILING(TIMESTAMPDIFF(MONTH, hire_date, CURDATE()) / 12.0 * 2) / 2
END + 1
) AS compensation
FROM employees
) comp
),
daily_walks AS (
SELECT 
employee_id,
DATE(timestamp) AS walk_date,
COUNT(*) AS walk_count
FROM access_control
WHERE YEAR(timestamp) = 2025
AND HOUR(timestamp) NOT BETWEEN 12 AND 13
GROUP BY employee_id, DATE(timestamp)
),
walk_summary AS (
SELECT 
employee_id,
COUNT(DISTINCT walk_date) AS total_walk_days,
SUM(CASE WHEN walk_count > 3 THEN 1 ELSE 0 END) AS days_over_3_walks
FROM daily_walks
GROUP BY employee_id
),
walk_score AS (
SELECT 
employee_id,
CASE
WHEN total_walk_days > 70 AND days_over_3_walks > 25 THEN 3
WHEN total_walk_days > 60 THEN 1
ELSE 0
END AS score_walk
FROM walk_summary
),
flybook_avg AS (
SELECT 
employee_id,
AVG(daily_interaction) AS avg_interaction,
AVG(daily_messages) AS avg_messages,
AVG(daily_documents) AS avg_documents
FROM flybook_stats
WHERE stat_date >= '2025-01-31'
GROUP BY employee_id
),
flybook_overall_avg AS (
SELECT 
AVG(daily_interaction) AS overall_avg_interaction,
AVG(daily_messages) AS overall_avg_messages,
AVG(daily_documents) AS overall_avg_documents
FROM flybook_stats
WHERE stat_date >= '2025-01-31'
),
document_score AS (
SELECT 
fa.employee_id,
CASE
WHEN (fa.avg_interaction < foa.overall_avg_interaction AND
fa.avg_messages < foa.overall_avg_messages AND
fa.avg_documents < foa.overall_avg_documents) THEN 3
WHEN (CASE WHEN fa.avg_interaction < foa.overall_avg_interaction THEN 1 ELSE 0 END +
CASE WHEN fa.avg_messages < foa.overall_avg_messages THEN 1 ELSE 0 END +
CASE WHEN fa.avg_documents < foa.overall_avg_documents THEN 1 ELSE 0 END) = 2 THEN 1
ELSE 0
END AS score_document
FROM flybook_avg fa
CROSS JOIN flybook_overall_avg foa
)
SELECT 
e.id AS employee_id,
(COALESCE(ats.score_attendance, 0) + 
 COALESCE(cs.score_compensation, 0) + 
 COALESCE(ws.score_walk, 0) + 
 COALESCE(ds.score_document, 0)) AS score
FROM employees e
LEFT JOIN attendance_score ats ON e.id = ats.employee_id
LEFT JOIN compensation_score cs ON e.id = cs.employee_id
LEFT JOIN walk_score ws ON e.id = ws.employee_id
LEFT JOIN document_score ds ON e.id = ds.employee_id
ORDER BY employee_id ASC;
2026-01-16 财源广进：开除摸鱼的员工  
WITH 
attendance_hours AS (
SELECT 
employee_id,
DATE(punch_time) AS punch_date,
TIMESTAMPDIFF(HOUR, 
MIN(punch_time), 
MAX(punch_time)
) AS total_hours
FROM attendance
WHERE YEAR(punch_time) = 2025
GROUP BY employee_id, DATE(punch_time)
),
avg_attendance AS (
SELECT AVG(emp_total.total_hours) AS avg_hours
FROM (
SELECT employee_id, SUM(total_hours) AS total_hours
FROM attendance_hours
GROUP BY employee_id
) emp_total
),
attendance_score AS (
SELECT 
emp_sum.employee_id,
CASE 
WHEN emp_sum.total_hours < (SELECT avg_hours FROM avg_attendance) 
THEN 1 
ELSE 0 
END AS score_attendance
FROM (
SELECT employee_id, SUM(total_hours) AS total_hours
FROM attendance_hours
GROUP BY employee_id
) emp_sum
),
compensation_score AS (
SELECT 
id AS employee_id,
CASE
WHEN (TIMESTAMPDIFF(YEAR, hire_date, CURDATE()) + 1) * (salary / 12) < 10 THEN 3
WHEN (TIMESTAMPDIFF(YEAR, hire_date, CURDATE()) + 1) * (salary / 12) < 20 THEN 2
WHEN (TIMESTAMPDIFF(YEAR, hire_date, CURDATE()) + 1) * (salary / 12) < 30 THEN 1
ELSE 0
END AS score_compensation
FROM employees
),
daily_walks AS (
SELECT 
employee_id,
DATE(timestamp) AS walk_date,
COUNT(*) AS walk_count
FROM access_control
WHERE YEAR(timestamp) = 2025
AND HOUR(timestamp) NOT BETWEEN 12 AND 13
GROUP BY employee_id, DATE(timestamp)
),
walk_summary AS (
SELECT 
employee_id,
COUNT(DISTINCT walk_date) AS total_walk_days,
SUM(CASE WHEN walk_count > 3 THEN 1 ELSE 0 END) AS days_over_3_walks
FROM daily_walks
GROUP BY employee_id
),
walk_score AS (
SELECT 
employee_id,
CASE
WHEN total_walk_days > 70 AND days_over_3_walks > 25 THEN 3
WHEN total_walk_days > 60 THEN 1
ELSE 0
END AS score_walk
FROM walk_summary
),
flybook_avg AS (
SELECT 
employee_id,
AVG(daily_interaction) AS avg_interaction,
AVG(daily_messages) AS avg_messages,
AVG(daily_documents) AS avg_documents
FROM flybook_stats
WHERE stat_date >= '2025-01-31'
GROUP BY employee_id
),
flybook_overall_avg AS (
SELECT 
AVG(daily_interaction) AS overall_avg_interaction,
AVG(daily_messages) AS overall_avg_messages,
AVG(daily_documents) AS overall_avg_documents
FROM flybook_stats
WHERE stat_date >= '2025-01-31'
),
document_score AS (
SELECT 
fa.employee_id,
CASE
WHEN (fa.avg_interaction < foa.overall_avg_interaction AND
fa.avg_messages < foa.overall_avg_messages AND
fa.avg_documents < foa.overall_avg_documents) THEN 3
WHEN (CASE WHEN fa.avg_interaction < foa.overall_avg_interaction THEN 1 ELSE 0 END +
CASE WHEN fa.avg_messages < foa.overall_avg_messages THEN 1 ELSE 0 END +
CASE WHEN fa.avg_documents < foa.overall_avg_documents THEN 1 ELSE 0 END) = 2 THEN 1
ELSE 0
END AS score_document
FROM flybook_avg fa
CROSS JOIN flybook_overall_avg foa
)
SELECT 
e.id AS employee_id,
(COALESCE(ats.score_attendance, 0) + 
 COALESCE(cs.score_compensation, 0) + 
 COALESCE(ws.score_walk, 0) + 
 COALESCE(ds.score_document, 0)) AS score
FROM employees e
LEFT JOIN attendance_score ats ON e.id = ats.employee_id
LEFT JOIN compensation_score cs ON e.id = cs.employee_id
LEFT JOIN walk_score ws ON e.id = ws.employee_id
LEFT JOIN document_score ds ON e.id = ds.employee_id
ORDER BY employee_id ASC;
2026-01-16 财源广进：开除摸鱼的员工  
WITH 
attendance_hours AS (
SELECT 
employee_id,
DATE(punch_time) AS punch_date,
TIMESTAMPDIFF(HOUR, 
MIN(punch_time), 
MAX(punch_time)
) AS total_hours
FROM attendance
WHERE YEAR(punch_time) = 2025
GROUP BY employee_id, DATE(punch_time)
),
avg_attendance AS (
SELECT AVG(emp_total.total_hours) AS avg_hours
FROM (
SELECT employee_id, SUM(total_hours) AS total_hours
FROM attendance_hours
GROUP BY employee_id
) emp_total
),
attendance_score AS (
SELECT 
emp_sum.employee_id,
CASE 
WHEN emp_sum.total_hours < (SELECT avg_hours FROM avg_attendance) 
THEN 1 
ELSE 0 
END AS score_attendance
FROM (
SELECT employee_id, SUM(total_hours) AS total_hours
FROM attendance_hours
GROUP BY employee_id
) emp_sum
),
compensation_score AS (
SELECT 
id AS employee_id,
CASE 
WHEN compensation < 10 THEN 3
WHEN compensation < 20 THEN 2
WHEN compensation < 30 THEN 1
ELSE 0
END ASscore_compensation
FROM (
SELECT 
id,
salary * (
CASE 
WHEN TIMESTAMPDIFF(MONTH, hire_date, '2025-01-17') < 6 THEN 0.5
WHEN TIMESTAMPDIFF(MONTH, hire_date, '2025-01-17') % 12 = 0 
THEN TIMESTAMPDIFF(MONTH, hire_date, '2025-01-17') / 12
ELSE CEILING(TIMESTAMPDIFF(MONTH, hire_date, '2025-01-17') / 12.0 * 2) / 2
END + 1
) AS compensation
FROM employees
) comp
),
daily_walks AS (
SELECT 
employee_id,
DATE(timestamp) AS walk_date,
COUNT(*) AS walk_count
FROM access_control
WHERE YEAR(timestamp) = 2025
AND HOUR(timestamp) NOT BETWEEN 12 AND 13
GROUP BY employee_id, DATE(timestamp)
),
walk_summary AS (
SELECT 
employee_id,
COUNT(DISTINCT walk_date) AS total_walk_days,
SUM(CASE WHEN walk_count > 3 THEN 1 ELSE 0 END) AS days_over_3_walks
FROM daily_walks
GROUP BY employee_id
),
walk_score AS (
SELECT 
employee_id,
CASE
WHEN total_walk_days > 70 AND days_over_3_walks > 25 THEN 3
WHEN total_walk_days > 60 THEN 1
ELSE 0
END AS score_walk
FROM walk_summary
),
flybook_avg AS (
SELECT 
employee_id,
AVG(daily_interaction) AS avg_interaction,
AVG(daily_messages) AS avg_messages,
AVG(daily_documents) AS avg_documents
FROM flybook_stats
WHERE stat_date >= '2025-01-31'
GROUP BY employee_id
),
flybook_overall_avg AS (
SELECT 
AVG(daily_interaction) AS overall_avg_interaction,
AVG(daily_messages) AS overall_avg_messages,
AVG(daily_documents) AS overall_avg_documents
FROM flybook_stats
WHERE stat_date >= '2025-01-31'
),
document_score AS (
SELECT 
fa.employee_id,
CASE
WHEN (fa.avg_interaction < foa.overall_avg_interaction AND
fa.avg_messages < foa.overall_avg_messages AND
fa.avg_documents < foa.overall_avg_documents) THEN 3
WHEN (CASE WHEN fa.avg_interaction < foa.overall_avg_interaction THEN 1 ELSE 0 END +
CASE WHEN fa.avg_messages < foa.overall_avg_messages THEN 1 ELSE 0 END +
CASE WHEN fa.avg_documents < foa.overall_avg_documents THEN 1 ELSE 0 END) = 2 THEN 1
ELSE 0
END AS score_document
FROM flybook_avg fa
CROSS JOIN flybook_overall_avg foa
)
SELECT 
e.id AS employee_id,
(COALESCE(ats.score_attendance, 0) + 
 COALESCE(cs.score_compensation, 0) + 
 COALESCE(ws.score_walk, 0) + 
 COALESCE(ds.score_document, 0)) AS score
FROM employees e
LEFT JOIN attendance_score ats ON e.id = ats.employee_id
LEFT JOIN compensation_score cs ON e.id = cs.employee_id
LEFT JOIN walk_score ws ON e.id = ws.employee_id
LEFT JOIN document_score ds ON e.id = ds.employee_id
ORDER BY score DESC;
2026-01-16 财源广进：开除摸鱼的员工  
WITH 
attendance_hours AS (
SELECT 
employee_id,
DATE(punch_time) AS punch_date,
TIMESTAMPDIFF(HOUR, 
MIN(punch_time), 
MAX(punch_time)
) AS total_hours
FROM attendance
WHERE YEAR(punch_time) = 2025
GROUP BY employee_id, DATE(punch_time)
),
avg_attendance AS (
SELECT AVG(emp_total.total_hours) AS avg_hours
FROM (
SELECT employee_id, SUM(total_hours) AS total_hours
FROM attendance_hours
GROUP BY employee_id
) emp_total
),
attendance_score AS (
SELECT 
emp_sum.employee_id,
CASE 
WHEN emp_sum.total_hours <= (SELECT avg_hours FROM avg_attendance) 
THEN 1 
ELSE 0 
END AS score_attendance
FROM (
SELECT employee_id, SUM(total_hours) AS total_hours
FROM attendance_hours
GROUP BY employee_id
) emp_sum
),
compensation_score AS (
SELECT 
id AS employee_id,
CASE
WHEN (TIMESTAMPDIFF(YEAR, hire_date, CURDATE()) + 1) * (salary / 12) < 10 THEN 3
WHEN (TIMESTAMPDIFF(YEAR, hire_date, CURDATE()) + 1) * (salary / 12) < 20 THEN 2
WHEN (TIMESTAMPDIFF(YEAR, hire_date, CURDATE()) + 1) * (salary / 12) < 30 THEN 1
ELSE 0
END AS score_compensation
FROM employees
),
daily_walks AS (
SELECT 
employee_id,
DATE(timestamp) AS walk_date,
COUNT(*) AS walk_count
FROM access_control
WHERE YEAR(timestamp) = 2025
AND HOUR(timestamp) NOT BETWEEN 12 AND 13
GROUP BY employee_id, DATE(timestamp)
),
walk_summary AS (
SELECT 
employee_id,
COUNT(DISTINCT walk_date) AS total_walk_days,
SUM(CASE WHEN walk_count > 3 THEN 1 ELSE 0 END) AS days_over_3_walks
FROM daily_walks
GROUP BY employee_id
),
walk_score AS (
SELECT 
employee_id,
CASE
WHEN total_walk_days > 70 AND days_over_3_walks > 25 THEN 3
WHEN total_walk_days > 60 THEN 1
ELSE 0
END AS score_walk
FROM walk_summary
),
flybook_avg AS (
SELECT 
employee_id,
AVG(daily_interaction) AS avg_interaction,
AVG(daily_messages) AS avg_messages,
AVG(daily_documents) AS avg_documents
FROM flybook_stats
WHERE stat_date >= '2025-01-31'
GROUP BY employee_id
),
flybook_overall_avg AS (
SELECT 
AVG(daily_interaction) AS overall_avg_interaction,
AVG(daily_messages) AS overall_avg_messages,
AVG(daily_documents) AS overall_avg_documents
FROM flybook_stats
WHERE stat_date >= '2025-01-31'
),
document_score AS (
SELECT 
fa.employee_id,
CASE
WHEN (fa.avg_interaction < foa.overall_avg_interaction AND
fa.avg_messages < foa.overall_avg_messages AND
fa.avg_documents < foa.overall_avg_documents) THEN 3
WHEN (CASE WHEN fa.avg_interaction < foa.overall_avg_interaction THEN 1 ELSE 0 END +
CASE WHEN fa.avg_messages < foa.overall_avg_messages THEN 1 ELSE 0 END +
CASE WHEN fa.avg_documents < foa.overall_avg_documents THEN 1 ELSE 0 END) = 2 THEN 1
ELSE 0
END AS score_document
FROM flybook_avg fa
CROSS JOIN flybook_overall_avg foa
)
SELECT 
e.id AS employee_id,
(COALESCE(ats.score_attendance, 0) + 
 COALESCE(cs.score_compensation, 0) + 
 COALESCE(ws.score_walk, 0) + 
 COALESCE(ds.score_document, 0)) AS score
FROM employees e
LEFT JOIN attendance_score ats ON e.id = ats.employee_id
LEFT JOIN compensation_score cs ON e.id = cs.employee_id
LEFT JOIN walk_score ws ON e.id = ws.employee_id
LEFT JOIN document_score ds ON e.id = ds.employee_id
ORDER BY score DESC;
2026-01-16 财源广进：开除摸鱼的员工  
WITH 
attendance_hours AS (
SELECT 
employee_id,
DATE(punch_time) AS punch_date,
TIMESTAMPDIFF(HOUR, 
MIN(punch_time), 
MAX(punch_time)
) AS total_hours
FROM attendance
WHERE YEAR(punch_time) = 2025
GROUP BY employee_id, DATE(punch_time)
),
emp_total_hours AS (
SELECT 
employee_id,
SUM(total_hours) AS total_hours
FROM attendance_hours
GROUP BY employee_id
),
avg_attendance AS (
SELECT AVG(total_hours) AS avg_hours
FROM emp_total_hours
),
attendance_score AS (
SELECT 
employee_id,
CASE 
WHEN total_hours < (SELECT avg_hours FROM avg_attendance) 
THEN 1 
ELSE 0 
END AS score_attendance
FROM emp_total_hours
),
compensation_score AS (
SELECT 
id AS employee_id,
CASE
WHEN (TIMESTAMPDIFF(YEAR, hire_date, CURDATE()) + 1) * salary < 10 THEN 3
WHEN (TIMESTAMPDIFF(YEAR, hire_date, CURDATE()) + 1) * salary < 20 THEN 2
WHEN (TIMESTAMPDIFF(YEAR, hire_date, CURDATE()) + 1) * salary < 30 THEN 1
ELSE 0
END AS score_compensation
FROM employees
),
daily_walks AS (
SELECT 
employee_id,
DATE(timestamp) AS walk_date,
COUNT(*) AS walk_count
FROM access_control
WHERE YEAR(timestamp) = 2025
AND (HOUR(timestamp) < 12 OR HOUR(timestamp) >= 13)
GROUP BY employee_id, DATE(timestamp)
),
walk_summary AS (
SELECT 
employee_id,
COUNT(DISTINCT walk_date) AS total_walk_days,
SUM(CASE WHEN walk_count > 3 THEN 1 ELSE 0 END) AS days_over_3_walks
FROM daily_walks
GROUP BY employee_id
),
walk_score AS (
SELECT 
employee_id,
CASE
WHEN total_walk_days > 70 AND days_over_3_walks > 25 THEN 3
WHEN total_walk_days > 60 THEN 1
ELSE 0
END AS score_walk
FROM walk_summary
),
flybook_emp_avg AS (
SELECT 
employee_id,
AVG(daily_interaction) AS avg_interaction,
AVG(daily_messages) AS avg_messages,
AVG(daily_documents) AS avg_documents
FROM flybook_stats
WHERE stat_date >= '2025-01-31'
GROUP BY employee_id
),
flybook_overall_avg AS (
SELECT 
AVG(avg_interaction) AS overall_avg_interaction,
AVG(avg_messages) AS overall_avg_messages,
AVG(avg_documents) AS overall_avg_documents
FROM flybook_emp_avg
),
document_score AS (
SELECT 
fea.employee_id,
CASE
WHEN (fea.avg_interaction < (SELECT overall_avg_interaction FROM flybook_overall_avg) AND
fea.avg_messages < (SELECT overall_avg_messages FROM flybook_overall_avg) AND
fea.avg_documents < (SELECT overall_avg_documents FROM flybook_overall_avg)) THEN 3
WHEN ((CASE WHEN fea.avg_interaction < (SELECT overall_avg_interaction FROM flybook_overall_avg) THEN 1 ELSE 0 END) +
(CASE WHEN fea.avg_messages < (SELECT overall_avg_messages FROM flybook_overall_avg) THEN 1 ELSE 0 END) +
(CASE WHEN fea.avg_documents < (SELECT overall_avg_documents FROM flybook_overall_avg) THEN 1 ELSE 0 END)) = 2 THEN 1
ELSE 0
END AS score_document
FROM flybook_emp_avg fea
)
SELECT 
e.id AS employee_id,
(COALESCE(ats.score_attendance, 0) + 
 COALESCE(cs.score_compensation, 0) + 
 COALESCE(ws.score_walk, 0) + 
 COALESCE(ds.score_document, 0)) AS score
FROM employees e
LEFT JOIN attendance_score ats ON e.id = ats.employee_id
LEFT JOIN compensation_score cs ON e.id = cs.employee_id
LEFT JOIN walk_score ws ON e.id = ws.employee_id
LEFT JOIN document_score ds ON e.id = ds.employee_id;
2026-01-16 财源广进：开除摸鱼的员工  
WITH 
attendance_hours AS (
SELECT 
employee_id,
DATE(punch_time) AS punch_date,
TIMESTAMPDIFF(HOUR, 
MIN(punch_time), 
MAX(punch_time)
) AS total_hours
FROM attendance
WHERE YEAR(punch_time) = 2025
GROUP BY employee_id, DATE(punch_time)
),
avg_attendance AS (
SELECT AVG(emp_total.total_hours) AS avg_hours
FROM (
SELECT employee_id, SUM(total_hours) AS total_hours
FROM attendance_hours
GROUP BY employee_id
) emp_total
),
attendance_score AS (
SELECT 
emp_sum.employee_id,
CASE 
WHEN emp_sum.total_hours < (SELECT avg_hours FROM avg_attendance) 
THEN 1 
ELSE 0 
END AS score_attendance
FROM (
SELECT employee_id, SUM(total_hours) AS total_hours
FROM attendance_hours
GROUP BY employee_id
) emp_sum
),
compensation_score AS (
SELECT 
id AS employee_id,
CASE
WHEN (TIMESTAMPDIFF(YEAR, hire_date, CURDATE()) + 1) * (salary / 12) < 10 THEN 3
WHEN (TIMESTAMPDIFF(YEAR, hire_date, CURDATE()) + 1) * (salary / 12) < 20 THEN 2
WHEN (TIMESTAMPDIFF(YEAR, hire_date, CURDATE()) + 1) * (salary / 12) < 30 THEN 1
ELSE 0
END AS score_compensation
FROM employees
),
daily_walks AS (
SELECT 
employee_id,
DATE(timestamp) AS walk_date,
COUNT(*) AS walk_count
FROM access_control
WHERE YEAR(timestamp) = 2025
AND HOUR(timestamp) NOT BETWEEN 12 AND 13
GROUP BY employee_id, DATE(timestamp)
),
walk_summary AS (
SELECT 
employee_id,
COUNT(DISTINCT walk_date) AS total_walk_days,
SUM(CASE WHEN walk_count > 3 THEN 1 ELSE 0 END) AS days_over_3_walks
FROM daily_walks
GROUP BY employee_id
),
walk_score AS (
SELECT 
employee_id,
CASE
WHEN total_walk_days > 70 AND days_over_3_walks > 25 THEN 3
WHEN total_walk_days > 60 THEN 1
ELSE 0
END AS score_walk
FROM walk_summary
),
flybook_avg AS (
SELECT 
employee_id,
AVG(daily_interaction) AS avg_interaction,
AVG(daily_messages) AS avg_messages,
AVG(daily_documents) AS avg_documents
FROM flybook_stats
WHERE stat_date >= '2025-01-31'
GROUP BY employee_id
),
flybook_overall_avg AS (
SELECT 
AVG(daily_interaction) AS overall_avg_interaction,
AVG(daily_messages) AS overall_avg_messages,
AVG(daily_documents) AS overall_avg_documents
FROM flybook_stats
WHERE stat_date >= '2025-01-31'
),
document_score AS (
SELECT 
fa.employee_id,
CASE
WHEN (fa.avg_interaction < (SELECT overall_avg_interaction FROM flybook_overall_avg) AND
fa.avg_messages < (SELECT overall_avg_messages FROM flybook_overall_avg) AND
fa.avg_documents < (SELECT overall_avg_documents FROM flybook_overall_avg)) THEN 3
WHEN ((CASE WHEN fa.avg_interaction < (SELECT overall_avg_interaction FROM flybook_overall_avg) THEN 1 ELSE 0 END) +
(CASE WHEN fa.avg_messages < (SELECT overall_avg_messages FROM flybook_overall_avg) THEN 1 ELSE 0 END) +
(CASE WHEN fa.avg_documents < (SELECT overall_avg_documents FROM flybook_overall_avg) THEN 1 ELSE 0 END)) = 2 THEN 1
ELSE 0
END AS score_document
FROM flybook_avg fa
)
SELECT 
e.id AS employee_id,
(COALESCE(ats.score_attendance, 0) + 
 COALESCE(cs.score_compensation, 0) + 
 COALESCE(ws.score_walk, 0) + 
 COALESCE(ds.score_document, 0)) AS score
FROM employees e
LEFT JOIN attendance_score ats ON e.id = ats.employee_id
LEFT JOIN compensation_score cs ON e.id = cs.employee_id
LEFT JOIN walk_score ws ON e.id = ws.employee_id
LEFT JOIN document_score ds ON e.id = ds.employee_id;
2026-01-16 财源广进：开除摸鱼的员工  
WITH 
attendance_hours AS (
SELECT 
employee_id,
DATE(punch_time) AS punch_date,
TIMESTAMPDIFF(HOUR, 
MIN(punch_time), 
MAX(punch_time)
) AS total_hours
FROM attendance
WHERE YEAR(punch_time) = 2025
GROUP BY employee_id, DATE(punch_time)
),
avg_attendance AS (
SELECT AVG(emp_total.total_hours) AS avg_hours
FROM (
SELECT employee_id, SUM(total_hours) AS total_hours
FROM attendance_hours
GROUP BY employee_id
) emp_total
),
attendance_score AS (
SELECT 
emp_sum.employee_id,
CASE 
WHEN emp_sum.total_hours < (SELECT avg_hours FROM avg_attendance) 
THEN 1 
ELSE 0 
END AS score_attendance
FROM (
SELECT employee_id, SUM(total_hours) AS total_hours
FROM attendance_hours
GROUP BY employee_id
) emp_sum
),
compensation_score AS (
SELECT 
id AS employee_id,
CASE
WHEN (TIMESTAMPDIFF(YEAR, hire_date, CURDATE()) + 1) * (salary / 12) < 10 THEN 3
WHEN (TIMESTAMPDIFF(YEAR, hire_date, CURDATE()) + 1) * (salary / 12) < 20 THEN 2
WHEN (TIMESTAMPDIFF(YEAR, hire_date, CURDATE()) + 1) * (salary / 12) < 30 THEN 1
ELSE 0
END AS score_compensation
FROM employees
),
daily_walks AS (
SELECT 
employee_id,
DATE(timestamp) AS walk_date,
COUNT(*) AS walk_count
FROM access_control
WHERE YEAR(timestamp) = 2025
AND HOUR(timestamp) NOT BETWEEN 12 AND 13
GROUP BY employee_id, DATE(timestamp)
),
walk_summary AS (
SELECT 
employee_id,
COUNT(DISTINCT walk_date) AS total_walk_days,
SUM(CASE WHEN walk_count > 3 THEN 1 ELSE 0 END) AS days_over_3_walks
FROM daily_walks
GROUP BY employee_id
),
walk_score AS (
SELECT 
employee_id,
CASE
WHEN total_walk_days > 70 AND days_over_3_walks > 25 THEN 3
WHEN total_walk_days > 60 THEN 1
ELSE 0
END AS score_walk
FROM walk_summary
),
flybook_avg AS (
SELECT 
employee_id,
AVG(daily_interaction) AS avg_interaction,
AVG(daily_messages) AS avg_messages,
AVG(daily_documents) AS avg_documents
FROM flybook_stats
WHERE stat_date >= '2025-01-31'
GROUP BY employee_id
),
flybook_overall_avg AS (
SELECT 
AVG(daily_interaction) AS overall_avg_interaction,
AVG(daily_messages) AS overall_avg_messages,
AVG(daily_documents) AS overall_avg_documents
FROM flybook_stats
WHERE stat_date >= '2025-01-31'
),
document_score AS (
SELECT 
fa.employee_id,
CASE
WHEN (fa.avg_interaction < foa.overall_avg_interaction AND
fa.avg_messages < foa.overall_avg_messages AND
fa.avg_documents < foa.overall_avg_documents) THEN 3
WHEN (CASE WHEN fa.avg_interaction < foa.overall_avg_interaction THEN 1 ELSE 0 END +
CASE WHEN fa.avg_messages < foa.overall_avg_messages THEN 1 ELSE 0 END +
CASE WHEN fa.avg_documents < foa.overall_avg_documents THEN 1 ELSE 0 END) = 2 THEN 1
ELSE 0
END AS score_document
FROM flybook_avg fa
CROSS JOIN flybook_overall_avg foa
)
SELECT 
e.id AS employee_id,
(COALESCE(ats.score_attendance, 0) + 
 COALESCE(cs.score_compensation, 0) + 
 COALESCE(ws.score_walk, 0) + 
 COALESCE(ds.score_document, 0)) AS score
FROM employees e
LEFT JOIN attendance_score ats ON e.id = ats.employee_id
LEFT JOIN compensation_score cs ON e.id = cs.employee_id
LEFT JOIN walk_score ws ON e.id = ws.employee_id
LEFT JOIN document_score ds ON e.id = ds.employee_id
2025-06-18 每个视频类型的T+3留存率  
with tmp1 as(
	select
		a.usr_id, 
		a.v_id, 
		b.v_typ, 
		date(a.v_tm) v_dt
	from bilibili_t20 a left join bilibili_t3 b on a.v_id = b.v_id
	where date(a.v_tm) between '2021-02-05' and '2021-02-08'
)
select
	a.v_typ, 
	count(distinct a.usr_id) total_views, 
	count(distinct b.usr_id) retained_users, 
	round(count(distinct b.usr_id) / count(distinct a.usr_id) *100, 2) retention_rate
from tmp1 a left join tmp1 b on a.usr_id = b.usr_idand datediff(b.v_dt, a.v_dt) between 1 and 3
where a.v_dt = '2021-02-05'
group by 1
order by 4 desc
2025-06-18 抖音面试真题（5）新用户的T+1月留存  
with tmp1 as(
	select 
		usr_id,
		date_format(min(login_time),'%Y-%m-01') reg_dt
	from user_login_log
	where login_time >= '2024-01-01' AND login_time < '2025-01-01'
	group by 1
),
tmp2 as(
	select 
		usr_id,
		date_format(login_time,'%Y-%m-01') dt
	from user_login_log
	where login_time >= '2024-01-01' AND login_time < '2025-01-01'
	group by 1,2
)
select
	a.reg_dt current_month	,
	round(count(b.dt)*100 / count(a.reg_dt),2) t_plus_1_month_retention_rate
from tmp1 a left join tmp2 b 
	on a.usr_id = b.usr_id 
	and date(a.reg_dt) = date_sub(b.dt,interval 1 month)
group by 1
order by 1
2025-06-18 抖音面试真题（5）新用户的T+1月留存  
with tmp as(
	select 
		usr_id,
		date_format(login_time,'%Y-%m-01') dt, 
		date_format(min(login_time)over(partition by usr_id),'%Y-%m-01') reg_dt
	from user_login_log
	wherelogin_time >= '2024-01-01' AND login_time < '2025-01-01'
)
select
	a.reg_dt current_month	,
	round(count(distinct b.usr_id)*100 / count(distinct a.usr_id),2) t_plus_1_month_retention_rate
from tmp a left join tmp b 
	on a.usr_id = b.usr_id 
	and date(a.reg_dt) = date_sub(b.dt,interval 1 month)
group by 1
order by 1
2025-06-18 抖音面试真题（5）新用户的T+1月留存  
with tmp as(
	select 
		usr_id,
		date_format(login_time,'%Y-%m-01') dt, 
		date_format(min(login_time)over(partition by usr_id),'%Y-%m-01') reg_dt
	from user_login_log
	wherelogin_time >= '2024-01-01' AND login_time < '2025-02-01'
)
select
	a.reg_dt current_month	,
	round(count(distinct b.usr_id)*100 / count(distinct a.usr_id),2) t_plus_1_month_retention_rate
from tmp a left join tmp b 
	on a.usr_id = b.usr_id 
	and date(a.reg_dt) = date_sub(b.dt,interval 1 month)
group by 1
order by 1
2025-06-18 抖音面试真题（5）新用户的T+1月留存  
with tmp as(
	select 
		usr_id,
		date_format(login_time,'%Y-%m-01') dt, 
		date_format(min(login_time)over(partition by usr_id),'%Y-%m-01') reg_dt
	from user_login_log
	wherelogin_time >= '2024-01-01' AND login_time < '2025-01-01'
), 
tmp2 as(
	select reg_dt, count(distinct usr_id) total_user
	from tmp
	group by 1
), 
rete as(
select
	reg_dt, 
	sum(case when min_gap = 1 then 1 else 0 end) rete
from(
	select
		usr_id, 
		reg_dt, 
		min(timestampdiff(month, reg_dt, dt)) min_gap
	from tmp 
	where timestampdiff(month, reg_dt, dt) <> 0
	group by 1, 2
	) t1
group by1
)
select 
	a.reg_dt current_month, 
	round(ifnull(b.rete / a.total_user *100, 0), 2) t_plus_1_month_retention_rate
from tmp2 a left join rete b on a.reg_dt = b.reg_dt
2025-06-18 抖音面试真题（5）新用户的T+1月留存  
with tmp1 as(
	select 
		usr_id,
		date_format(min(login_time),'%Y-%m-01') reg_dt
	from user_login_log
	where login_time >= '2024-01-01' AND login_time < '2025-01-01'
	group by 1
),
tmp2 as(
	select 
		usr_id,
		date_format(login_time,'%Y-%m-01') dt
	from user_login_log
	where login_time >= '2024-01-01' AND login_time < '2025-01-01'
	group by 1,2
)
select
	a.reg_dt current_month	,
	round(count(b.dt)*100 / count(a.reg_dt),2) t_plus_1_month_retention_rate
from tmp1 a left join tmp2 b 
	on a.usr_id = b.usr_id 
	and DATE_FORMAT(DATE_ADD(a.reg_dt, INTERVAL 1 MONTH), '%Y-%m-01') = b.dt
group by 1
order by 1