Run Code
|
API
|
Code Wall
|
Misc
|
Feedback
|
Login
|
Theme
|
Privacy
|
Patreon
Weekly Average Starting on Different Days
create table events (uid text, event_date date); set datestyle = 'dmy'; insert into events (uid, event_date) values ('3fin2d','19/03/17'), ('2f4j34','20/03/17'); create table usage (uid text, usage_start timestamp, usage_end timestamp, duration interval); insert into usage (uid, usage_start, usage_end, duration) values ('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'); 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 ;
run
|
edit
|
history
|
help
0
xD
Current time/date, timezone
yordan
IF IN POSTGRESQL
user
tic tac toe
pokemo
3c
prec
SQL3