# Difference between revisions of "White Christmas"

## Contents

The [HadCET] data is "the longest available instrumental record of temperature in the world", currently available from the UK Met Office. It provides the daily mean temperature for the centre of England since 1772.

## Days, Months and Years

The units are 10th of a degree Celcius. The columns are yr and dy for year and day of month. The next twelve columns are for January through to December.

Show the average daily temperature for August 10th 1964

```SELECT m5/10 FROM hadcet
WHERE yr=1962 AND dy=20
```
```SELECT m8/10 FROM hadcet
WHERE yr=1964 AND dy=10
```

## preteen Dickens

Charles Dickens is said to be responsible for the tradition of expecting snow at Christmas Daily Telegraph. Show the temperature on Christmas day (25th December) for each year of his childhood. He was born in February 1812 - so he was 1 (more or less) in December 1812.

Show the twelve temperatures.

```SELECT yr-1811 as age FROM hadcet
WHERE yr BETWEEN 1812 and 1812+12 AND dy=25
```
```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

A White Christmas is possible if there was a day with an average temperature below zero between 21st and 25th of December.

For each age 1-12 show which years where potentially White Christmas.

```SELECT yr-1811 as age FROM hadcet
WHERE yr BETWEEN 1812 and 1812+12 AND dy=25
```
```SELECT age,
CASE WHEN MIN(m12)<0 THEN 'white' END
FROM
(SELECT yr-1811 as age,dy,m12 FROM hadcet
WHERE yr BETWEEN 1812 and 1812+11 AND dy BETWEEN 21 AND 25) AS dpc
GROUP BY age
```

## White Christmas Count

A person's White Christmas Count (wcc) is the number of White Christmases they were exposed to as a child (between 3 and 12 inclusive assuming they were born at the beginning of the year and were about 1 year old on their first Christmas).

Charles Dickens's wcc was 8.

List all the years and the wcc for children born in each year. Only show years where the wcc was at least 7.

```+------+-----+
| yob  | wcc |
+------+-----+
| 1805 |   7 |
| 1806 |   7 |
| 1807 |   7 |
| 1808 |   8 |
| 1809 |   9 |
| 1810 |   8 |
| 1811 |   8 |
| 1812 |   8 |
| 1813 |   7 |
+------+-----+

```
```SELECT yr-1811 as age FROM hadcet
WHERE yr BETWEEN 1812 and 1812+12 AND dy=25
```
```SELECT * FROM
(SELECT yob,COUNT(CASE WHEN mt<=0 THEN 1 END) AS wcc FROM
(SELECT yob,yr-yob-1 as age,MIN(m12) as mt
FROM hadcet JOIN (SELECT distinct yr as yob FROM hadcet WHERE yr BETWEEN 1700 and 2017) yrs
WHERE yr BETWEEN yob+2 and yob+11 AND dy BETWEEN 21 AND 25
GROUP BY yob,age) as yam
GROUP BY yob) as ywcc
WHERE wcc>=7
```