Instantly share code, notes, and snippets.

View gist:8a7990774e21143c32b4cacd7851d113
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
View query.sql
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",
View gist:894f5fbd09d98af064edc8bfc7fbe8ac
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",
View recursive_cte.py
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()
View gist:0fcacffff0942392dea305f828db78b6
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))
View gist:4d56f24726733b390d07d45b3d9c6883
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'
View gist:46635d7e3723c911e5f2ec9440908ced
(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
View gist:3a8594c96cea47471565be8d08be25f9
(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))
View gist:fcd5fda9741220355f6ce36a423c69cc
(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))
View gist:7227bc8c428949d8c1f277effca28d14
(5/2 11/5 3/2 3/17 13/57 13/11 29/247 323/29 23/13 37/23 43/37 31/23 31/53
2773/31 61/3149 61/43 73/4087 3551/73 71/61 83/71 97/83 79/71 79/103 1919/79
7169/101 109/6313 109/97 127/6431 6077/127 113/109 137/113 149/137 131/113
131/157 163/131 173/163 151/131 151/181 179/28841 179/173 197/34189 34571/197
193/179 211/193 227/211 199/193 199/233 54731/199 241/4351 241/227 257/4579
4427/257 251/241 269/251 277/269 263/251 263/283 53671/263 5567/281 307/70027
307/277 313/73373 67637/313 311/307 331/311 347/331 317/311 317/353
349/5206666620054784675760045562843307267 349/347
367/5732260726811103633565476029754934499
5797959990155643503291154838118887903/367 359/349 379/68569 73153/379 373/359