The Overall Average

Fait partie de la section Les fondamentaux du Journey SQL de Coddy — leçon 56 sur 72.

Défi

Facile

Tables et colonnes disponibles :
enom_gilpane: part_id, rpm, watt, size
castle_loctus: part_id, rpm, watt, size
honpan_bilopsa: part_id, rpm, watt, size
yurnol_qoltam: part_id, rpm, watt, size

Enfin, calculez la qualité moyenne pour chaque part_id.

Retournez part_id avg_quality, triés par avg_quality en ordre décroissant.

Essayez vous-même

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