Compute Effective Reads with SQL and Python Streaming

post_read_events

+----------+---------+-----------+---------+--------------------+---------------------+---------------------+
| event_id | user_id | session_id| post_id | watch_duration_sec | screen_coverage_pct | event_timestamp     |
+----------+---------+-----------+---------+--------------------+---------------------+---------------------+
| 1        | 101     | s1        | 501     | 2.5                | 75                  | 2023-06-01 10:00:01 |
| 2        | 101     | s1        | 501     | 3.0                | 80                  | 2023-06-01 10:00:05 |
| 3        | 102     | s2        | 502     | 6.0                | 65                  | 2023-06-01 10:01:09 |
| 4        | 101     | s3        | 503     | 1.5                | 90                  | 2023-06-01 11:15:21 |
| 5        | 102     | s2        | 502     | 4.0                | 70                  | 2023-06-01 10:02:15 |
+----------+---------+-----------+---------+--------------------+---------------------+---------------------+

Scenario

Event-level log of post impressions for a newsfeed; need to compute effective reads in batch (SQL) and in near-real-time (Python streaming).

Question

Write a SQL query that returns every post_id whose total watch_duration_sec across all views exceeds X seconds AND whose maximum screen_coverage_pct across those views exceeds Y.

Using the same data consumed as a stream, write Python code that groups events into sessions (end a session if no event arrives for ≥30 s), then emits, for each session, the posts that satisfy the same effective-read criteria along with their total watch time and max screen coverage.

Hints

Window functions or GROUP BY for SQL; for Python, think generators, stateful dictionaries, and a session timeout watermark.

Posts Exceeding Watch Duration and Screen Coverage Thresholds

You are given an event-level log of newsfeed post impressions in the table post_read_events. Each row is a single impression event with its watch duration (watch_duration_sec) and the percentage of the screen the post occupied (screen_coverage_pct).

Write a PostgreSQL query that finds every post_id that satisfies both of the following conditions across all of its impression events:

1. The total watch duration, SUM(watch_duration_sec), is greater than 5 seconds, AND
2. The maximum screen coverage, MAX(screen_coverage_pct), is greater than 70 percent.

(Both comparisons are strict >, so a post whose maximum coverage is exactly 70 does not qualify.)

Output: one row per qualifying post, with a single column post_id, sorted by post_id ascending.

Table post_read_events

column	type	constraints
event_id	INTEGER	PRIMARY KEY, NOT NULL
user_id	INTEGER	NOT NULL
session_id	VARCHAR(20)	NOT NULL
post_id	INTEGER	NOT NULL
watch_duration_sec	DECIMAL(6,2)	NOT NULL, CHECK (watch_duration_sec >= 0)
screen_coverage_pct	INTEGER	NOT NULL, CHECK (screen_coverage_pct BETWEEN 0 AND 100)
event_timestamp	TIMESTAMP	NOT NULL

Sample data:

event_id	event_timestamp	post_id	screen_coverage_pct	session_id	user_id	watch_duration_sec
1	2023-06-01 10:00:01	501	75	s1	101	2.5
2	2023-06-01 10:00:05	501	80	s1	101	3
3	2023-06-01 10:01:09	502	65	s2	102	6
4	2023-06-01 11:15:21	503	90	s3	101	1.5
5	2023-06-01 10:02:15	502	70	s2	102	4
6	2023-06-01 12:00:00	504	95	s4	103	5.5
7	2023-06-01 12:05:00	504	60	s5	104	2

Expected output

One row per post_id where SUM(watch_duration_sec) > 5 AND MAX(screen_coverage_pct) > 70, ordered by post_id. With the sample data: post 501 (total 5.5s, max coverage 80) and post 504 (total 7.5s, max coverage 95) qualify.

post_id
501
504

Hints

1. Collapse the event log to one row per post with GROUP BY post_id before applying any thresholds.
2. Use SUM(watch_duration_sec) for total watch time and MAX(screen_coverage_pct) for peak coverage, then filter both with a single HAVING clause.
3. The thresholds are strict (>), so a post whose max coverage is exactly 70 must be excluded.