El promedio general

Parte de la sección Fundamentos del Journey de SQL de Coddy. Lección 56 de 72.

Desafío

Fácil

Tablas y columnas 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

Finalmente, calcula la calidad promedio para cada part_id.

Devuelve part_id y avg_quality, ordenados por avg_quality en orden descendente.

Pruébalo tú mismo

-- Estos cuatro bloques de productos y el bloque combinado se arrastran de la última lección
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
)

-- Reduce las filas combinadas a una fila por parte de producto, el promedio más grande primero
SELECT part_id, ____ as avg_quality
FROM quality_table
GROUP BY ____
ORDER BY ____