Interview Guides

First Feature Interaction After Signup | Dataford Interview Questions - Dataford - Ace your Interview

First Feature Interaction After Signup

Medium

SQL & Data Manipulation

Asked at 1 company1JoinsWindow FunctionsCTEs

Also asked at

Problem

Task

NovaApp wants to understand which product feature each user touches first after creating an account. Write a SQL query to return the first feature interaction that happened on or after each user's signup timestamp.

Requirements

Return one row per user who has at least one valid interaction after signup.
Include user_id, email, signup_at, feature_name, and first_interaction_at.
Ignore events that occurred before signup.
If a user has multiple valid events at the exact same timestamp, return the one with the smaller event_id.
Order the final result by user_id.

Table Definitions

`users`

column	type	description
user_id	INT	Primary key for each user
email	VARCHAR(255)	User email address
signup_at	TIMESTAMP	Account creation timestamp
plan_tier	VARCHAR(50)	Subscription tier

`feature_events`

column	type	description
event_id	INT	Primary key for each event
user_id	INT	User who triggered the event
feature_name	VARCHAR(100)	Product feature used
event_at	TIMESTAMP	Event timestamp
device_type	VARCHAR(50)	Device used for the event

Sample Data

`users`

user_id	email	signup_at	plan_tier
101	ava@novaapp.com	2024-01-10 09:00:00	free
102	ben@novaapp.com	2024-01-10 10:30:00	pro
103	cara@novaapp.com	2024-01-11 08:15:00	free

`feature_events`

event_id	user_id	feature_name	event_at	device_type
1005	101	search	2024-01-10 09:05:00	web
1002	101	onboarding_checklist	2024-01-10 09:05:00	mobile
1008	102	dashboard	2024-01-10 10:20:00	web

Expected Output

user_id	email	signup_at	feature_name	first_interaction_at
101	ava@novaapp.com	2024-01-10 09:00:00	onboarding_checklist	2024-01-10 09:05:00
102	ben@novaapp.com	2024-01-10 10:30:00	upload_file	2024-01-10 10:30:00

Schema

users

Column	Type	Description
user_idPK	INT	Unique identifier for a user
email	VARCHAR(255)	User email address
signup_at	TIMESTAMP	Timestamp when the user signed up
plan_tier	VARCHAR(50)	Subscription plan tier

feature_events

Column	Type	Description
event_idPK	INT	Unique identifier for an event
user_id	INT	User who generated the event
feature_name	VARCHAR(100)	Name of the feature the user interacted with
event_at	TIMESTAMP	Timestamp of the feature interaction
device_type	VARCHAR(50)	Device type used for the interaction

Sample Data

Example 1

Input

