Skip to content

Files

Latest commit

 

History

History

Popular Posts

Folders and files

NameName
Last commit message
Last commit date

parent directory

..
 
 
 
 
 
 

Popular Posts

Star Badge View Main Folder View Repositories View My Profile


🛠️ Problem Statement

Table Name : post_views

Column Name Type
session_id int
post_id int
perc_viewed float

Table Name : user_sessions

Column Name Type
session_id int
user_id varchar(10)
session_starttime timestamp
session_endtime timestamp
platform varchar(20)

The column 'perc_viewed' in the table 'post_views' denotes the percentage of the session duration time the user spent viewing a post.
Write a SQL query to calculate the total time that each post was viewed by users. Output post ID and the total viewing time in seconds, but only for posts with a total viewing time of over 5 seconds.

Input

Table Name : post_views

session_id post_id perc_viewed
1 1 2
1 2 4
1 3 1
2 1 20
2 2 10
2 3 10
2 4 21
3 2 1
3 4 1
4 2 50
4 3 10
6 2 2
8 2 5
8 3 2.5

Table Name : user_sessions

session_id user_id session_starttime session_endtime platform
1 U1 2020-01-01 12:14:28 2020-01-01 12:16:08 Windows
2 U1 2020-01-01 18:23:50 2020-01-01 18:24:00 Windows
3 U1 2020-01-01 08:15:00 2020-01-01 08:20:00 IPhone
4 U2 2020-01-01 10:53:10 2020-01-01 10:53:30 IPhone
5 U2 2020-01-01 18:25:14 2020-01-01 18:27:53 IPhone
6 U2 2020-01-01 11:28:13 2020-01-01 11:31:33 Windows
7 U3 2020-01-01 06:46:20 2020-01-01 06:58:13 Android
8 U3 2020-01-01 10:53:10 2020-01-01 10:53:50 Android
9 U3 2020-01-01 13:13:13 2020-01-01 13:34:34 Windows
10 U4 2020-01-01 08:12:00 2020-01-01 12:23:11 Windows
11 U4 2020-01-01 21:54:03 2020-01-01 21:54:04 IPad
Output
post_id total_viewtime
4 5.1
2 24