题解 | #统计每个学校的答过题的用户的平均答题数#

-- 每个学校用户数
SELECT
	UNIVERSITY ,
	COUNT(device_id)
FROM
	USER_PROFILE UP
GROUP BY
	UNIVERSITY ;
-- 每个学校答过题的用户数
SELECT
	UNIVERSITY ,
	COUNT(device_id)
FROM
	USER_PROFILE UP
WHERE
	DEVICE_ID IN (
	SELECT
		DISTINCT DEVICE_ID
	FROM
		QUESTION_PRACTICE_DETAIL)
GROUP BY
	UNIVERSITY;
-- 每个用户答题数
SELECT
	DEVICE_ID ,
	COUNT(QPD.QUESTION_ID)
FROM
	QUESTION_PRACTICE_DETAIL QPD
GROUP BY
	DEVICE_ID ;
-- 每个学校的用户
SELECT
	UNIVERSITY ,
	DEVICE_ID
FROM
	USER_PROFILE UP
ORDER BY
	UNIVERSITY ;
-- 每个学校答过题的用户
SELECT
	UNIVERSITY ,
	DEVICE_ID
FROM
	USER_PROFILE UP
WHERE
	DEVICE_ID IN (
	SELECT
		DISTINCT DEVICE_ID
	FROM
		QUESTION_PRACTICE_DETAIL)
ORDER BY
	UNIVERSITY
	-- 每个学校答过题的用户的答题数
SELECT
	UP.UNIVERSITY ,
	UP.DEVICE_ID,
	t1.qcnt
FROM
	USER_PROFILE UP,
	(
	SELECT
		DEVICE_ID ,
		COUNT(QPD.QUESTION_ID) qcnt
	FROM
		QUESTION_PRACTICE_DETAIL QPD
	GROUP BY
		DEVICE_ID) t1
WHERE
	UP.DEVICE_ID IN (
	SELECT
		DISTINCT DEVICE_ID
	FROM
		QUESTION_PRACTICE_DETAIL)
	AND UP.DEVICE_ID = t1.DEVICE_ID
ORDER BY
	UNIVERSITY;
-- 每个学校每个用户平均答题数
SELECT
	UP.UNIVERSITY ,
	-- UP.DEVICE_ID,
	AVG(t1.qcnt)
FROM
	user_profile UP,
	(
	SELECT
		DEVICE_ID ,
		COUNT(QPD.QUESTION_ID) qcnt
	FROM
		question_practice_detail QPD
	GROUP BY
		DEVICE_ID) t1
WHERE
	UP.DEVICE_ID IN (
	SELECT
		DISTINCT DEVICE_ID
	FROM
		question_practice_detail)
	AND UP.DEVICE_ID = t1.DEVICE_ID
GROUP BY
	UNIVERSITY
ORDER BY
	UNIVERSITY ;