This question evaluates competency in relational data manipulation and analytics, including join semantics, date-based event identification, aggregation and grouping with inclusion of zero-count members, and distinct-member counting for conditional filters.
You are given two tables about user video uploads.
video_posts(post_date DATE, member_id INT, video_length INT)
video_length
is measured in seconds.
members(member_id INT, country VARCHAR, join_date DATE)
Join key: members.member_id = video_posts.member_id.
Assume post_date and join_date are calendar dates in the same timezone.
Tasks:
'usa'
vs
'non-usa'
). Include members with zero uploads in the grouping; they contribute 0 posts.
Definitions:
post_date
in
video_posts
for that member.
Required output:
members_first_video_same_day
country_group, total_video_posts
country, num_members_with_long_video