Calculate Engagement Metrics for Info-Stream Content Analysis

info_stream_views

+----------+-----------+--------------+----------+------------+
| post_id  | viewer_id | relationship | duration | ds         |
+----------+-----------+--------------+----------+------------+
| 101      | 9001      | Friend       | 85.4     | 2023-09-18 |
| 102      | 9002      | Unconnected  | 63.0     | 2023-09-19 |
| 103      | 9003      | Followee     | 12.0     | 2023-09-20 |
| 104      | 9004      | Unconnected  | 70.1     | 2023-09-20 |
+----------+-----------+--------------+----------+------------+

post_reactions

+----------+-----------+-------------+------------+
| post_id  | viewer_id | post_action | ds         |
+----------+-----------+-------------+------------+
| 101      | 9001      | like        | 2023-09-18 |
| 102      | 9002      | comment     | 2023-09-19 |
| 102      | 9003      | like        | 2023-09-19 |
| 104      | 9004      | reshare     | 2023-09-20 |
+----------+-----------+-------------+------------+

Scenario

Analyst must write SQL/Python to understand engagement with info-stream content over the last week.

Question

Using the tables below, write a query to compute the number of DISTINCT posts that received more than 60 seconds of viewing time from unconnected audiences in the past 7 days. Write a second query that returns the average number of reactions per post coming from (a) friends and (b) unconnected viewers during the past 7 days.

Hints

Filter ds for last 7 days, group by relationship, count distinct post_id for first query, aggregate reactions and divide by distinct posts for second.

First active and last inactive date per user

Given a table of user activity statuses by date, return for each user the earliest date they were recorded as active and the most recent date they were recorded as inactive.

Table activity

column	type	constraints
id	INTEGER
date	DATE
active_status	VARCHAR(20)

Sample data:

active_status	date	id
active	2023-01-01	1
inactive	2023-01-10	1
active	2023-02-01	1
inactive	2023-01-05	2
active	2023-03-20	2

Expected output

For each user ID, show the earliest date with active status and the most recent date with inactive status.

first_active_date	id	last_inactive_date
2023-01-01	1	2023-01-10
2023-03-20	2	2023-01-05

Hints

1. Use conditional aggregation with CASE expressions inside MIN and MAX.
2. Group by id to compute dates per user.