I'm trying to use postgres' RANGE clause within a window function to dynamically set the window to the past 4 weeks not including the current date. Here is my attempt at it:
SELECT date("aa0"."created_at") AS "Day of Created At",
"aa0"."user_id" AS "User Id",
count (*) activities_today,
count(*) over (PARTITION BY "aa0"."user_id"
ORDER BY date("aa0"."created_at") ASC
RANGE BETWEEN date("aa0"."created_at" - interval '4 weeks')
AND
date("aa0"."created_at" - interval '1 day')
) active_days_past_4_weeks
FROM "public"."activity_activity" AS "aa0"
GROUP BY date("aa0"."created_at"), "aa0"."user_id"
ORDER BY "Day of Created At" ASC LIMIT 1000;
However, I get the following error:
ERROR: syntax error at or near ")" Position: 459
If I use ROWS instead of RANGE it works but this is not the correct logic since it's not guaranteed that there will be a row for every day:
SELECT date("aa0"."created_at") AS "Day of Created At",
"aa0"."user_id" AS "User Id",
count(*) activities_today,
count(*) over (PARTITION BY "aa0"."user_id"
ORDER BY date("aa0"."created_at") ASC
ROWS BETWEEN 28 PRECEEDING AND 1 PRECEEDING
) active_days_past_4_weeks
FROM "public"."activity_activity" AS "aa0"
GROUP BY date("aa0"."created_at"), "aa0"."user_id"
ORDER BY "Day of Created At" ASC LIMIT 1000;
Schema:
CREATE TABLE activity_activity (
user_id int,
created_at timestamp
)
Version:
select version()
PostgreSQL 9.3.4 on x86_64-unknown-linux-gnu, compiled by gcc (Ubuntu 4.8.2-16ubuntu6) 4.8.2, 64-bit
