Analyze Social Media Engagement with SQL Queries

info_stream_views

+----------+---------+-----------+-------------------------+-----------------------+------------+ | view_id | post_id | viewer_id | creator_connection_type | view_duration_seconds | view_date | +----------+---------+-----------+-------------------------+-----------------------+------------+ | 101 | 9001 | 501 | friend | 15 | 2023-09-18 | | 102 | 9001 | 777 | unconnected | 25 | 2023-09-18 | | 103 | 9002 | 502 | unconnected | 40 | 2023-09-19 | | 104 | 9003 | 501 | friend | 10 | 2023-09-20 | | 105 | 9002 | 888 | unconnected | 30 | 2023-09-20 | +----------+---------+-----------+-------------------------+-----------------------+------------+

​

post_action

+-----------+---------+-------------+----------+-------------+ | action_id | post_id | action_type | actor_id | action_date | +-----------+---------+-------------+----------+-------------+ | 5001 | 9001 | react | 501 | 2023-09-18 | | 5002 | 9002 | react | 777 | 2023-09-19 | | 5003 | 9002 | comment | 888 | 2023-09-19 | | 5004 | 9003 | react | 502 | 2023-09-20 | | 5005 | 9001 | share | 501 | 2023-09-18 | +-----------+---------+-------------+----------+-------------+

Scenario

A social-media analytics task using info_stream_views and post_action tables.

Question

Write a query to find the number of DISTINCT post_id that accumulated more than 60 seconds of total view_duration_seconds from unconnected viewers during the past 7 days. 2. Write a query to compute the average number of total reactions (action_type = 'react') received by (a) friend-created posts and (b) unconnected‐creator posts in the last 7 days.

Hints

Use date filtering on the last 7 days, group by post_id, and JOIN the two tables for reactions.