Katkat
Back

Analyze member video posting behavior by country

LinkedInData Scientist · Technical Screen
EasyData ScientistTechnical ScreenData Manipulation (SQL/Python)

Last updated

A LinkedIn data scientist technical-screen question on member video-posting behavior using two relational tables. It tests joins, first-event derivation with MIN/GROUP BY, conditional grouping for US vs non-US comparison, and distinct-member counts per country with a length filter.

Question

You are given two tables describing LinkedIn members and the videos they upload. Write SQL (and optionally Python where noted) to answer the questions below.

Tables

members

  • member_id INT (PK)
  • country VARCHAR
  • join_date DATE — UTC calendar date when the member joined

video_posts

  • post_date DATE — UTC calendar date of the upload
  • member_id INT (FK → members.member_id)
  • video_length_seconds INT — length of the video in seconds

Relationship & assumptions

  • video_posts.member_id = members.member_id.
  • Each row in video_posts represents one uploaded video; a member may post multiple videos on different dates.
  • Dates are compared as UTC calendar dates.
  • A member's first-ever video date is the minimum post_date for that member. If a member uploaded multiple videos on that earliest date, that date is still considered the member's first video date.

Tasks

  1. How many members posted their first-ever video on the same calendar date they joined the platform?
  • Output column: members_first_video_on_join_date (a single row).
  1. Compare the total number of uploaded videos by US members versus non-US members.
  • Define member_region = 'US' when country = 'usa', otherwise member_region = 'Non-US'.
  • Output columns: member_region, total_video_posts.
  1. For each country, count how many distinct members have posted at least one video longer than 60 seconds.
  • Output columns: country, members_with_video_over_60s.