allenday

WITH city AS (
	SELECT
		layers.name as osm_name,
		layers.all_tags AS osm_tags,
		(SELECT tags.value FROM UNNEST(all_tags) as tags WHERE tags.key = 'admin_level') as admin_level,
		layers.geometry AS geometry
	FROM `openstreetmap-public-data-dev.osm_planet.osm_layers_partitions` AS layers
	WHERE layers.partnum = `openstreetmap-public-data-dev.osm_planet.name2partnum`('boundary-administrative')
		AND EXISTS(SELECT tags.value FROM UNNEST(all_tags) as tags WHERE tags.key = 'name' and tags.value='London')
		AND EXISTS(SELECT tags.value FROM UNNEST(all_tags) as tags WHERE tags.key = 'place' and tags.value='city')

allenday

WITH city AS (
	SELECT
		layers.name as osm_name,
		layers.all_tags AS osm_tags,
		(SELECT tags.value FROM UNNEST(all_tags) as tags WHERE tags.key = 'admin_level') as admin_level,
		layers.geometry AS geometry
	FROM `bigquery-public-data.geo_openstreetmap.layers` AS layers
	WHERE layers.partnum = `bigquery-public-data.geo_openstreetmap.name2partnum`('boundary-administrative')
		AND EXISTS(SELECT tags.value FROM UNNEST(all_tags) as tags WHERE tags.key = 'name' and tags.value='New York')
		AND EXISTS(SELECT tags.value FROM UNNEST(all_tags) as tags WHERE tags.key = 'place' and tags.value='city')

allenday

SELECT name, COUNT(name) AS c 
FROM `bigquery-public-data.geo_openstreetmap.layers` 
GROUP BY name ORDER BY c DESC

allenday

allenday

G---D---e---b---C---G---a---D---
G---D---e---b---C---G---A-G-D---

C---b-e-C---b-e-C---b-e-A---a-D-
C---b-e-C---b-e-C---b-e-a-A-a-D-

C---b-e-C---b-e-C---b-e-a-----D-
C---b-e-C---b-e-C---b-e-a-----D-

D-------a-G-D-----------a-D-----

allenday

#docker run -d -p 8080:8080 -v /root:/data   gcr.io/deeplearning-platform-release/tf-cpu.1-13
import os
import sys
import tempfile
import shutil
import numpy as np
np.set_printoptions(threshold=np.inf)
from keras.preprocessing.image import ImageDataGenerator, img_to_array, load_img
from keras.models import Sequential
from keras.layers import Dropout, Flatten, Dense

allenday

WITH 
z AS -- TODO better to use percentiles
(
  SELECT 
    tok.address,
    COUNT(tok.address) AS n,
    AVG(SAFE_CAST(tx.value AS NUMERIC)/POWER(10,CAST(tok.decimals AS NUMERIC))) AS mu,
    STDDEV(SAFE_CAST(tx.value AS NUMERIC)/POWER(10,CAST(tok.decimals AS NUMERIC))) AS sigma
  FROM `crypto-etl-ethereum-dev.crypto_ethereum.tokens` AS tok,
       `crypto-etl-ethereum-dev.crypto_ethereum.token_transfers` AS tx

allenday

SELECT CAST('2019-04-01' AS DATE) AS d, AVG(gas_used) AS gas 
FROM `bigquery-public-data.crypto_ethereum.blocks` 
WHERE TRUE
  AND timestamp >= TIMESTAMP('2019-04-01') 
  AND timestamp <= TIMESTAMP(DATE_ADD(CAST('2019-04-01' AS DATE), INTERVAL 1 DAY))
GROUP BY d

allenday

SELECT number, gas_used AS gas FROM `bigquery-public-data.crypto_ethereum.blocks` WHERE number = 7000000;

allenday

#standardSQL
--
-- The following query computes the Hardy-Weinberg equilibrium for variants.
--
WITH variants AS (
  SELECT reference_name, start_position, end_position, reference_bases, alt, 
  SUM(HOM_REF) AS HOM_REF,
  SUM(HOM_ALT) AS HOM_ALT,
  SUM(HET) AS HET
  FROM (