Interview Guides

Meta Messenger Signup Funnel | Dataford Interview Questions - Dataford - Ace your Interview

Meta Messenger Signup Funnel

Easy

SQL & Data Manipulation

Asked at 3 companies3

Also asked at

Problem

Task

Meta wants to measure a simple Messenger onboarding funnel: signup → first message sent → 7-day active. Write a SQL query to calculate, by signup date, how many users reached each stage.

A user counts as:

signed_up if they appear in users
sent_first_message if they sent at least one message on or after signup
active_7d if they had at least one activity event exactly 7 days after signup

Return one row per signup_date with counts for each funnel stage.

Requirements

Group results by signup_date
Count distinct users at each funnel stage
A message sent before signup should not count
A 7-day active event must occur on signup_date + 7 days
Order output by signup_date

Table Definitions

`users`

column	type	description
user_id	INT	Unique user identifier
signup_ts	TIMESTAMP	Account signup timestamp
signup_surface	VARCHAR(50)	Meta signup entry point

`messages`

column	type	description
message_id	INT	Unique message identifier
sender_id	INT	User who sent the message
sent_ts	TIMESTAMP	Message send timestamp
thread_type	VARCHAR(30)	Conversation type

`activity_events`

column	type	description
event_id	INT	Unique activity event identifier
user_id	INT	Active user
event_ts	TIMESTAMP	Activity timestamp
event_name	VARCHAR(50)	Activity type

Sample Data

`users`

user_id	signup_ts	signup_surface
101	2024-01-01 09:00:00	Facebook
102	2024-01-01 15:30:00	Instagram
103	2024-01-02 08:00:00	Facebook
104	2024-01-02 11:00:00	Messenger

`messages`

message_id	sender_id	sent_ts	thread_type
201	101	2024-01-01 10:00:00	direct
202	102	2023-12-31 23:00:00	group
203	103	2024-01-03 09:00:00	direct
204	104	2024-01-02 12:00:00	direct

`activity_events`

event_id	user_id	event_ts	event_name
301	101	2024-01-08 08:00:00	app_open
302	102	2024-01-08 16:00:00	app_open
303	103	2024-01-09 07:30:00	send_message
304	104	2024-01-10 09:00:00	app_open

Expected Output

signup_date	signed_up_users	users_sent_first_message	users_active_7d
2024-01-01	2	1	2
2024-01-02	2	2	1

Schema

users

Column	Type	Description
user_idPK	INT	Unique user identifier
signup_ts	TIMESTAMP	Timestamp when the user signed up
signup_surface	VARCHAR(50)	Meta surface where signup started

messages

Column	Type	Description
message_idPK	INT	Unique message identifier
sender_id	INT	User who sent the message
sent_ts	TIMESTAMP	Timestamp when the message was sent
thread_type	VARCHAR(30)	Conversation type

activity_events

Column	Type	Description
event_idPK	INT	Unique activity event identifier
user_id	INT	User who generated the activity event
event_ts	TIMESTAMP	Timestamp of the activity event
event_name	VARCHAR(50)	Type of activity event

Sample Data

Example 1

Input

