Skip to content

Instantly share code, notes, and snippets.

View view_balances.sql
CREATE TEMP FUNCTION
KECCAK256(data STRING)
RETURNS STRING
LANGUAGE js AS """
var utf8 = unescape(encodeURIComponent(data));
var arr = [];
for (var i = 0; i < utf8.length; i++) {
arr.push(utf8.charCodeAt(i));
}
return ethers.utils.keccak256(arr)
@allenday
allenday / query.sql
Created Jul 7, 2022
balancer new pool bigquery example with ethers.js and ethjs-abi.js
View query.sql
CREATE TEMP FUNCTION
KECCAK256(data STRING)
RETURNS STRING
LANGUAGE js AS """
var utf8 = unescape(encodeURIComponent(data));
var arr = [];
for (var i = 0; i < utf8.length; i++) {
arr.push(utf8.charCodeAt(i));
}
return ethers.utils.keccak256(arr)
View mnist-train.ipynb
Sorry, something went wrong. Reload?
Sorry, we cannot display this file.
Sorry, this file is invalid so it cannot be displayed.
View polygon-irenedao-votes.sql
CREATE TEMP FUNCTION
hex64ToInt64(hex STRING)
RETURNS INT64 AS (
IF(hex < "8000000000000000",
cast(concat("0x", hex) AS INT64),
(SELECT (((ms32 & 0x7fffffff) << 32) | ls32) - 0x7fffffffffffffff - 1
FROM (SELECT cast(concat("0x", substr(hex, 1, 8)) AS INT64) AS ms32,
cast(concat("0x", substr(hex, 9, 8)) AS INT64) AS ls32))));
CREATE TEMP FUNCTION
KECCAK(data STRING)
View find-similar-ngrams.sql
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
allenday / worldpop_density.sql
Created Sep 17, 2021
worldpop population density per country 2020
View worldpop_density.sql
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
allenday / profanity.sh
Created Jul 13, 2021
run profanity for x sec to generate a vanity key, then exit
View profanity.sh
( cmdpid=$BASHPID; (sleep 3600; kill $cmdpid) & exec ./profanity.x64 --matching 888888XXXXXXXXXXXXXXXXXXXXXXXXXXXX888888 | grep --line-buffered Score >> keys.txt )
@allenday
allenday / geohash-precipitation-noaa_gsod.sql
Created Jul 3, 2021
get total precipitation within a date range for a geohash polygon
View geohash-precipitation-noaa_gsod.sql
#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;
@allenday
allenday / bq-geohash-udf.sql
Created Jul 2, 2021 — forked from vladaman/bq-geohash-udf.sql
Google BigQuery standardSQL UDF function to decode geohash string into bounding box and coordinates
View bq-geohash-udf.sql
#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;
@allenday
allenday / download-worldpop.sh
Created Mar 27, 2021
earth-engine-to-bigquery
View download-worldpop.sh
#!/bin/sh
CREDENTIALS="credentials.json"
COLLECTION="projects/earthengine-public/assets/WorldPop/GP/100m/pop"
YEAR=2020
export "GOOGLE_APPLICATION_CREDENTIALS=$CREDENTIALS"
IMAGES=$(ogrinfo -ro -al "EEDA:" -oo "COLLECTION=$COLLECTION" -where "year=$YEAR" | grep 'gdal_dataset (String) = ' | cut -d '=' -f2 | tr -d ' ')
for IMAGE in $IMAGES
do