# Difference between revisions of "White Christmas"

## Contents

The table hadcet from the UK Met Office contains the average temperatures for central England since 1772, 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 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. 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 21 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
```