Difference between revisions of "White Christmas"

From SQLZOO
Jump to: navigation, search
(preteen Dickens)
Line 1: Line 1:
==hadcet==
+
==HadCET==
 +
The [[http://www.metoffice.gov.uk/hadobs/hadcet/ 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==
 
<div class='qu'>
 
<div class='qu'>
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 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.
 
The columns are yr and dy for year and day of month. The next twelve columns are for January through to December.
<p class='imper'>Show the average temperature for August 10th 1964</p>
+
<p class='imper'>Show the average daily temperature for August 10th 1964</p>
  
 
<source lang='sql' class='def'>
 
<source lang='sql' class='def'>

Revision as of 11:24, 14 March 2017

HadCET

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