I need to calculate the weekly average of app usage duration (each usage has it's own log with the duration). The problem is that I would like to compare the averages before one certain event took place and after (different date for each user). So if the event took place on Tuesday one month ago, I would like to calculate weekly averages starting on Tuesdays before that specific Tuesday and after.
Relevant data look as follows: EVENTS
User ID || date of event
3fin2d..|| 19/03/17
2f4j34..|| 20/03/17
USAGE
UID || timestamp start || timestamp end || Duration
3fin2d.. || 11/03/17 11:20:00 || 11/03/17 12:00:00 || 00:40
3fin2d.. || 18/03/17 11:20:00 || 18/03/17 12:00:00 || 00:40
2f4j34.. || 19/03/17 18:20:00 || 19/03/17 18:40:00 || 00:20
2f4j34.. || 19/03/17 19:20:00 || 19/03/17 20:00:00 || 00:40
3fin2d.. || 19/03/17 19:30:00 || 19/03/17 20:00:00 || 00:30
2f4j34.. || 20/03/17 19:20:00 || 20/03/17 20:00:00 || 00:40
edit: USERS
UID || Created On
3fin2d.. || 11/03/17 11:00:00
2f4j34.. || 18/03/17 13:00:00
Expected Result:
UID ||Average Duration before even||Average Duration After event
3fin2d.. || 00:40:00 || 00:30:00
2f4j34.. || 01:00:00 || 00:40:00
of course weeks with 0 usage should be considered. In the above example I assume current date is 20/03/17 (otherwise many more zeros should be counted).
Thank you
http://rextester.com/USBJ20378
select uid,
avg(u.duration) filter (where usage_start < e.event_date) as before,
avg(U.duration) filter (where usage_start >= e.event_date) as after
from
usage u
inner join
events e using (uid)
where usage_start between e.event_date - 7 and e.event_date + 7
group by uid
;
uid | before | after
--------+----------+----------
2f4j34 | 00:30:00 | 00:40:00
3fin2d | 00:40:00 | 00:30:00