paslandau

-- Using expression subqueries to query nested and repeated fields in Google BigQuery; 2020-05-29
-- @see http://www.pascallandau.com/bigquery-snippets/expression-subqueries-for-nested-repeated-fields/
WITH example as (
  SELECT 
    1 as id,
    [
      STRUCT("foo" as key, "foo 1" as value),
      STRUCT("bar" as key, "bar 1" as value)
    ] AS data,
  UNION ALL 

paslandau

# Calculate the MEDIAN in BigQuery; standard-sql; 2020-06-20
# @see http://www.pascallandau.com/bigquery-snippets/calculate-median/
WITH data as (
  SELECT
    1 as id,
    "2020-06-20" as day,
    10 as quantity
  UNION ALL SELECT 2,"2020-06-20", 15
  UNION ALL SELECT 1,"2020-06-21",5
  UNION ALL SELECT 2,"2020-06-21",10

paslandau

# Monitor Query costs in BigQuery; standard-sql; 2020-06-21
# @see http://www.pascallandau.com/bigquery-snippets/monitor-query-costs/

DECLARE timezone STRING DEFAULT "Europe/Berlin";
DECLARE gb_divisor INT64 DEFAULT 1024*1024*1024;
DECLARE tb_divisor INT64 DEFAULT gb_divisor*1024;
DECLARE cost_per_tb_in_dollar INT64 DEFAULT 5;
DECLARE cost_factor FLOAT64 DEFAULT cost_per_tb_in_dollar / tb_divisor;

SELECT