{"users":[[101,"ava@novaapp.com","2024-01-10 09:00:00","free"],[102,"ben@novaapp.com","2024-01-10 10:30:00","pro"],[103,"cara@novaapp.com","2024-01-11 08:15:00","free"],[104,"dan@novaapp.com","2024-01-12 12:00:00","enterprise"],[105,"ella@novaapp.com","2024-01-12 12:00:00",null],[106,"finn@novaapp.com","2024-01-13 14:45:00","pro"],[107,"gina@novaapp.com","2024-01-14 16:20:00","free"],[108,"hugo@novaapp.com","2024-01-15 09:10:00","pro"],[109,"ivy@novaapp.com","2024-01-16 11:00:00","free"],["1","o

Output

[["7","sam@novaapp.com","2024-01-11 06:33:05","dashboard","2024-01-18 20:35:53"],["101","ava@novaapp.com","2024-01-10 09:00:00","onboarding_checklist","2024-01-10 09:05:00"],["102","ben@novaapp.com","2024-01-10 10:30:00","upload_file","2024-01-10 10:30:00"],["103","cara@novaapp.com","2024-01-11 08:15:00","invite_teammate","2024-01-11 08:16:00"],["104","dan@novaapp.com","2024-01-12 12:00:00","export_csv","2024-01-12 12:00:00"],["105","ella@novaapp.com","2024-01-12 12:00:00","null","2024-01-12 12:

Problem

Task

Requirements

Return one row per user who has at least one valid interaction after signup.
Include user_id, email, signup_at, feature_name, and first_interaction_at.
Ignore events that occurred before signup.
If a user has multiple valid events at the exact same timestamp, return the one with the smaller event_id.
Order the final result by user_id.

Table Definitions

`users`

column	type	description
user_id	INT	Primary key for each user
email	VARCHAR(255)	User email address
signup_at	TIMESTAMP	Account creation timestamp
plan_tier	VARCHAR(50)	Subscription tier

`feature_events`

column	type	description
event_id	INT	Primary key for each event
user_id	INT	User who triggered the event
feature_name	VARCHAR(100)	Product feature used
event_at	TIMESTAMP	Event timestamp
device_type	VARCHAR(50)	Device used for the event

Sample Data

`users`

user_id	email	signup_at	plan_tier
101	ava@novaapp.com	2024-01-10 09:00:00	free
102	ben@novaapp.com	2024-01-10 10:30:00	pro
103	cara@novaapp.com	2024-01-11 08:15:00	free

`feature_events`

event_id	user_id	feature_name	event_at	device_type
1005	101	search	2024-01-10 09:05:00	web
1002	101	onboarding_checklist	2024-01-10 09:05:00	mobile
1008	102	dashboard	2024-01-10 10:20:00	web

Expected Output

user_id	email	signup_at	feature_name	first_interaction_at
101	ava@novaapp.com	2024-01-10 09:00:00	onboarding_checklist	2024-01-10 09:05:00
102	ben@novaapp.com	2024-01-10 10:30:00	upload_file	2024-01-10 10:30:00

Schema

users

Column	Type	Description
user_idPK	INT	Unique identifier for a user
email	VARCHAR(255)	User email address
signup_at	TIMESTAMP	Timestamp when the user signed up
plan_tier	VARCHAR(50)	Subscription plan tier

feature_events

Column	Type	Description
event_idPK	INT	Unique identifier for an event
user_id	INT	User who generated the event
feature_name	VARCHAR(100)	Name of the feature the user interacted with
event_at	TIMESTAMP	Timestamp of the feature interaction
device_type	VARCHAR(50)	Device type used for the interaction

Sample Data

Example 1

Input

{"users":[[101,"ava@novaapp.com","2024-01-10 09:00:00","free"],[102,"ben@novaapp.com","2024-01-10 10:30:00","pro"],[103,"cara@novaapp.com","2024-01-11 08:15:00","free"],[104,"dan@novaapp.com","2024-01-12 12:00:00","enterprise"],[105,"ella@novaapp.com","2024-01-12 12:00:00",null],[106,"finn@novaapp.com","2024-01-13 14:45:00","pro"],[107,"gina@novaapp.com","2024-01-14 16:20:00","free"],[108,"hugo@novaapp.com","2024-01-15 09:10:00","pro"],[109,"ivy@novaapp.com","2024-01-16 11:00:00","free"],["1","o

Output

[["7","sam@novaapp.com","2024-01-11 06:33:05","dashboard","2024-01-18 20:35:53"],["101","ava@novaapp.com","2024-01-10 09:00:00","onboarding_checklist","2024-01-10 09:05:00"],["102","ben@novaapp.com","2024-01-10 10:30:00","upload_file","2024-01-10 10:30:00"],["103","cara@novaapp.com","2024-01-11 08:15:00","invite_teammate","2024-01-11 08:16:00"],["104","dan@novaapp.com","2024-01-12 12:00:00","export_csv","2024-01-12 12:00:00"],["105","ella@novaapp.com","2024-01-12 12:00:00","null","2024-01-12 12:

Practice SQL

PostgreSQL

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

Up next

First Chime Feature After SignupMedium

First Meaningful Post-Signup ActionMedium

Signup to Activation Funnel AnalysisMedium

Next question

First Feature Interaction After Signup

Medium

SQL & Data Manipulation

Asked at 1 company1JoinsWindow FunctionsCTEs

Also asked at

Problem

Task

Requirements

Return one row per user who has at least one valid interaction after signup.
Include user_id, email, signup_at, feature_name, and first_interaction_at.
Ignore events that occurred before signup.
If a user has multiple valid events at the exact same timestamp, return the one with the smaller event_id.
Order the final result by user_id.

Table Definitions

`users`

column	type	description
user_id	INT	Primary key for each user
email	VARCHAR(255)	User email address
signup_at	TIMESTAMP	Account creation timestamp
plan_tier	VARCHAR(50)	Subscription tier

`feature_events`

column	type	description
event_id	INT	Primary key for each event
user_id	INT	User who triggered the event
feature_name	VARCHAR(100)	Product feature used
event_at	TIMESTAMP	Event timestamp
device_type	VARCHAR(50)	Device used for the event

Sample Data

`users`

user_id	email	signup_at	plan_tier
101	ava@novaapp.com	2024-01-10 09:00:00	free
102	ben@novaapp.com	2024-01-10 10:30:00	pro
103	cara@novaapp.com	2024-01-11 08:15:00	free

`feature_events`

event_id	user_id	feature_name	event_at	device_type
1005	101	search	2024-01-10 09:05:00	web
1002	101	onboarding_checklist	2024-01-10 09:05:00	mobile
1008	102	dashboard	2024-01-10 10:20:00	web

Expected Output

user_id	email	signup_at	feature_name	first_interaction_at
101	ava@novaapp.com	2024-01-10 09:00:00	onboarding_checklist	2024-01-10 09:05:00
102	ben@novaapp.com	2024-01-10 10:30:00	upload_file	2024-01-10 10:30:00

Schema

users

Column	Type	Description
user_idPK	INT	Unique identifier for a user
email	VARCHAR(255)	User email address
signup_at	TIMESTAMP	Timestamp when the user signed up
plan_tier	VARCHAR(50)	Subscription plan tier

feature_events

Column	Type	Description
event_idPK	INT	Unique identifier for an event
user_id	INT	User who generated the event
feature_name	VARCHAR(100)	Name of the feature the user interacted with
event_at	TIMESTAMP	Timestamp of the feature interaction
device_type	VARCHAR(50)	Device type used for the interaction

Sample Data

Example 1

Input

{"users":[[101,"ava@novaapp.com","2024-01-10 09:00:00","free"],[102,"ben@novaapp.com","2024-01-10 10:30:00","pro"],[103,"cara@novaapp.com","2024-01-11 08:15:00","free"],[104,"dan@novaapp.com","2024-01-12 12:00:00","enterprise"],[105,"ella@novaapp.com","2024-01-12 12:00:00",null],[106,"finn@novaapp.com","2024-01-13 14:45:00","pro"],[107,"gina@novaapp.com","2024-01-14 16:20:00","free"],[108,"hugo@novaapp.com","2024-01-15 09:10:00","pro"],[109,"ivy@novaapp.com","2024-01-16 11:00:00","free"],["1","o

Output

[["7","sam@novaapp.com","2024-01-11 06:33:05","dashboard","2024-01-18 20:35:53"],["101","ava@novaapp.com","2024-01-10 09:00:00","onboarding_checklist","2024-01-10 09:05:00"],["102","ben@novaapp.com","2024-01-10 10:30:00","upload_file","2024-01-10 10:30:00"],["103","cara@novaapp.com","2024-01-11 08:15:00","invite_teammate","2024-01-11 08:16:00"],["104","dan@novaapp.com","2024-01-12 12:00:00","export_csv","2024-01-12 12:00:00"],["105","ella@novaapp.com","2024-01-12 12:00:00","null","2024-01-12 12:

Problem

Task

Requirements

Return one row per user who has at least one valid interaction after signup.
Include user_id, email, signup_at, feature_name, and first_interaction_at.
Ignore events that occurred before signup.
If a user has multiple valid events at the exact same timestamp, return the one with the smaller event_id.
Order the final result by user_id.

Table Definitions

`users`

column	type	description
user_id	INT	Primary key for each user
email	VARCHAR(255)	User email address
signup_at	TIMESTAMP	Account creation timestamp
plan_tier	VARCHAR(50)	Subscription tier

`feature_events`

column	type	description
event_id	INT	Primary key for each event
user_id	INT	User who triggered the event
feature_name	VARCHAR(100)	Product feature used
event_at	TIMESTAMP	Event timestamp
device_type	VARCHAR(50)	Device used for the event

Sample Data

`users`

user_id	email	signup_at	plan_tier
101	ava@novaapp.com	2024-01-10 09:00:00	free
102	ben@novaapp.com	2024-01-10 10:30:00	pro
103	cara@novaapp.com	2024-01-11 08:15:00	free

`feature_events`

event_id	user_id	feature_name	event_at	device_type
1005	101	search	2024-01-10 09:05:00	web
1002	101	onboarding_checklist	2024-01-10 09:05:00	mobile
1008	102	dashboard	2024-01-10 10:20:00	web

Expected Output

user_id	email	signup_at	feature_name	first_interaction_at
101	ava@novaapp.com	2024-01-10 09:00:00	onboarding_checklist	2024-01-10 09:05:00
102	ben@novaapp.com	2024-01-10 10:30:00	upload_file	2024-01-10 10:30:00

Schema

users

Column	Type	Description
user_idPK	INT	Unique identifier for a user
email	VARCHAR(255)	User email address
signup_at	TIMESTAMP	Timestamp when the user signed up
plan_tier	VARCHAR(50)	Subscription plan tier

feature_events

Column	Type	Description
event_idPK	INT	Unique identifier for an event
user_id	INT	User who generated the event
feature_name	VARCHAR(100)	Name of the feature the user interacted with
event_at	TIMESTAMP	Timestamp of the feature interaction
device_type	VARCHAR(50)	Device type used for the interaction

Sample Data

Example 1

Input

{"users":[[101,"ava@novaapp.com","2024-01-10 09:00:00","free"],[102,"ben@novaapp.com","2024-01-10 10:30:00","pro"],[103,"cara@novaapp.com","2024-01-11 08:15:00","free"],[104,"dan@novaapp.com","2024-01-12 12:00:00","enterprise"],[105,"ella@novaapp.com","2024-01-12 12:00:00",null],[106,"finn@novaapp.com","2024-01-13 14:45:00","pro"],[107,"gina@novaapp.com","2024-01-14 16:20:00","free"],[108,"hugo@novaapp.com","2024-01-15 09:10:00","pro"],[109,"ivy@novaapp.com","2024-01-16 11:00:00","free"],["1","o

Output

[["7","sam@novaapp.com","2024-01-11 06:33:05","dashboard","2024-01-18 20:35:53"],["101","ava@novaapp.com","2024-01-10 09:00:00","onboarding_checklist","2024-01-10 09:05:00"],["102","ben@novaapp.com","2024-01-10 10:30:00","upload_file","2024-01-10 10:30:00"],["103","cara@novaapp.com","2024-01-11 08:15:00","invite_teammate","2024-01-11 08:16:00"],["104","dan@novaapp.com","2024-01-12 12:00:00","export_csv","2024-01-12 12:00:00"],["105","ella@novaapp.com","2024-01-12 12:00:00","null","2024-01-12 12:

Practice SQL

PostgreSQL

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

Up next

First Chime Feature After SignupMedium

First Meaningful Post-Signup ActionMedium

Signup to Activation Funnel AnalysisMedium

Next question