Calculate Recent Post Views and Reactions for Social Media

info_stream_views +---------+-----------+--------------+----------+------------+ | post_id | viewer_id | relationship | duration | ds | +---------+-----------+--------------+----------+------------+ | 101 | 2001 | Friend | 45.7 | 2023-10-01 | | 102 | 2002 | Unconnected | 67.2 | 2023-10-01 | | 103 | 2003 | Followee | 12.0 | 2023-10-02 | | 104 | 2004 | Unconnected | 80.5 | 2023-10-02 | | 105 | 2005 | Friend | 30.0 | 2023-10-03 | +---------+-----------+--------------+----------+------------+ ​ post_reactions +---------+-----------+-------------+------------+ | post_id | viewer_id | post_action | ds | +---------+-----------+-------------+------------+ | 101 | 2001 | like | 2023-10-01 | | 101 | 2002 | comment | 2023-10-01 | | 102 | 2003 | reshare | 2023-10-02 | | 103 | 2001 | like | 2023-10-02 | | 104 | 2004 | like | 2023-10-03 | +---------+-----------+-------------+------------+ ##### Scenario Info stream engagement analytics for a social media platform; need recent metrics about post views and reactions. ##### Question Write a SQL query to find the number of distinct posts that accumulated at least one view longer than 60 seconds from viewers whose relationship to the author is 'Unconnected' during the last 7 days (relative to today). Write a SQL query to compute the average number of reactions (like, comment, reshare) per post separately for 'Friend' viewers and 'Unconnected' viewers over the last 7 days. ##### Hints Filter last 7 days, use relationship filter, group/aggregate by post and relationship.