Skip to content

Instantly share code, notes, and snippets.

View malisper's full-sized avatar

Michael Malis malisper

View GitHub Profile
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))
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()
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'
@malisper
malisper / gist:fcd5fda9741220355f6ce36a423c69cc
Last active November 1, 2017 00:48
Lisptran source code
(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))
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",
@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",
@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: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 / 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