Skip to content

Instantly share code, notes, and snippets.

View malisper's full-sized avatar

Michael Malis malisper

View GitHub Profile
@malisper
malisper / eval.sql
Created July 25, 2019 20:15
Programming Language Interpreter in SQL
-- Sum list of numbers
[["lambda", ["f"],
["f", "f",
["cons", 1, ["cons", 2, ["cons", 3, ["empty"]]]]]],
["lambda", ["self", "xs"],
["if", ["=", "xs", ["empty"]],
0,
["+", ["head", "xs"],
["self", "self", ["tail", "xs"]]]]]]
@malisper
malisper / gist:2d6ec4077f3097559b2206663ae39bec
Created February 24, 2019 18:36
SQL Queries that Produce Fractals
-- Sierpinski's Triangle.
WITH points AS (
SELECT ROW, col FROM generate_series(0, 63) a(ROW)
CROSS JOIN generate_series(0, 63) b(col)
ORDER BY ROW DESC, col ASC
), marked_points AS (
SELECT ROW, col, (CASE WHEN ROW & col != 0
THEN ' '
ELSE '*'
END) AS marker
@malisper
malisper / gist:8a7990774e21143c32b4cacd7851d113
Created July 1, 2018 05:27
Investigating Performance Issue
0:00:00-0:01:00 Thinking
0:01:00-0:03:15 Wrote query to get 100 queries after a given point in time that took a long time.
0:03:15-0:03:30 I try running the query and get an error. There's an issue with how I'm using TIMESTAMP AT TIME ZONE in Postgres.
0:03:30-0:04:15 I look up the docs for TIMESTAMP. I discover the issue is I wrote TIMESTAMP AT TIME ZONE instead of TIMESTAMP WITH TIME ZONE. I then run the query and it works.
0:04:15-0:05:45 I scroll through the results and look at them.
0:05:45-0:06:45 I modify the query I have to display how much time is spent in different parts of our stack. I'm looking at how much time was spent in our master database compared to our worker databases.
0:06:45-0:07:00 I get a few errors from ambigious fields in the SQL query. This is because I'm joining two tables that both have columns named start_time and both have columns named data. I modify the SQL query to refer explicitly to one of the tables.
0:07:00-07:30:00 The query I wrote is taking a long time. I kill it and t
@malisper
malisper / query.sql
Last active November 27, 2017 03:13
Query
SELECT "time_bucket",
COUNT(*) AS value ,
"group_field_0"
FROM
(SELECT DISTINCT ON ("time_bucket", "shardPushdown"."user_id", "group_field_0")
date_trunc('day', to_timestamp(("shardPushdown"."time" / 1000)) AT TIME ZONE 'America/Los_Angeles') AS "time_bucket",
"group_field_0"
FROM
(SELECT "eventQuery"."user_id",
"eventQuery"."time",
SELECT "time_bucket",
COUNT(*) AS value ,
"group_field_0"
FROM
(SELECT DISTINCT ON ("time_bucket",
"shardPushdown"."user_id",
"group_field_0") date_trunc('day', to_timestamp(("shardPushdown"."time" / 1000)) AT TIME ZONE 'America/Los_Angeles') AS "time_bucket",
"group_field_0"
FROM
(SELECT "eventQuery"."user_id",
def base_query():
return [1]
def recursive_query(ints):
return [n+1 for n in ints if n+1 <= 100]
def recursive_cte()
results = []
working_table = base_query()
Aggregate
-> GroupAggregate
Group Key: events.user_id
-> Append
-> Index Scan using events_pi_40542511 on events
Index Cond: ((time >= 1476428400000) AND (time <= 1477033200000))
-> Index Scan using events_pi_40542828 on events
Index Cond: ((time >= 1476428400000) AND (time <= 1477033200000))
SELECT
SUM(funnel_events(
ARRAY_AGG(funnel_step ORDER BY time ASC, funnel_step DESC),
2)) AS funnel_results
FROM ((SELECT *
FROM (SELECT user_id,
time,
0 AS funnel_step
FROM "events"
WHERE type = 'click'
(2/5 7/51874849202 337186519813/7 3/2 19/33 253/19 17/3 31/17 41/31 29/17 29/23
47/29 59/47 43/29 43/41 4087/43 71/4087 79/71 89/79 73/71 73/59 101/8833
12463/101 97/73 109/97 127/109 107/97 107/103 137/107 149/137 131/107 131/127
23707/131 163/23707 173/163 89/173 167/163 167/149 191/222277 256883/191
181/167 199/181 223/199 197/181 197/193 229/197 239/229 227/197 227/223
60491/227 257/60491 269/257 89/269 263/257 263/239 281/3850583 4143403/281
277/263 307/277 313/307 293/277 293/283 331/293 347/331 317/293 317/313
123197/317 359/123197 373/359 89/373 367/359 367/347 389/59105717 63937247/389
383/367 409/383 421/409 401/383 401/397 433/401 443/433 431/401 431/421
205193/431 461/205193 467/461 89/467 463/461 463/443 491/820232743
(defun multiple (a b)
"Is A a multiple of B?"
(= (mod a b) 0))
(defun next-fraction (n fractions)
"Returns the next value of F in the Fractran program."
(find-if (lambda (f)
(integerp (* n f)))
fractions))