Just because I can.   Let's add something else too:

I love it, when I want to chronologically sort year & months formatted like 2016-1, 2016-2..2016-10 and the result is like: 2016-1, 2016-10, 2016-2. This needs to stop. with prep as (SELECT * ,cast(concat(cast(year as string), '-', case when length(cast(month as string)) = 1 then…

The table: Let's start with most popular name throughout all the years: SELECT A.*, B.* FROM `bigquery-public-data.usa_names.usa_1910_2013` A inner join (select name as name2, sum(number) as res from `bigquery-public-data.usa_names.usa_1910_2013` group by name order by sum(number) desc) B on…

Situation: You have a table with the ID of the customer and the name. In another table you have the ID of the customers and their offers. You want to see the number of offers each client has got. And by client, you want the names, not the ID. Example tables could look like this: with table1 as…