{"users":[["101","2024-01-02 09:00:00","Facebook"],["102","2024-01-01 15:30:00","Instagram"],["103","2024-01-03 08:00:00","Messenger"],["104","2024-01-02 11:00:00","Facebook"],["105","2024-01-01 07:45:00","Messenger"],["106","2024-01-03 20:00:00","Instagram"],["107","2024-01-02 23:50:00",null],["108","2024-01-01 00:05:00","Facebook"],["109","2024-01-03 10:15:00","Messenger"],["110","2024-01-02 14:20:00","Instagram"],["111","2024-01-06 02:26:26","Gaming"],["112","2023-12-30 08:03:27","Facebook"],

Output

[["2023-12-29","4","0","0"],["2023-12-30","4","0","0"],["2024-01-01","3","3","2"],["2024-01-02","5","3","3"],["2024-01-03","3","1","2"],["2024-01-04","3","0","0"],["2024-01-05","2","0","0"],["2024-01-06","1","0","0"]]

Problem

Task

Meta wants to measure a simple Messenger onboarding funnel: signup → first message sent → 7-day active. Write a SQL query to calculate, by signup date, how many users reached each stage.

A user counts as:

signed_up if they appear in users
sent_first_message if they sent at least one message on or after signup
active_7d if they had at least one activity event exactly 7 days after signup

Return one row per signup_date with counts for each funnel stage.

Requirements

Group results by signup_date
Count distinct users at each funnel stage
A message sent before signup should not count
A 7-day active event must occur on signup_date + 7 days
Order output by signup_date

Table Definitions

`users`

column	type	description
user_id	INT	Unique user identifier
signup_ts	TIMESTAMP	Account signup timestamp
signup_surface	VARCHAR(50)	Meta signup entry point

`messages`

column	type	description
message_id	INT	Unique message identifier
sender_id	INT	User who sent the message
sent_ts	TIMESTAMP	Message send timestamp
thread_type	VARCHAR(30)	Conversation type

`activity_events`

column	type	description
event_id	INT	Unique activity event identifier
user_id	INT	Active user
event_ts	TIMESTAMP	Activity timestamp
event_name	VARCHAR(50)	Activity type

Sample Data

`users`

user_id	signup_ts	signup_surface
101	2024-01-01 09:00:00	Facebook
102	2024-01-01 15:30:00	Instagram
103	2024-01-02 08:00:00	Facebook
104	2024-01-02 11:00:00	Messenger

`messages`

message_id	sender_id	sent_ts	thread_type
201	101	2024-01-01 10:00:00	direct
202	102	2023-12-31 23:00:00	group
203	103	2024-01-03 09:00:00	direct
204	104	2024-01-02 12:00:00	direct

`activity_events`

event_id	user_id	event_ts	event_name
301	101	2024-01-08 08:00:00	app_open
302	102	2024-01-08 16:00:00	app_open
303	103	2024-01-09 07:30:00	send_message
304	104	2024-01-10 09:00:00	app_open

Expected Output

signup_date	signed_up_users	users_sent_first_message	users_active_7d
2024-01-01	2	1	2
2024-01-02	2	2	1

Schema

users

Column	Type	Description
user_idPK	INT	Unique user identifier
signup_ts	TIMESTAMP	Timestamp when the user signed up
signup_surface	VARCHAR(50)	Meta surface where signup started

messages

Column	Type	Description
message_idPK	INT	Unique message identifier
sender_id	INT	User who sent the message
sent_ts	TIMESTAMP	Timestamp when the message was sent
thread_type	VARCHAR(30)	Conversation type

activity_events

Column	Type	Description
event_idPK	INT	Unique activity event identifier
user_id	INT	User who generated the activity event
event_ts	TIMESTAMP	Timestamp of the activity event
event_name	VARCHAR(50)	Type of activity event

Sample Data

Example 1

Input

{"users":[["101","2024-01-02 09:00:00","Facebook"],["102","2024-01-01 15:30:00","Instagram"],["103","2024-01-03 08:00:00","Messenger"],["104","2024-01-02 11:00:00","Facebook"],["105","2024-01-01 07:45:00","Messenger"],["106","2024-01-03 20:00:00","Instagram"],["107","2024-01-02 23:50:00",null],["108","2024-01-01 00:05:00","Facebook"],["109","2024-01-03 10:15:00","Messenger"],["110","2024-01-02 14:20:00","Instagram"],["111","2024-01-06 02:26:26","Gaming"],["112","2023-12-30 08:03:27","Facebook"],

Output

[["2023-12-29","4","0","0"],["2023-12-30","4","0","0"],["2024-01-01","3","3","2"],["2024-01-02","5","3","3"],["2024-01-03","3","1","2"],["2024-01-04","3","0","0"],["2024-01-05","2","0","0"],["2024-01-06","1","0","0"]]

Practice SQL

PostgreSQL

Open on desktop for the full SQL editor with syntax highlighting and autocomplete.

Up next

Analyze Asana Onboarding Funnel DropoffMedium

Signup to Activation Funnel AnalysisMedium

Analyze Signup Funnel Drop-OffMedium

Next question

Meta Messenger Signup Funnel

Easy

SQL & Data Manipulation

Asked at 3 companies3

Also asked at

Problem

Task

Meta wants to measure a simple Messenger onboarding funnel: signup → first message sent → 7-day active. Write a SQL query to calculate, by signup date, how many users reached each stage.

A user counts as:

signed_up if they appear in users
sent_first_message if they sent at least one message on or after signup
active_7d if they had at least one activity event exactly 7 days after signup

Return one row per signup_date with counts for each funnel stage.

Requirements

Group results by signup_date
Count distinct users at each funnel stage
A message sent before signup should not count
A 7-day active event must occur on signup_date + 7 days
Order output by signup_date

Table Definitions

`users`

column	type	description
user_id	INT	Unique user identifier
signup_ts	TIMESTAMP	Account signup timestamp
signup_surface	VARCHAR(50)	Meta signup entry point

`messages`

column	type	description
message_id	INT	Unique message identifier
sender_id	INT	User who sent the message
sent_ts	TIMESTAMP	Message send timestamp
thread_type	VARCHAR(30)	Conversation type

`activity_events`

column	type	description
event_id	INT	Unique activity event identifier
user_id	INT	Active user
event_ts	TIMESTAMP	Activity timestamp
event_name	VARCHAR(50)	Activity type

Sample Data

`users`

user_id	signup_ts	signup_surface
101	2024-01-01 09:00:00	Facebook
102	2024-01-01 15:30:00	Instagram
103	2024-01-02 08:00:00	Facebook
104	2024-01-02 11:00:00	Messenger

`messages`

message_id	sender_id	sent_ts	thread_type
201	101	2024-01-01 10:00:00	direct
202	102	2023-12-31 23:00:00	group
203	103	2024-01-03 09:00:00	direct
204	104	2024-01-02 12:00:00	direct

`activity_events`

event_id	user_id	event_ts	event_name
301	101	2024-01-08 08:00:00	app_open
302	102	2024-01-08 16:00:00	app_open
303	103	2024-01-09 07:30:00	send_message
304	104	2024-01-10 09:00:00	app_open

Expected Output

signup_date	signed_up_users	users_sent_first_message	users_active_7d
2024-01-01	2	1	2
2024-01-02	2	2	1

Schema

users

Column	Type	Description
user_idPK	INT	Unique user identifier
signup_ts	TIMESTAMP	Timestamp when the user signed up
signup_surface	VARCHAR(50)	Meta surface where signup started

messages

Column	Type	Description
message_idPK	INT	Unique message identifier
sender_id	INT	User who sent the message
sent_ts	TIMESTAMP	Timestamp when the message was sent
thread_type	VARCHAR(30)	Conversation type

activity_events

Column	Type	Description
event_idPK	INT	Unique activity event identifier
user_id	INT	User who generated the activity event
event_ts	TIMESTAMP	Timestamp of the activity event
event_name	VARCHAR(50)	Type of activity event

Sample Data

Example 1

Input

{"users":[["101","2024-01-02 09:00:00","Facebook"],["102","2024-01-01 15:30:00","Instagram"],["103","2024-01-03 08:00:00","Messenger"],["104","2024-01-02 11:00:00","Facebook"],["105","2024-01-01 07:45:00","Messenger"],["106","2024-01-03 20:00:00","Instagram"],["107","2024-01-02 23:50:00",null],["108","2024-01-01 00:05:00","Facebook"],["109","2024-01-03 10:15:00","Messenger"],["110","2024-01-02 14:20:00","Instagram"],["111","2024-01-06 02:26:26","Gaming"],["112","2023-12-30 08:03:27","Facebook"],

Output

[["2023-12-29","4","0","0"],["2023-12-30","4","0","0"],["2024-01-01","3","3","2"],["2024-01-02","5","3","3"],["2024-01-03","3","1","2"],["2024-01-04","3","0","0"],["2024-01-05","2","0","0"],["2024-01-06","1","0","0"]]

Problem

Task

Meta wants to measure a simple Messenger onboarding funnel: signup → first message sent → 7-day active. Write a SQL query to calculate, by signup date, how many users reached each stage.

A user counts as:

signed_up if they appear in users
sent_first_message if they sent at least one message on or after signup
active_7d if they had at least one activity event exactly 7 days after signup

Return one row per signup_date with counts for each funnel stage.

Requirements

Group results by signup_date
Count distinct users at each funnel stage
A message sent before signup should not count
A 7-day active event must occur on signup_date + 7 days
Order output by signup_date

Table Definitions

`users`

column	type	description
user_id	INT	Unique user identifier
signup_ts	TIMESTAMP	Account signup timestamp
signup_surface	VARCHAR(50)	Meta signup entry point

`messages`

column	type	description
message_id	INT	Unique message identifier
sender_id	INT	User who sent the message
sent_ts	TIMESTAMP	Message send timestamp
thread_type	VARCHAR(30)	Conversation type

`activity_events`

column	type	description
event_id	INT	Unique activity event identifier
user_id	INT	Active user
event_ts	TIMESTAMP	Activity timestamp
event_name	VARCHAR(50)	Activity type

Sample Data

`users`

user_id	signup_ts	signup_surface
101	2024-01-01 09:00:00	Facebook
102	2024-01-01 15:30:00	Instagram
103	2024-01-02 08:00:00	Facebook
104	2024-01-02 11:00:00	Messenger

`messages`

message_id	sender_id	sent_ts	thread_type
201	101	2024-01-01 10:00:00	direct
202	102	2023-12-31 23:00:00	group
203	103	2024-01-03 09:00:00	direct
204	104	2024-01-02 12:00:00	direct

`activity_events`

event_id	user_id	event_ts	event_name
301	101	2024-01-08 08:00:00	app_open
302	102	2024-01-08 16:00:00	app_open
303	103	2024-01-09 07:30:00	send_message
304	104	2024-01-10 09:00:00	app_open

Expected Output

signup_date	signed_up_users	users_sent_first_message	users_active_7d
2024-01-01	2	1	2
2024-01-02	2	2	1

Schema

users

Column	Type	Description
user_idPK	INT	Unique user identifier
signup_ts	TIMESTAMP	Timestamp when the user signed up
signup_surface	VARCHAR(50)	Meta surface where signup started

messages

Column	Type	Description
message_idPK	INT	Unique message identifier
sender_id	INT	User who sent the message
sent_ts	TIMESTAMP	Timestamp when the message was sent
thread_type	VARCHAR(30)	Conversation type

activity_events

Column	Type	Description
event_idPK	INT	Unique activity event identifier
user_id	INT	User who generated the activity event
event_ts	TIMESTAMP	Timestamp of the activity event
event_name	VARCHAR(50)	Type of activity event

Sample Data

Example 1

Input

{"users":[["101","2024-01-02 09:00:00","Facebook"],["102","2024-01-01 15:30:00","Instagram"],["103","2024-01-03 08:00:00","Messenger"],["104","2024-01-02 11:00:00","Facebook"],["105","2024-01-01 07:45:00","Messenger"],["106","2024-01-03 20:00:00","Instagram"],["107","2024-01-02 23:50:00",null],["108","2024-01-01 00:05:00","Facebook"],["109","2024-01-03 10:15:00","Messenger"],["110","2024-01-02 14:20:00","Instagram"],["111","2024-01-06 02:26:26","Gaming"],["112","2023-12-30 08:03:27","Facebook"],

Output

[["2023-12-29","4","0","0"],["2023-12-30","4","0","0"],["2024-01-01","3","3","2"],["2024-01-02","5","3","3"],["2024-01-03","3","1","2"],["2024-01-04","3","0","0"],["2024-01-05","2","0","0"],["2024-01-06","1","0","0"]]

Practice SQL

PostgreSQL

Open on desktop for the full SQL editor with syntax highlighting and autocomplete.

Up next

Analyze Asana Onboarding Funnel DropoffMedium

Signup to Activation Funnel AnalysisMedium

Analyze Signup Funnel Drop-OffMedium

Next question