【SQLPlanet】共享单车运维团队面试题

表1：emp

表2：attend

1.上周每天分城市分职务的在岗人数和出勤率
出勤率=当天有效出勤人数/当天在岗人数
（出勤会有多次编辑，相同出勤ID按gmt_modify降序排列，取最新1条）

Sql语句：

SELECT
	e.pt,
	e.city_name,
	e.group_type,
	COUNT(e.emp_id) AS num,
	round(
		COUNT(b.id) / COUNT(e.emp_id),
		2
	) AS attend_rate
FROM
	emp AS e
LEFT JOIN (
	SELECT
		*
	FROM
		(
			SELECT
				*, row_number () over (PARTITION BY id ORDER BY gmt_modify DESC) AS sort
			FROM
				attend
			ORDER BY
				id
		) AS a
	WHERE
		a.sort = '1'
	AND a.attend_status = '1'
) AS b ON (
	DATE_FORMAT(e.pt, '%Y%m%d') = b.a_date
	AND e.emp_id = b.emp_id
	AND b.a_date BETWEEN '20210329'
	AND '20210404'
)
WHERE
	e.entry_status = '1'
GROUP BY
	e.pt,
	e.city_name,
	e.group_type;

2.找出上周连续出勤5天以上的人员ID和人员姓名，以及第5天出勤的日期（出勤会有多次编辑，按gmt_modify降序排列取最新1条记录）

Sql语句：

SELECT
	d.emp_id,
	d.emp_name,
	d.ad5
FROM
	(
		SELECT
			c.emp_id,
			c.emp_name,
			c.label_date,
			COUNT(*) AS count_day,
			DATE_ADD(c.label_date, INTERVAL 5 DAY) AS ad5
		FROM
			(
				SELECT
					*, DATE_SUB(DATE_FORMAT(b.a_date, '%Y%m%d'), INTERVAL b.rn DAY) AS label_date
				FROM
					(
						SELECT
							*, row_number () over (PARTITION BY a.emp_id ORDER BY a.a_date) AS rn
						FROM
							(
								SELECT
									*, row_number () over (PARTITION BY id ORDER BY gmt_modify DESC) AS sort
								FROM
									attend
								ORDER BY
									id
							) AS a
						WHERE
							a.sort = '1'
						AND a.attend_status = '1'
						AND a.a_date BETWEEN '20210329'
						AND '20210404'
					) AS b
			) AS c
		GROUP BY
			c.emp_id,
			c.label_date
	) AS d
WHERE
	d.count_day >= '5';