0

I have a table like this -

row_no Movie. movie_start_time movie_end_time
1 A 2021-02-01 01:00:00 2021-02-01 02:00:00
2 B 2021-02-01 01:00:00 2021-02-01 02:00:00
3 A 2021-02-01 01:30:00 2021-02-01 02:30:00
4 A 2021-02-01 01:30:00 2021-02-01 02:30:00
5 A 2021-02-01 02:15:00 2021-02-01 03:15:00
6 B 2021-02-01 02:15:00 2021-02-01 03:15:00
7 A 2021-02-01 04:15:00 2021-02-01 05:15:00

I want to add one additional column to the table which has difference between previous play time and current play time for the same movie. I also have a condition that previous play time and current play time shouldn't be overlapping. So in above scenario, results should look like below:

row_no Movie. movie_start_time movie_end_time. last_play
1 A 2021-02-01 01:00:00 2021-02-01 02:00:00 -
2 B 2021-02-01 01:00:00 2021-02-01 02:00:00 -
3 A 2021-02-01 01:30:00 2021-02-01 02:30:00 -
4 A 2021-02-01 01:30:00 2021-02-01 02:30:00 -
5 A 2021-02-01 02:15:00 2021-02-01 03:15:00 15 minutes
6 B 2021-02-01 02:15:00 2021-02-01 03:15:00 15 minutes
7 A 2021-02-01 04:15:00 2021-02-01 05:15:00 60 minutes

I tried writing below query for getting the previous movie_end_time so that I can calculate the difference later :

select movie, movie_start_time, movie_end_time, lag(movie_end_time) over (partition by movie order by movie_start_time) prev_end_time from table where prev_end_time <= movie_start_time

But this doesn't work since we can't add where clause with window functions. Is there any other way to solve this problem ?

Nayana M
  • 1
  • 1
  • `select * from () t where prev_end_time <= movie_start_time`? – GSerg Feb 24 '21 at 18:59
  • well are you sure the fifth row is correct? because it is overlapping with another movie A in the 4th row. – ARAT Feb 24 '21 at 20:52
  • @GSerg That won't work as lag will just give me immediate previous row, I need immediate previous row which satisfies the condition prev_end_time <= movie_start_time – Nayana M Feb 25 '21 at 05:19
  • @ARAT I think the example I chose was little confusing - 15 minutes on the 5th row is the difference from non-overlapping previous record - which is the 1st row – Nayana M Feb 25 '21 at 05:20

2 Answers2

0

So I used postgreSQL to create a table:

CREATE TABLE IF NOT EXISTS table1 (
  row_no INT,
  Movie CHAR,
  movie_start_time timestamp,
  movie_end_time timestamp
);

and insert the records into this table:

INSERT INTO table1 (row_no, Movie, movie_start_time, movie_end_time) VALUES(1, 'A', '2021-02-01 01:00:00', '2021-02-01 02:00:00');
INSERT INTO table1 (row_no, Movie, movie_start_time, movie_end_time) VALUES(2, 'B', '2021-02-01 01:00:00',  '2021-02-01 02:00:00');
INSERT INTO table1 (row_no, Movie, movie_start_time, movie_end_time) VALUES(3, 'A', '2021-02-01 01:30:00',  '2021-02-01 02:30:00');
INSERT INTO table1 (row_no, Movie, movie_start_time, movie_end_time) VALUES(4, 'A', '2021-02-01 01:30:00', '2021-02-01 02:30:00');
INSERT INTO table1 (row_no, Movie, movie_start_time, movie_end_time) VALUES(5, 'A', '2021-02-01 02:15:00', '2021-02-01 03:15:00');
INSERT INTO table1 (row_no, Movie, movie_start_time, movie_end_time) VALUES(6, 'B', '2021-02-01 02:15:00', '2021-02-01 03:15:00');
INSERT INTO table1 (row_no, Movie, movie_start_time, movie_end_time) VALUES(7, 'A', '2021-02-01 04:15:00', '2021-02-01 05:15:00');

Then what you want is:

select row_no, movie, movie_start_time, movie_end_time, EXTRACT(EPOCH FROM (movie_start_time - prev_end_time)::INTERVAL)/60 AS last_play FROM 
(select row_no,movie, movie_start_time, movie_end_time, lag(movie_end_time) over (partition by movie order by movie_start_time) AS "prev_end_time"
from table1) t
where prev_end_time <= movie_start_time
ORDER BY movie_start_time

enter image description here

ARAT
  • 884
  • 1
  • 14
  • 35
  • In your results, 5th row is missing from the expected result. You are just checking immediate previous record for that movie. But I need immediate previous record for the movie where prev_end_time <= movie_start_time. In the case of 5th row, the immediate previous record satisfying the condition would be 1st row – Nayana M Feb 25 '21 at 04:55
0

I was able to solve the problem with below query :

with a as 
(select row_no,movie, movie_start_time, movie_end_time, 
array_agg(movie_end_time) over (partition by movie order by movie_start_time
 rows between unbounded preceding and 1 preceding) AS prev_end_time from `table1`),

b as (select row_no,movie, movie_start_time, movie_end_time,
case 
when prev_end_time is null then null
else (select max(i_prev_end_time) from unnest(prev_end_time)i_prev_end_time 
where i_prev_end_time <= movie_start_time)
end previous_end_time from a)

select row_no,movie, movie_start_time, movie_end_time,
 unix_seconds(movie_start_time) - unix_seconds(previous_end_time) last_run from b
Nayana M
  • 1
  • 1