Aden Forshaw AdenForshaw

## create-or-replace-bigquery-table-with-partition.sql

create or replace table my_dataset.my_new_partitioned_table PARTITION BY date_column
as
  (
    select *
    from my_dataset.my_table
   )


## buigquery-angle-of-intersection.sql
SELECT
  gislib.sp.ST_InitialBearing( ST_GeogPoint(45,35), ST_GeogPoint(135,35) ),
  gislib.sp.ST_InitialBearing( ST_GeogPoint(-94.58, 39.1), ST_GeogPoint(-90.2, 38.63) )

## percentiles-in-bigquery.sql


select
  percentiles[offset(10)] as p10,
  percentiles[offset(25)] as p25,
  percentiles[offset(50)] as p50,
  percentiles[offset(75)] as p75,
  percentiles[offset(90)] as p90,
from (
  select approx_quantiles(char_length(text), 100) percentiles

## bigquery-use-parameterised-query.sql
DECLARE ORG_NAME STRING;
SET ORG_NAME = "AFL";
EXECUTE IMMEDIATE """
WITH x as
(
select
     country
   , category
   , subcategory
   , organisation

## first_last_date_current_month_bigquery.sql
How to find FIRST_DAY, LAST_DAY of current month, next month, previous month in Google Bigquery

Using #Standard SQL

First day of the current month:
```SQL
SELECT DATE_TRUNC(CURRENT_DATE(), MONTH)
```

Last day of the current month (first day next month minus 1):

## bigquery-row-for-each-date-in-range.sql
with dates as (


  SELECT day,
  FORMAT_DATE('%A', day) AS dow
  FROM UNNEST(
      GENERATE_DATE_ARRAY(DATE('2020-02-01'), DATE('2020-02-29'), INTERVAL 1 DAY)
  ) AS day

)

## handler.js
var https = require('https');
var util = require('util');

const SLACK_URI     = process.env.SLACK_URI || 'XXXX/XXXX/XXXXXXXXXXXXXXXX'
const SLACK_CHANNEL =  process.env.SLACK_CHANNEL || 'YOUR-SLACK-CHANNEL'

exports.handler = async (event, context) => {

    console.log(JSON.stringify(event, null, 2));

## Service downtime report - template
Executive summary
- brief paragraph that anyone can understand & apology for those affected.
The rest of the report should be purely factual with no emotion & or blame.

Issue Summary
- short summary (5 sentences)
- list the duration along with start and end times (include timezone)
- state the impact (most user requests resulted in 500 errors, at peak 100%)
- close with root cause
	SELECT
	gislib.sp.ST_InitialBearing( ST_GeogPoint(45,35), ST_GeogPoint(135,35) ),
	gislib.sp.ST_InitialBearing( ST_GeogPoint(-94.58, 39.1), ST_GeogPoint(-90.2, 38.63) )


	select
	percentiles[offset(10)] as p10,
	percentiles[offset(25)] as p25,
	percentiles[offset(50)] as p50,
	percentiles[offset(75)] as p75,
	percentiles[offset(90)] as p90,
	from (
	select approx_quantiles(char_length(text), 100) percentiles
	DECLARE ORG_NAME STRING;
	SET ORG_NAME = "AFL";
	EXECUTE IMMEDIATE """
	WITH x as
	(
	select
	country
	, category
	, subcategory
	, organisation
	How to find FIRST_DAY, LAST_DAY of current month, next month, previous month in Google Bigquery

	Using #Standard SQL

	First day of the current month:
	```SQL
	SELECT DATE_TRUNC(CURRENT_DATE(), MONTH)
	```

	Last day of the current month (first day next month minus 1):
	with dates as (


	SELECT day,
	FORMAT_DATE('%A', day) AS dow
	FROM UNNEST(
	GENERATE_DATE_ARRAY(DATE('2020-02-01'), DATE('2020-02-29'), INTERVAL 1 DAY)
	) AS day

	)
	var https = require('https');
	var util = require('util');

	const SLACK_URI = process.env.SLACK_URI \|\| 'XXXX/XXXX/XXXXXXXXXXXXXXXX'
	const SLACK_CHANNEL = process.env.SLACK_CHANNEL \|\| 'YOUR-SLACK-CHANNEL'

	exports.handler = async (event, context) => {

	console.log(JSON.stringify(event, null, 2));
	Executive summary
	- brief paragraph that anyone can understand & apology for those affected.
	The rest of the report should be purely factual with no emotion & or blame.

	Issue Summary
	- short summary (5 sentences)
	- list the duration along with start and end times (include timezone)
	- state the impact (most user requests resulted in 500 errors, at peak 100%)
	- close with root cause