Meta Interview Coding Question: Instagram College Students Visiting Search on the Day of Registration, by Country

Instagram College Students Visiting Search on the Day of Registration, by Country

Table: ig_users

This table has one row for every Instagram user.

Columns:

• user_id INT — The unique identifier of the Instagram user
• country STRING — Country of the user
• is_college BOOLEAN — Indicates whether a user is a college student
• reg_date DATE — Date that the user joined Instagram

Sample Rows:

user_id | country | is_college | reg_date
1012    | US      | TRUE       | 2019-02-02
7506    | JP      | FALSE      | 2015-04-07
3098    | IN      | TRUE       | 2017-12-05
8904    | US      | TRUE       | 2020-06-19

Table: ig_time_spent

This table has data on how users spend time on Instagram, aggregated per surface.

Columns:

• date DATE — The date that the user visited Instagram
• user_id INT — The unique identifier of the Instagram user
• surface STRING — Which surface the user visited
• time_spent DOUBLE — The amount of time (in seconds) spent on that surface

Sample Rows:

date       | user_id | surface  | time_spent
2020-12-02 | 1012    | profile  | 279.856
2020-12-02 | 1012    | stories  | 433.775
2020-12-02 | 3652    | search   | 68.233
2020-12-01 | 7506    | stories  | 342.190

By country, what percentage of all college students that joined Instagram on 2023-12-01 went to Search on the same day of registration?

This problem asks for a country-level conversion-style percentage: among college students who registered on 2023-12-01, compute how many visited the Search surface on the same day as registration. The standard approach is to filter users by registration date and college status, join to the time-spent table on user_id and matching date, then aggregate by country to calculate the ratio of users with a Search visit over all eligible registered college users.