Interview Guides

Landing Page to Sign-Up Conversion | Dataford Interview Questions - Dataford - Ace your Interview

Landing Page to Sign-Up Conversion

Easy

SQL & Data Manipulation

Asked at 2 companies2JoinsAggregationsConversion Rate

Also asked at

Problem

Task

Chime’s Growth team wants a quick read on how well the sign-up landing page converts visitors into completed account sign-ups. Write a SQL query to calculate the conversion rate using a single event table.

Requirements

Use only events from the signup_landing surface.
Count distinct visitors who had a landing_page_visit event as the denominator.
Count distinct visitors who had a completed_signup event as the numerator.
Return the number of landing page visitors, the number of completed sign-ups, and the conversion rate as a percentage rounded to 2 decimal places.

Table Definition

growth_funnel_events

column_name	type	description
event_id	INT	Unique event row identifier
user_id	INT	Visitor or member identifier
event_name	VARCHAR(50)	Name of the funnel event
surface_name	VARCHAR(50)	Chime product surface where the event happened
event_date	DATE	Date of the event
device_type	VARCHAR(20)	Device used for the event

Sample Data

event_id	user_id	event_name	surface_name	event_date	device_type
3	102	landing_page_visit	signup_landing	2024-06-01	android
1	101	landing_page_visit	signup_landing	2024-06-01	ios
8	106	completed_signup	signup_landing	2024-06-02	web
5	103	completed_signup	signup_landing	2024-06-01	web
10	108	landing_page_visit	home_feed	2024-06-02	ios
2	101	completed_signup	signup_landing	2024-06-01	ios
6	104	landing_page_visit	signup_landing	2024-06-02	ios
9	107	landing_page_visit	signup_landing	2024-06-02	null
4	102	landing_page_visit	signup_landing	2024-06-01	android
7	105	landing_page_visit	signup_landing	2024-06-02	web

Expected Output

landing_page_visitors	completed_signups	conversion_rate_pct
6	3	50.00

Schema

growth_funnel_events

Column	Type	Description
event_idPK	INT	Unique event row identifier
user_id	INT	Visitor or member identifier
event_name	VARCHAR(50)	Name of the funnel event
surface_name	VARCHAR(50)	Chime product surface where the event occurred
event_date	DATE	Date of the event
device_type	VARCHAR(20)	Device used for the event

Sample Data

Example 1

Input

