Straightforward filter-and-count but I initially forgot to filter on the relationship column and just wrote a WHERE on duration and ds.
First, clarify the schema and definitions (e.g., what 'unconnected' means, how viewing time is recorded). Then, write a SQL query that filters rows to the past 7 days and unconnected viewers, aggregates total viewing time per post, and counts distinct posts where the total exceeds 60 seconds.
Pro tip: Mention that you would verify the grain of the table (e.g., one row per view session) and consider using a subquery or CTE to first calculate total watch time per post, which makes the query more readable and avoids potential aggregation errors.
Ask clarifying questions about the definition of 'unconnected viewers' (e.g., is there a connection_type column?), how viewing time is stored (e.g., watch_time_seconds per row), and the exact time window (e.g., last 7 days from today).
Use a WHERE clause to restrict to the past 7 days (e.g., view_date >= CURRENT_DATE - INTERVAL '7 days') and to unconnected viewers (e.g., connection_type = 'unconnected').
Group by post_id and sum the viewing time (e.g., SUM(watch_time_seconds)) to get total viewing time per post.
Apply a HAVING clause to keep only posts with total viewing time > 60 seconds, then count the distinct post_ids.
Combine steps into a single query, possibly using a subquery or CTE. Discuss handling of NULLs, timezone, and whether 'viewing time' might be spread across multiple rows per viewer.
AI-generated suggestions, not part of the candidate's original notes. May be inaccurate — verify before relying on them.
First, clarify the grain of the views and reactions tables and how they join (likely on post_id and viewer_id). Then, filter both tables to the last 7 days, join them, and compute the average reactions per post separately for friend and unconnected viewers, ensuring you handle posts with zero reactions appropriately.
Pro tip: Explicitly state your assumption about the join key and whether you're counting distinct posts or all view events; this shows you understand the data model and avoids double-counting.
Identify the columns in the views and reactions tables, especially the post identifier, viewer identifier, and viewer type (friend/unconnected). Confirm the join key (e.g., post_id and viewer_id) and the time column for filtering.
Apply a date filter to both tables to include only records from the past 7 days, using the appropriate timestamp column (e.g., view_time, reaction_time).
Perform a LEFT JOIN from views to reactions on post_id and viewer_id to retain all views, even those without reactions, so that zero-reaction views are counted.
Group by post_id and viewer_type (friend/unconnected), count reactions (or sum a reaction indicator), and compute the average reactions per post for each viewer type.
If the question asks for the average across all posts, aggregate the per-post averages or compute directly: total reactions divided by total distinct posts, separately for friend and unconnected viewers.
AI-generated suggestions, not part of the candidate's original notes. May be inaccurate — verify before relying on them.