SQL Questions asked in Meta.

Meta Business Intelligence Analyst Interview Experience:

CTC - 40 LPA

Here are 5 questions asked in meta.

1: Average Post Hiatus

Given a table of Facebook posts, for each user who posted at least twice in 2024, write a SQL query to find the number of days between each user’s first post of the year and last post of the year in the year 2024. Output the user and number of days between each user’s first and last post.
• posts table: user_id (integer), post_id (integer), post_date (timestamp), post_content (text)

2. Facebook Power Users

A Facebook power user is defined as someone who posts a ton and gets a lot of reactions on their post. For the purpose of this question, consider a Facebook power user as someone who posts at least twice a day and receives an average of 150 comments and/or reactions per post. Write a SQL query to return the IDs of all Facebook power users, along with the number of posts, and the average number of reactions per post.
• user_post table: user_id (integer), post_id (integer), post_date (timestamp)
• post_interactions table: post_id (integer), comments (integer), reactions (integer)

3. Active User Retention

Assume you’re given a table containing information on Facebook user actions. Write a SQL query to obtain the number of monthly active users (MAUs) in July 2022, including the month in numerical format “1, 2, 3”. Hint: An active user is defined as a user who has performed actions such as ‘sign-in’, ‘like’, or ‘comment’ in both the current month and the previous month.
• user_actions table: user_id (integer), event_id (integer), event_type (string), event_date (datetime)

4. Facebook Friend Recommendations

Facebook wants to recommend new friends to people who show interest in attending 2 or more of the same private Facebook events. Write a SQL query to find pairs of friends to be recommended to each other if they’re interested in attending 2 or more of the same private events.
Notes:
• A user interested in attending would have either ‘going’ or ‘maybe’ as their attendance status.
• Friend recommendations are unidirectional, meaning if user x and user y should be recommended to each other, the result table should have both user x recommended to user y and user y recommended to user x.
• The result should not contain duplicates (i.e., user y should not be recommended to user x multiple times).
• friendship_status table: user_a_id (integer), user_b_id (integer), status (enum: ‘friends’, ‘not_friends’)
• event_rsvp table: user_id (integer), event_id (integer), event_type (enum: ‘public’, ‘private’), attendance_status (enum: ‘going’, ‘maybe’, ‘not_going’), event_date (date)

5. Average Number of Shares per Post

As a data analyst at Facebook, you are asked to find the average number of shares per post for each user.
• user_posts table: post_id (integer), user_id (integer), post_text (text), post_date (timestamp)
• post_shares table: share_id (integer), post_id (integer), share_date (timestamp).