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.

What difficulty level is this interview question?

This is a medium difficulty Data Manipulation (SQL/Python) question, commonly asked during Technical Screen rounds at LinkedIn.

What role is this question designed for?

This question is commonly asked for Data Scientist candidates at LinkedIn during technical interviews.

Analyze video posting activity | LinkedIn Interview Question

Q: Analyze video posting activity

This question evaluates data manipulation skills such as joins, date comparisons, conditional grouping, and aggregation to compute member- and country-level metrics from relational tables within the Data Manipulation (SQL/Python) domain.

You are given two tables about members and video uploads.

Table 1: video_posts

post_date DATE — UTC calendar date of the upload
member_id INT
video_length_seconds INT

Table 2: members

member_id INT
country STRING
join_date DATE — UTC calendar date when the member joined

Relationship:

video_posts.member_id = members.member_id

Assumptions:

Each row in video_posts represents one uploaded video.
Dates are compared as UTC calendar dates.
If a member uploaded multiple videos on their earliest upload date, that date is still considered the member's first video date.

Write SQL (and optionally Python for part 2) for the following:

How many members posted their first-ever video on the same calendar date that they joined the platform?
- Output column: members_first_video_on_join_date
Compare total 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
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