Analyze member video posting behavior by country

Q: Analyze member video posting behavior by country

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.

Q: How do I approach Data Manipulation (SQL/Python) interview questions?

Data Manipulation (SQL/Python) questions require understanding of core concepts and practice. PracHub provides solutions with explanations to help you master data manipulation (sql/python) interviews.

Question

You are given member profile data and video posting events.

Tables

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 member may post multiple videos on different dates.

Tasks

A) How many members posted their first-ever video on the same calendar day they joined?

Output: a single row with members_first_video_same_day .

C) For each country, how many distinct members have posted at least one video longer than 60 seconds?

Output columns: country , num_members_over_60s .

Notes

For “first-ever video”, define the first post as the minimum post_date per member; break ties using the smallest video_length (or state an alternative tie-break).

Analyze member video posting behavior by country

Quick Overview

Tables

Tasks

Notes

Comments (0)