La moyenne globale

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 dans l’ordre décroissant.

Essayez vous-même

-- Ces quatre blocs de produits et le bloc combiné sont repris de la dernière leçon
WITH filtered_enom_gilpane_quality as (
    WITH enom_gilpane_quality as (
        SELECT part_id, ((rpm * watt) / size) as quality
        FROM (
            SELECT part_id, rpm, watt + (select avg(watt) from enom_gilpane) as watt, size
            FROM enom_gilpane
        )
    )

    SELECT *
    FROM enom_gilpane_quality
    WHERE quality > (select avg(quality) from enom_gilpane_quality)
),
filtered_castle_loctus_quality as (
    WITH castle_loctus_quality as (
        SELECT part_id, ((rpm * watt) / size) as quality
        FROM (
            SELECT part_id, rpm, watt + (select avg(watt) from castle_loctus) as watt, size
            FROM castle_loctus
        )
    )

    SELECT *
    FROM castle_loctus_quality
    WHERE quality > (select avg(quality) from castle_loctus_quality)
),
filtered_honpan_bilopsa_quality as (
    WITH honpan_bilopsa_quality as (
        SELECT part_id, ((rpm * watt) / size) as quality
        FROM (
            SELECT part_id, rpm, watt + (select avg(watt) from honpan_bilopsa) as watt, size
            FROM honpan_bilopsa
        )
    )

    SELECT *
    FROM honpan_bilopsa_quality
    WHERE quality > (select avg(quality) from honpan_bilopsa_quality)
),
filtered_yurnol_qoltam_quality as (
    WITH yurnol_qoltam_quality as (
        SELECT part_id, ((rpm * watt) / size) as quality
        FROM (
            SELECT part_id, rpm, watt + (select avg(watt) from yurnol_qoltam) as watt, size
            FROM yurnol_qoltam
        )
    )

    SELECT *
    FROM yurnol_qoltam_quality
    WHERE quality > (select avg(quality) from yurnol_qoltam_quality)
),
quality_table as (
    SELECT * FROM filtered_enom_gilpane_quality
    UNION ALL
    SELECT * FROM filtered_castle_loctus_quality
    UNION ALL
    SELECT * FROM filtered_honpan_bilopsa_quality
    UNION ALL
    SELECT * FROM filtered_yurnol_qoltam_quality
)

-- Réduire les lignes combinées à une ligne par pièce de produit, la plus grande moyenne en premier
SELECT part_id, ____ as avg_quality
FROM quality_table
GROUP BY ____
ORDER BY ____