Analyze member video posting behavior by country
Company: LinkedIn
Role: Data Scientist
Category: Data Manipulation (SQL/Python)
Difficulty: easy
Interview Round: Technical Screen
##### 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).
2. 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`.
3. 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`.
Overview: 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.
Community answers
Answer by anirbanb1984
WITH t1 AS
(
SELECT member_id, MIN(post_date) AS min_dt FROM video_posts
GROUP BY 1
)
SELECT COUNT(DISTINCT a.member_id) AS num_members FROM members a
INNER JOIN t1 b
ON a.member_id = b.member_id AND a.join_date = b.min_dt
WITH t1 AS
(
SELECT member_id, SUM(CASE WHEN video_length > 60 THEN 1 ELSE 0 END) AS num_videos_more_than_60s
FROM video_posts
GROUP BY 1
HAVING SUM(CASE WHEN video_length > 60 THEN 1 ELSE 0 END) > 1
)
SELECT a.country, COUNT(DISTINCT a.member_id) AS num_members
FROM members a
INNER JOIN t1 b
ON a.member_id = b.member_id
GROUP BY 1
Answer by prernamanwani2
select count(distinct m.member_id) as members_first_video_on_join_date
from members m join (select member_id, min(post_date) as first_video_date from video_posts group by 1) as p
on m.member_id = p.member_id
where m.join_date = p.first_video_date;
select (case when m. country = 'US' then 'US' else 'Non-US' end) as country,
count(*) as total_video_posts
from members m left join video_posts v
on m.member_id = v.member_id
group by 1;
select country, count(distinct member_id) as count_members
from members m join
(
select member_id from video_posts where video_length_seconds > 60
) p
on m.member_id = p.member_id
group by 1;
Answer by mlbro
with q1 AS (select member_id, min(post_date) as first_post_date from video_posts group by 1),
select count(m.member_id) from members m join q1 on m.member_id=q1.member_id and m.join_date=q1.first_post_date;