Skip to content

Instantly share code, notes, and snippets.

@randyzwitch

randyzwitch/dplyr-sql-generated.R

Created January 13, 2015 21:02
Show Gist options
  • Save randyzwitch/52d5a08bd4ef7ec4ce90 to your computer and use it in GitHub Desktop.
Save randyzwitch/52d5a08bd4ef7ec4ce90 to your computer and use it in GitHub Desktop.
Download ZIP
SQL generated by dplyr
Raw
dplyr-sql-generated.R
SELECT
"uid",
"event_timestamp",
"minutes_since_last_event",
"event_boundary",
"session_id"
FROM (
SELECT
"uid",
"event_timestamp",
"minutes_since_last_event",
CASE WHEN "minutes_since_last_event" > 30.0 THEN 1.0 ELSE 0.0 END AS "event_boundary",
sum(CASE WHEN "minutes_since_last_event" > 30.0 THEN 1.0 ELSE 0.0 END) OVER (PARTITION BY "uid" ORDER BY "event_timestamp" ROWS UNBOUNDED PRECEDING) AS "session_id"
FROM
(
SELECT
"uid",
"event_timestamp",
(DATE_PART('day', "event_timestamp" - LAG("event_timestamp", 1, NULL) OVER (PARTITION BY "uid" ORDER BY "event_timestamp")) * 24.0
+ DATE_PART('hour', "event_timestamp" - LAG("event_timestamp", 1, NULL) OVER (PARTITION BY "uid" ORDER BY "event_timestamp")) * 60.0
+ DATE_PART('minute', "event_timestamp" - LAG("event_timestamp", 1, NULL) OVER (PARTITION BY "uid" ORDER BY "event_timestamp")) * 60.0
+ DATE_PART('second', "event_timestamp" - LAG("event_timestamp", 1, NULL) OVER (PARTITION BY "uid" ORDER BY "event_timestamp"))) / 60.0 AS "minutes_since_last_event"
FROM "single_col_timestamp"
ORDER BY "uid", "event_timestamp"
) AS "_W1"
) AS "_W2"
Sign up for free to join this conversation on GitHub. Already have an account? Sign in to comment