allenday

overall structure

• resources (multiple audio files, video files, and other resources referenced in the sequence)
• project
  • sequence (a group of multiple assets that compose a single timeline)
    • spine' (a timeline. this is the OUTER spine in which everything else is contained)
      • marker
        • complete="0" is a TODO marker
      • video (ignore these)
      • asset-clip' (a segment of a video or audio file)
• spine (this is a floating timeline that contains one or more of...)

allenday

SELECT
  country_name, 
  COUNT(1) AS hectares, 
  SUM(population) AS total_pop, 
  SUM(population)/COUNT(1) AS pop_per_hectare
FROM
  `bigquery-public-data.geo_worldpop.WorldPop_GP_100m` AS pop
  ,`bigquery-public-data.country_codes.country_codes` AS codes
WHERE TRUE
  AND pop.country_code = codes.alpha_3_code

allenday

SELECT 
  ROUND((input_value - output_value)/ size, 0) AS fees_per_byte,
  COUNT(*) AS txn_cnt
FROM
  `bigquery-public-data.crypto_bitcoin.transactions`
WHERE TRUE
  AND block_timestamp >= '2018-01-01' 
  AND is_coinbase IS FALSE
GROUP BY 1

allenday

SELECT
	txn_count,
	COUNT(txn_count) AS num_addresses
FROM
(
	SELECT 
	  ARRAY_TO_STRING(inputs.addresses, '') AS addresses,
	  COUNT(DISTINCT `hash`) AS txn_count
	FROM `crypto-etl-ethereum-dev.bitcoin_blockchain.transactions` AS txns
	CROSS JOIN UNNEST(txns.inputs) AS inputs

allenday

allenday

CREATE TEMP FUNCTION term_years(term STRING)
RETURNS ARRAY<STRUCT<year INT64, term_frequency INT64>> AS (
  (
      SELECT years
      FROM `gcp-pdp-words-dev.sandbox.eng_bert_preprocessed_year_term_frequencies`
      WHERE preprocessed_term = term
  )
  --   SELECT ARRAY_AGG(STRUCT<year INT64, term_frequency INT64>(z.year, z.term_frequency + IFNULL(t.term_frequency, 0)))
  --   FROM 
  --   (

allenday

WITH double_entry_book AS (
    -- debits
    SELECT
     array_to_string(inputs.addresses, ",") as address
    , inputs.type
    , -inputs.value as value
    , block_timestamp
    FROM `bigquery-public-data.crypto_bitcoin.inputs` as inputs
 
    UNION ALL

allenday

WITH double_entry_book AS (
    -- debits
    SELECT
     array_to_string(inputs.addresses, ",") as address
    , inputs.type
    , -inputs.value as value
    FROM `bigquery-public-data.crypto_bitcoin.inputs` as inputs
 
    UNION ALL
 

allenday

( cmdpid=$BASHPID; (sleep 3600; kill $cmdpid) & exec ./profanity.x64 --matching 888888XXXXXXXXXXXXXXXXXXXXXXXXXXXX888888 | grep --line-buffered Score >> keys.txt )

allenday

#standardSQL
CREATE TEMPORARY FUNCTION geohashDecode(geohash STRING)
RETURNS STRUCT<bbox ARRAY<FLOAT64>, lat FLOAT64, lng FLOAT64> 
  LANGUAGE js AS """
    if (!geohash) return null;
    var base32 = '0123456789bcdefghjkmnpqrstuvwxyz';
    geohash = geohash.toLowerCase();
    var evenBit = true;
    var latMin =  -90, latMax =  90;
    var lonMin = -180, lonMax = 180;