Calculate Product Quality

Coddy SQL 여정의 기초 섹션에 포함된 레슨 — 72개 중 53번째.

챌린지

쉬움

사용 가능한 테이블 및 컬럼:
enom_gilpane: part_id, rpm, watt, size

다음 공식을 사용하여 각 부품의 품질을 계산하세요: 품질 = (rpm * adjusted_watt) / size.

adjusted_watt를 사용하고 있다는 점에 주목하세요.

part_id와 quality를 반환하세요

직접 해보기

SELECT part_id, AVG(quality) AS quality
FROM (
    SELECT part_id, AVG(quality) AS quality
    FROM (
        SELECT part_id, (rpm * (watt + (SELECT AVG(watt) FROM enom_gilpane)) / size) as quality
        FROM enom_gilpane
    )
    GROUP BY part_id
    HAVING quality > AVG(quality)
    UNION ALL
    SELECT part_id, AVG(quality) AS quality
    FROM (
        SELECT part_id, (rpm * (watt + (SELECT AVG(watt) FROM castle_loctus)) / size) as quality
        FROM castle_loctus
    )
    GROUP BY part_id
    HAVING quality > AVG(quality)
    UNION ALL
    SELECT part_id, AVG(quality) AS quality
    FROM (
        SELECT part_id, (rpm * (watt + (SELECT AVG(watt) FROM honpan_bilopsa)) / size) as quality
        FROM honpan_bilopsa
    )
    GROUP BY part_id
    HAVING quality > AVG(quality)
    UNION ALL
    SELECT part_id, AVG(quality) AS quality
    FROM (
        SELECT part_id, (rpm * (watt + (SELECT AVG(watt) FROM yurnol_qoltam)) / size) as quality
        FROM yurnol_qoltam
    )
    GROUP BY part_id
    HAVING quality > AVG(quality)
)
GROUP BY part_id
ORDER BY quality DESC