Skip to content

Instantly share code, notes, and snippets.

View AdenForshaw's full-sized avatar

Aden Forshaw AdenForshaw

View GitHub Profile
@AdenForshaw
AdenForshaw / create-or-replace-bigquery-table-with-partition.sql
Created August 17, 2021 01:50
Create or Replace BigQuery table with partition
create or replace table my_dataset.my_new_partitioned_table PARTITION BY date_column
as
(
select *
from my_dataset.my_table
)
@AdenForshaw
AdenForshaw / buigquery-angle-of-intersection.sql
Created July 5, 2021 09:05
Bigquery angle of intersection
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) )
@AdenForshaw
AdenForshaw / percentiles-in-bigquery.sql
Created February 4, 2021 21:52
[Percentiles in BigQuery]
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
@AdenForshaw
AdenForshaw / bigquery-use-parameterised-query.sql
Created January 25, 2021 08:25
[BigQuery using parameterised query in the console]
DECLARE ORG_NAME STRING;
SET ORG_NAME = "AFL";
EXECUTE IMMEDIATE """
WITH x as
(
select
country
, category
, subcategory
, organisation
@AdenForshaw
AdenForshaw / first_last_date_current_month_bigquery.sql
Created January 8, 2021 00:17
[how to find first and last date of last, current and next month in BigQuery]
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):
@AdenForshaw
AdenForshaw / bigquery-row-for-each-date-in-range.sql
Last active September 8, 2020 03:54
[BigQuery row for each date in range] Generates a row for every date in a range with day of week #bigquery
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
)
@AdenForshaw
AdenForshaw / handler.js
Created October 8, 2019 03:07
AWS Lambda function to post SES mail updates from SNS and an SES configuration set
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