{"growth_funnel_events":[["3","102","landing_page_visit","signup_landing","2024-06-01","android"],["1","101","landing_page_visit","signup_landing","2024-06-01","ios"],["8","106","completed_signup","signup_landing","2024-06-02","web"],["5","103","completed_signup","signup_landing","2024-06-01","web"],["10","108","landing_page_visit","home_feed","2024-06-02","ios"],["2","101","completed_signup","signup_landing","2024-06-01","ios"],["6","104","landing_page_visit","signup_landing","2024-06-02","ios"

Output[["5","3","60.00"]]

Problem

Task

Requirements

Use only events from the signup_landing surface.
Count distinct visitors who had a landing_page_visit event as the denominator.
Count distinct visitors who had a completed_signup event as the numerator.
Return the number of landing page visitors, the number of completed sign-ups, and the conversion rate as a percentage rounded to 2 decimal places.

Table Definition

growth_funnel_events

column_name	type	description
event_id	INT	Unique event row identifier
user_id	INT	Visitor or member identifier
event_name	VARCHAR(50)	Name of the funnel event
surface_name	VARCHAR(50)	Chime product surface where the event happened
event_date	DATE	Date of the event
device_type	VARCHAR(20)	Device used for the event

Sample Data

event_id	user_id	event_name	surface_name	event_date	device_type
3	102	landing_page_visit	signup_landing	2024-06-01	android
1	101	landing_page_visit	signup_landing	2024-06-01	ios
8	106	completed_signup	signup_landing	2024-06-02	web
5	103	completed_signup	signup_landing	2024-06-01	web
10	108	landing_page_visit	home_feed	2024-06-02	ios
2	101	completed_signup	signup_landing	2024-06-01	ios
6	104	landing_page_visit	signup_landing	2024-06-02	ios
9	107	landing_page_visit	signup_landing	2024-06-02	null
4	102	landing_page_visit	signup_landing	2024-06-01	android
7	105	landing_page_visit	signup_landing	2024-06-02	web

Expected Output

landing_page_visitors	completed_signups	conversion_rate_pct
6	3	50.00

Schema

growth_funnel_events

Column	Type	Description
event_idPK	INT	Unique event row identifier
user_id	INT	Visitor or member identifier
event_name	VARCHAR(50)	Name of the funnel event
surface_name	VARCHAR(50)	Chime product surface where the event occurred
event_date	DATE	Date of the event
device_type	VARCHAR(20)	Device used for the event

Sample Data

Example 1

Input

{"growth_funnel_events":[["3","102","landing_page_visit","signup_landing","2024-06-01","android"],["1","101","landing_page_visit","signup_landing","2024-06-01","ios"],["8","106","completed_signup","signup_landing","2024-06-02","web"],["5","103","completed_signup","signup_landing","2024-06-01","web"],["10","108","landing_page_visit","home_feed","2024-06-02","ios"],["2","101","completed_signup","signup_landing","2024-06-01","ios"],["6","104","landing_page_visit","signup_landing","2024-06-02","ios"

Output[["5","3","60.00"]]

Practice SQL

PostgreSQL

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

Up next

Compare Chime Channel Conversion RatesMedium

Compare Conversion Rates by ChannelEasy

A/B Test for Chime Sign-Up FlowMedium

Next question

Landing Page to Sign-Up Conversion

Easy

SQL & Data Manipulation

Asked at 2 companies2JoinsAggregationsConversion Rate

Also asked at

Problem

Task

Requirements

Use only events from the signup_landing surface.
Count distinct visitors who had a landing_page_visit event as the denominator.
Count distinct visitors who had a completed_signup event as the numerator.
Return the number of landing page visitors, the number of completed sign-ups, and the conversion rate as a percentage rounded to 2 decimal places.

Table Definition

growth_funnel_events

column_name	type	description
event_id	INT	Unique event row identifier
user_id	INT	Visitor or member identifier
event_name	VARCHAR(50)	Name of the funnel event
surface_name	VARCHAR(50)	Chime product surface where the event happened
event_date	DATE	Date of the event
device_type	VARCHAR(20)	Device used for the event

Sample Data

event_id	user_id	event_name	surface_name	event_date	device_type
3	102	landing_page_visit	signup_landing	2024-06-01	android
1	101	landing_page_visit	signup_landing	2024-06-01	ios
8	106	completed_signup	signup_landing	2024-06-02	web
5	103	completed_signup	signup_landing	2024-06-01	web
10	108	landing_page_visit	home_feed	2024-06-02	ios
2	101	completed_signup	signup_landing	2024-06-01	ios
6	104	landing_page_visit	signup_landing	2024-06-02	ios
9	107	landing_page_visit	signup_landing	2024-06-02	null
4	102	landing_page_visit	signup_landing	2024-06-01	android
7	105	landing_page_visit	signup_landing	2024-06-02	web

Expected Output

landing_page_visitors	completed_signups	conversion_rate_pct
6	3	50.00

Schema

growth_funnel_events

Column	Type	Description
event_idPK	INT	Unique event row identifier
user_id	INT	Visitor or member identifier
event_name	VARCHAR(50)	Name of the funnel event
surface_name	VARCHAR(50)	Chime product surface where the event occurred
event_date	DATE	Date of the event
device_type	VARCHAR(20)	Device used for the event

Sample Data

Example 1

Input

{"growth_funnel_events":[["3","102","landing_page_visit","signup_landing","2024-06-01","android"],["1","101","landing_page_visit","signup_landing","2024-06-01","ios"],["8","106","completed_signup","signup_landing","2024-06-02","web"],["5","103","completed_signup","signup_landing","2024-06-01","web"],["10","108","landing_page_visit","home_feed","2024-06-02","ios"],["2","101","completed_signup","signup_landing","2024-06-01","ios"],["6","104","landing_page_visit","signup_landing","2024-06-02","ios"

Output[["5","3","60.00"]]

Problem

Task

Requirements

Use only events from the signup_landing surface.
Count distinct visitors who had a landing_page_visit event as the denominator.
Count distinct visitors who had a completed_signup event as the numerator.
Return the number of landing page visitors, the number of completed sign-ups, and the conversion rate as a percentage rounded to 2 decimal places.

Table Definition

growth_funnel_events

column_name	type	description
event_id	INT	Unique event row identifier
user_id	INT	Visitor or member identifier
event_name	VARCHAR(50)	Name of the funnel event
surface_name	VARCHAR(50)	Chime product surface where the event happened
event_date	DATE	Date of the event
device_type	VARCHAR(20)	Device used for the event

Sample Data

event_id	user_id	event_name	surface_name	event_date	device_type
3	102	landing_page_visit	signup_landing	2024-06-01	android
1	101	landing_page_visit	signup_landing	2024-06-01	ios
8	106	completed_signup	signup_landing	2024-06-02	web
5	103	completed_signup	signup_landing	2024-06-01	web
10	108	landing_page_visit	home_feed	2024-06-02	ios
2	101	completed_signup	signup_landing	2024-06-01	ios
6	104	landing_page_visit	signup_landing	2024-06-02	ios
9	107	landing_page_visit	signup_landing	2024-06-02	null
4	102	landing_page_visit	signup_landing	2024-06-01	android
7	105	landing_page_visit	signup_landing	2024-06-02	web

Expected Output

landing_page_visitors	completed_signups	conversion_rate_pct
6	3	50.00

Schema

growth_funnel_events

Column	Type	Description
event_idPK	INT	Unique event row identifier
user_id	INT	Visitor or member identifier
event_name	VARCHAR(50)	Name of the funnel event
surface_name	VARCHAR(50)	Chime product surface where the event occurred
event_date	DATE	Date of the event
device_type	VARCHAR(20)	Device used for the event

Sample Data

Example 1

Input

{"growth_funnel_events":[["3","102","landing_page_visit","signup_landing","2024-06-01","android"],["1","101","landing_page_visit","signup_landing","2024-06-01","ios"],["8","106","completed_signup","signup_landing","2024-06-02","web"],["5","103","completed_signup","signup_landing","2024-06-01","web"],["10","108","landing_page_visit","home_feed","2024-06-02","ios"],["2","101","completed_signup","signup_landing","2024-06-01","ios"],["6","104","landing_page_visit","signup_landing","2024-06-02","ios"

Output[["5","3","60.00"]]

Practice SQL

PostgreSQL

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

Up next

Compare Chime Channel Conversion RatesMedium

Compare Conversion Rates by ChannelEasy

A/B Test for Chime Sign-Up FlowMedium

Next question