Skip to content

Instantly share code, notes, and snippets.

@malisper
Created November 27, 2017 01:50
Show Gist options
  • Save malisper/894f5fbd09d98af064edc8bfc7fbe8ac to your computer and use it in GitHub Desktop.
Save malisper/894f5fbd09d98af064edc8bfc7fbe8ac to your computer and use it in GitHub Desktop.
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",
'04nakresdpnf8' AS query_identifier,
group_field_0
FROM
(SELECT "multi_group_wrapper_1".*,
group_field_0,
FROM
(SELECT "events"."time",
"events"."user_id",
"events"."data"
FROM "events"
WHERE (((((((("events"."time" >= 1504335600000)
AND ("events"."time" <= 1504940400000))
AND ("events"."user_id" >= '(236035469, 0)'::APP_USER_ID))
AND ("events"."user_id" <= '(236035469, 9007199254740991)'::APP_USER_ID))
AND (("events"."data" ->> 'object') = 'pageview'))
AND ((("events"."data" ->> 'domain') = 'blog.heapanalytics.com')
OR (("events"."data" ->> 'domain') = 'heap.engineering')))
AND (("events"."data" ->> 'path') ILIKE '%'))
AND (("events"."data" ->> 'library') = 'web'))) AS "multi_group_wrapper_1"
LEFT JOIN
(SELECT "users"."id" AS "user_id",
normalize_referrer("users"."initial_referrer") AS "group_field_0"
FROM "users") "left_group_by_1" ON ((("left_group_by_1".user_id).app_id = ("multi_group_wrapper_1".user_id).app_id
AND ("left_group_by_1".user_id).user_id = ("multi_group_wrapper_1".user_id).user_id))) "eventQuery") "shardPushdown") "shardPushdown"
GROUP BY "time_bucket" ,
"group_field_0"
Sign up for free to join this conversation on GitHub. Already have an account? Sign in to comment