全体平均

CoddyのSQLジャーニー「基礎」セクションの一部。レッスン 56/72。

チャレンジ

簡単

利用可能なテーブルと列：
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

最後に、各 part_id の平均品質を計算してください。

part_id と avg_quality を返し、avg_quality の降順で並べ替えてください。

自分で試してみよう

-- これら4つの製品ブロックと結合ブロックは前回のレッスンから引き継がれています
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
)

-- 結合された行を製品パーツごとに1行に減らし、平均が最も大きいものから順に
SELECT part_id, ____ as avg_quality
FROM quality_table
GROUP BY ____
ORDER BY ____