This question evaluates a data scientist's data manipulation skills, covering SQL/Python competencies such as joins, aggregations, deduplication, tie-breaking for first-event identification, and group-level filtering by country.
You are given member profile data and video posting events.
members
memberid
INT (PK)
country
VARCHAR
join_date
DATE
video_posts
post_date
DATE
memberid
INT (FK →
members.memberid
)
video_length
INT — seconds
Assumptions:
A) How many members posted their first-ever video on the same calendar day they joined?
members_first_video_same_day
.
C) For each country, how many distinct members have posted at least one video longer than 60 seconds?
country
,
num_members_over_60s
.
post_date
per member; break ties using the smallest
video_length
(or state an alternative tie-break).