SQLZOOのWhite Christmasを解いてみた

これまで取り組んできたSQLZOOの練習の集大成として、White Christmasを解いてみました。最後の問題がとくに難しかったです。

Days, Months and Years

1.

単位は摂氏10度です。yrとdyのカラムはそれぞれ西暦とその月の日付です。続く12のカラムは1月から12月のカラムです。1964年の8月10日の平均気温を表示してください。

SELECT
    m8/10
FROM hadcet
WHERE yr = 1964 AND dy = 10;

preteen Dickens

2.

Charles Dickens はクリスマスにに雪を期待する風習があると言っています。彼の幼少期の隔年の、クリスマス（12月25日）の気温を表示してください。彼は1812年の2月に生まれました。つまり彼は1812年の12月には（誤差があるかもしれませんが）1歳だったわけです。12歳までのクリスマスの気温を表示してください。

SELECT
    yr - 1811 AS age
    , m12 / 10 AS temp
FROM hadcet
WHERE yr BETWEEN 1812 AND 1812+11
AND dy =25;

Minimum Temperature Before Christmas

3.

12月21日から25日の間に平均気温が0度を下回る日があった場合、ホワイトクリスマスであるとされます。1~12歳の各年で、ホワイトクリスマスであった年を表示してください。各年についてWhite ChristmasかNo snowを表示してください

SELECT
    yr - 1811 AS age
    , CASE WHEN MIN(m12) <0 THEN 'White Christmas' ELSE 'No Snow' END
FROM hadcet
WHERE yr BETWEEN 1812 AND 1812+11
    AND dy BETWEEN 21 AND 25
GROUP BY yr;

White Christmas Count

4.

ある人のホワイトクリスマスカウント（wcc）を子供の時にホワイトクリスマスだった年の数だとします（年の初めに生まれて、初めてのクリスマスには1歳だったと仮定して、3〜12歳までを子供の時とします）。Charles Dickensのwccは8でした。データセットの各年に生まれた子供のwccと西暦を列挙してください。wccが7以上の年だけ表示してください。

SET SQL_BIG_SELECTS=1;
SELECT
    b_year AS yob
    , SUM(Is_snow) AS wcc
FROM
    (SELECT
        a.yr AS a_year
        ,b.yr AS b_year
        , MIN(a.m12)
        , MIN(b.m12)
        , CASE WHEN MIN(a.m12) < 0 THEN 1 ELSE 0 END AS Is_snow
    FROM hadcet AS a
        LEFT JOIN hadcet AS b ON a.yr BETWEEN b.yr+2 AND b.yr+11
    WHERE a.dy BETWEEN 21 AND 25
    GROUP BY b.yr, a.yr) AS temp
GROUP BY b_year
HAVING wcc >= 7;