题解 | #统计每个学校的答过题的用户的平均答题数#
统计每个学校的答过题的用户的平均答题数
http://www.nowcoder.com/practice/88aa923a9a674253b861a8fa56bac8e5
-- 每个学校用户数
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 ;