Skip to content

Instantly share code, notes, and snippets.

View RyanOthnielKearns's full-sized avatar
🦜
Parroting, stochastically

Ryan Othniel Kearns RyanOthnielKearns

🦜
Parroting, stochastically
10 followers · 6 following
View GitHub Profile
@RyanOthnielKearns
RyanOthnielKearns / exoplanets-table.txt
Created January 28, 2021 01:37
PRAGMA TABLE_INFO(EXOPLANETS);
$ sqlite3 EXOPLANETS.db
sqlite> PRAGMA TABLE_INFO(EXOPLANETS);
0 | _id | TEXT | 0 | | 0
1 | distance | REAL | 0 | | 0
2 | g | REAL | 0 | | 0
3 | orbital_period | REAL | 0 | | 0
4 | avg_temp | REAL | 0 | | 0
5 | date_added | TEXT | 0 | | 0
@RyanOthnielKearns
RyanOthnielKearns / rolling-avg-detections.csv
Created January 28, 2021 00:57
Rolling Average Detections
DATE_ADDED AVG_TEMP_NULL_RATE TWO_WEEK_ROLLING_AVG
2020-03-09 0.967391304347826 0.436077995611105
2020-06-02 0.929411764705882 0.441299602441599
2020-06-03 0.977011494252874 0.47913211475687
2020-06-04 0.989690721649485 0.515566041654715
2020-06-07 0.987804878048781 0.554753033524633
2020-06-08 0.961904761904762 0.594966974173356
@RyanOthnielKearns
RyanOthnielKearns / only-start-date-dist-detections.csv
Created January 28, 2021 00:54
Only Start Date Distribution Detections
DATE_ADDED AVG_TEMP_NULL_RATE
2020-03-09 0.967391304347826
2020-06-02 0.929411764705882
2020-06-07 0.987804878048781
@RyanOthnielKearns
RyanOthnielKearns / naive-dist-detections.csv
Created January 28, 2021 00:52
Naive Distribution Detections
DATE_ADDED AVG_TEMP_NULL_RATE
2020-03-09 0.967391304347826
2020-06-02 0.929411764705882
2020-06-03 0.977011494252874
2020-06-04 0.989690721649485
2020-06-07 0.987804878048781
2020-06-08 0.961904761904762
@RyanOthnielKearns
RyanOthnielKearns / null-rates-query-results.csv
Created January 28, 2021 00:49
Null Rates query results
date_added DISTANCE_NULL_RATE G_NULL_RATE ORBITAL_PERIOD_NULL_RATE AVG_TEMP_NULL_RATE
2020-01-01 0.0833333333333333 0.178571428571429 0.214285714285714 0.380952380952381
2020-01-02 0.0 0.152173913043478 0.326086956521739 0.402173913043478
2020-01-03 0.0594059405940594 0.188118811881188 0.237623762376238 0.336633663366337
2020-01-04 0.0490196078431373 0.117647058823529 0.264705882352941 0.490196078431373
... ... ... ... ...
2020-07-13 0.0892857142857143 0.160714285714286 0.285714285714286 0.357142857142857
2020-07-14 0.0673076923076923 0.125 0.269230769230769 0.394230769230769
2020-07-15 0.0636363636363636 0.118181818181818 0.245454545454545 0.490909090909091
2020-07-16 0.058252427184466 0.145631067961165 0.262135922330097 0.466019417475728
@RyanOthnielKearns
RyanOthnielKearns / freshness-detections.csv
Created January 28, 2021 00:43
Freshness Detections
DATE_ADDED DAYS_SINCE_LAST_UPDATE
2020–02–08 8
2020–03–30 4
2020–05–14 8
2020–06–07 3
2020–06–17 5
2020–06–30 3
@RyanOthnielKearns
RyanOthnielKearns / days-since-last-update-query-results.csv
Created January 28, 2021 00:38
Days Since Last Update query results
DATE_ADDED DAYS_SINCE_LAST_UPDATE
2020–01–01
2020–01–02 1
2020–01–03 1
2020–01–04 1
2020–01–05 1
... ...
2020–07–14 1
2020–07–15 1
2020–07–16 1
@RyanOthnielKearns
RyanOthnielKearns / rows-added-query-results.csv
Last active January 28, 2021 00:37
Rows Added query results
date_added ROWS_ADDED
2020-01-01 84
2020-01-02 92
2020-01-03 101
2020-01-04 102
2020-01-05 100
... ...
2020-07-14 104
2020-07-15 110
2020-07-16 103
@RyanOthnielKearns
RyanOthnielKearns / top-5-from-exoplanets.csv
Last active January 28, 2021 00:34
SELECT * FROM EXOPLANETS LIMIT 5;
_id distance g orbital_period avg_temp date_added
c168b188-ef0c-4d6a-8cb2-f473d4154bdb 34.6273036348341 476.480044083599 2020-01-01
e7b56e84-41f4-4e62-b078-01b076cea369 110.196919810563 2.52507362359066 839.8378167897 2020-01-01
a27030a0-e4b4-4bd7-8d24-5435ed86b395 26.6957950454452 10.2764970016067 301.018816321399 2020-01-01
54f9cf85-eae9-4f29-b665-855357a14375 54.8883521129783 173.788967912197 328.644125249613 2020-01-01
4d06ec88-f5c8-4d03-91ef-7493a12cd89e 153.264217159834 0.922874568459221 200.712661803056 2020-01-01
@RyanOthnielKearns
RyanOthnielKearns / schema-change-detector.sql
Created January 19, 2021 23:51
WITH CHANGES AS(
SELECT
DATE,
SCHEMA AS NEW_SCHEMA,
LAG(SCHEMA) OVER(ORDER BY DATE) AS PAST_SCHEMA
FROM
EXOPLANETS_SCHEMA
)
SELECT