Start by clarifying the schema and the definition of 'unconnected user' (e.g., not in the user's network). Then write a SQL query that first deduplicates view events by user, post, and day using MAX(duration), filters for duration > 60 seconds and unconnected users, and finally counts distinct posts within the 7-day window.
Pro tip: Explicitly state your assumptions about the data model (e.g., how connections are represented) and mention that you would validate the deduplication logic with a sample query before finalizing. This shows you think about data quality and edge cases.
Ask about the table structures, how 'unconnected' is defined (e.g., via a connections table), and the exact 7-day window (e.g., rolling or fixed). Confirm that duration is in seconds and that reactions table is not needed for this specific count.
Use a subquery or CTE to group by user_id, post_id, and DATE(view_time), taking MAX(duration) to handle multiple views on the same day. This ensures each user-post-day combination is represented once with the longest duration.
Join with a connections table (or use a NOT EXISTS clause) to exclude users connected to the post author, and apply the condition duration > 60. Be careful with NULLs and ensure the join logic correctly identifies unconnected users.
Restrict the filtered events to the last 7 days (e.g., WHERE view_date >= CURRENT_DATE - INTERVAL '7 days') and then count distinct post_id. Use COUNT(DISTINCT post_id) to get the final metric.
Check for edge cases (e.g., users with no connections, posts with no views) and consider indexing on view_time, user_id, and post_id for performance. Optionally, discuss how to handle time zones if relevant.
AI-generated suggestions, not part of the candidate's original notes. May be inaccurate — verify before relying on them.
This one is genuinely hard to get right in one pass.
First, deduplicate views by selecting the row with the highest duration for each (post_id, viewer_id, ds) combination. Then, left join reactions to the deduplicated views on post_id, viewer_id, and ds, ensuring all distinct posts from views are included. Finally, compute the average reactions per post for each viewer group (Friend vs Unconnected) by dividing total reactions by the count of distinct posts in views for that group.
Pro tip: Clarify the definition of 'Friend vs Unconnected'—likely based on a relationship flag in the views table—and confirm that the denominator counts distinct posts, not view events, to avoid inflating the average.
Use a window function (e.g., ROW_NUMBER() OVER (PARTITION BY post_id, viewer_id, ds ORDER BY duration DESC)) to select the view row with the highest duration for each unique combination.
Join the deduplicated views to the reactions table on post_id, viewer_id, and ds, ensuring that all view rows are retained even if there are no matching reactions.
Group by viewer group (Friend vs Unconnected) and compute the total number of reactions (e.g., COUNT(reaction_id)) and the count of distinct posts from views (e.g., COUNT(DISTINCT post_id)).
Divide the total reactions by the distinct post count for each group to get the average reactions per post.
AI-generated suggestions, not part of the candidate's original notes. May be inaccurate — verify before relying on them.