Interview Guides

Find Users Missing Follow-Up Action | Dataford Interview Questions - Dataford - Ace your Interview

Find Users Missing Follow-Up Action

Medium

SQL & Data Manipulation

Asked at 1 company1JoinsWindow FunctionsDate Functions

Also asked at

Problem

Task

At StreamForge, product analysts want to identify users who completed action A but did not complete action B within the next 24 hours. Write a PostgreSQL query to return those users.

Requirements

Consider each A event independently.
A user qualifies if they have at least one A event with no corresponding B event from the same user in the next 24 hours.
Return each qualifying user once, along with the timestamp of the qualifying A event.
Sort the result by a_timestamp ascending, then user_id ascending.

Table Definitions

Table	Column	Type	Description
users	user_id	INT	Unique user identifier
users	user_name	VARCHAR(100)	User display name
users	signup_date	DATE	Date the user signed up
events	event_id	INT	Unique event identifier
events	user_id	INT	User who performed the event
events	action_type	VARCHAR(10)	Event type such as `A`, `B`, or other actions
events	event_time	TIMESTAMP	Timestamp of the event

Sample Data

user_id	user_name	signup_date
3	Carla Diaz	2024-01-03
1	Alice Chen	2024-01-01
8	Hana Kim	2024-01-08

event_id	user_id	action_type	event_time
101	1	A	2024-01-10 09:00:00
102	1	B	2024-01-10 20:00:00
103	1	A	2024-01-12 08:00:00
104	1	B	2024-01-13 09:00:00
105	2	A	2024-01-11 10:00:00
106	2	C	2024-01-11 11:00:00
...	...	...	...

Expected Output

user_id	user_name	a_timestamp
4	Daniel Wu	2024-01-09 23:30:00
2	Brian Lee	2024-01-11 10:00:00
1	Alice Chen	2024-01-12 08:00:00
6	Fatima Noor	2024-01-12 12:00:00
8	Hana Kim	2024-01-14 00:00:00

Schema

users

Column	Type	Description
user_idPK	INT	Unique user identifier
user_name	VARCHAR(100)	User display name
signup_date	DATE	Date the user signed up

events

Column	Type	Description
event_idPK	INT	Unique event identifier
user_id	INT	User who performed the event
action_type	VARCHAR(10)	Event type such as A, B, or other actions
event_time	TIMESTAMP	Timestamp when the event occurred

users

user_id	user_name	signup_date
3	Carla Diaz	2024-01-03
1	Alice Chen	2024-01-01
8	Hana Kim	2024-01-08
2	Brian Lee	2024-01-02
5	Elena Rossi
4	Daniel Wu	2024-01-04
7	Grace Park	2024-01-07
6	Fatima Noor	2024-01-06
9	Ivan Petrov	2024-01-09
10	Zane Miller	2024-01-05
11	Victor Gomez	2024-01-05
12	Zane Miller	2024-01-12
13	Yara Hassan	2024-01-12
14	Xavier Cole	null
15	Hana Kim	2024-01-04
16	Ben Carter	2023-12-30
17	Rosa Martinez	2024-01-05
18	Carla Diaz	2024-01-07
19	Carla Diaz	2024-01-10
20	Rosa Martinez	2024-01-10
21	Ivan Petrov	2024-01-01
22	Daniel Wu	2024-01-08
23	Olivia Reed	2024-01-06
24	Rosa Martinez	2024-01-08
25	Liam Brooks	2024-01-08
26	Ben Carter	2024-01-07
27	Alice Chen	2024-01-05
28	Priya Nair	2024-01-04

events

event_id	user_id	action_type	event_time
101	1	A	2024-01-10 09:00:00
102	1	B	2024-01-10 20:00:00
103	1	A	2024-01-12 08:00:00
104	1	B	2024-01-13 09:00:00
105	2	A	2024-01-11 10:00:00
106	2	C	2024-01-11 11:00:00
107	3	A	2024-01-10 15:00:00
108	3	B	2024-01-11 14:59:00
109	4	A	2024-01-09 23:30:00
110	4	B	2024-01-11 00:00:01
111	5	B	2024-01-10 08:00:00
112	5	A	2024-01-10 09:00:00
113	5	B	2024-01-10 10:00:00
114	6	A	2024-01-12 12:00:00
115	6	A	2024-01-12 13:00:00
116	6	B	2024-01-13 11:00:00
117	7	A	2024-01-08 09:00:00
118	7	B	2024-01-09 09:00:00
119	8	A	2024-01-14 00:00:00
120	8	B
121	9	C	2024-01-10 12:00:00
122		A	2024-01-15 10:00:00
123	5	cancel	2024-01-18 09:40:46
124	3	open	2024-01-13 04:45:48
125	6	purchase	2024-01-16 12:36:06
126	2	download	2024-01-15 21:47:03
127	6	cancel	2024-01-17 10:45:20
128	7	null	2024-01-13 22:52:22
129	1	approve	2024-01-09 11:00:35
130	2	logout	2024-01-08 11:59:46

Expected Output

user_id	user_name	a_timestamp
4	Daniel Wu	2024-01-09 23:30:00
2	Brian Lee	2024-01-11 10:00:00
1	Alice Chen	2024-01-12 08:00:00
8	Hana Kim	2024-01-14 00:00:00

Problem

Task

At StreamForge, product analysts want to identify users who completed action A but did not complete action B within the next 24 hours. Write a PostgreSQL query to return those users.

Requirements

Consider each A event independently.
A user qualifies if they have at least one A event with no corresponding B event from the same user in the next 24 hours.
Return each qualifying user once, along with the timestamp of the qualifying A event.
Sort the result by a_timestamp ascending, then user_id ascending.

Table Definitions

Table	Column	Type	Description
users	user_id	INT	Unique user identifier
users	user_name	VARCHAR(100)	User display name
users	signup_date	DATE	Date the user signed up
events	event_id	INT	Unique event identifier
events	user_id	INT	User who performed the event
events	action_type	VARCHAR(10)	Event type such as `A`, `B`, or other actions
events	event_time	TIMESTAMP	Timestamp of the event

Sample Data

user_id	user_name	signup_date
3	Carla Diaz	2024-01-03
1	Alice Chen	2024-01-01
8	Hana Kim	2024-01-08

event_id	user_id	action_type	event_time
101	1	A	2024-01-10 09:00:00
102	1	B	2024-01-10 20:00:00
103	1	A	2024-01-12 08:00:00
104	1	B	2024-01-13 09:00:00
105	2	A	2024-01-11 10:00:00
106	2	C	2024-01-11 11:00:00
...	...	...	...

Expected Output

user_id	user_name	a_timestamp
4	Daniel Wu	2024-01-09 23:30:00
2	Brian Lee	2024-01-11 10:00:00
1	Alice Chen	2024-01-12 08:00:00
6	Fatima Noor	2024-01-12 12:00:00
8	Hana Kim	2024-01-14 00:00:00

Schema

users

Column	Type	Description
user_idPK	INT	Unique user identifier
user_name	VARCHAR(100)	User display name
signup_date	DATE	Date the user signed up

events

Column	Type	Description
event_idPK	INT	Unique event identifier
user_id	INT	User who performed the event
action_type	VARCHAR(10)	Event type such as A, B, or other actions
event_time	TIMESTAMP	Timestamp when the event occurred

users

user_id	user_name	signup_date
3	Carla Diaz	2024-01-03
1	Alice Chen	2024-01-01
8	Hana Kim	2024-01-08
2	Brian Lee	2024-01-02
5	Elena Rossi
4	Daniel Wu	2024-01-04
7	Grace Park	2024-01-07
6	Fatima Noor	2024-01-06
9	Ivan Petrov	2024-01-09
10	Zane Miller	2024-01-05
11	Victor Gomez	2024-01-05
12	Zane Miller	2024-01-12
13	Yara Hassan	2024-01-12
14	Xavier Cole	null
15	Hana Kim	2024-01-04
16	Ben Carter	2023-12-30
17	Rosa Martinez	2024-01-05
18	Carla Diaz	2024-01-07
19	Carla Diaz	2024-01-10
20	Rosa Martinez	2024-01-10
21	Ivan Petrov	2024-01-01
22	Daniel Wu	2024-01-08
23	Olivia Reed	2024-01-06
24	Rosa Martinez	2024-01-08
25	Liam Brooks	2024-01-08
26	Ben Carter	2024-01-07
27	Alice Chen	2024-01-05
28	Priya Nair	2024-01-04

events

event_id	user_id	action_type	event_time
101	1	A	2024-01-10 09:00:00
102	1	B	2024-01-10 20:00:00
103	1	A	2024-01-12 08:00:00
104	1	B	2024-01-13 09:00:00
105	2	A	2024-01-11 10:00:00
106	2	C	2024-01-11 11:00:00
107	3	A	2024-01-10 15:00:00
108	3	B	2024-01-11 14:59:00
109	4	A	2024-01-09 23:30:00
110	4	B	2024-01-11 00:00:01
111	5	B	2024-01-10 08:00:00
112	5	A	2024-01-10 09:00:00
113	5	B	2024-01-10 10:00:00
114	6	A	2024-01-12 12:00:00
115	6	A	2024-01-12 13:00:00
116	6	B	2024-01-13 11:00:00
117	7	A	2024-01-08 09:00:00
118	7	B	2024-01-09 09:00:00
119	8	A	2024-01-14 00:00:00
120	8	B
121	9	C	2024-01-10 12:00:00
122		A	2024-01-15 10:00:00
123	5	cancel	2024-01-18 09:40:46
124	3	open	2024-01-13 04:45:48
125	6	purchase	2024-01-16 12:36:06
126	2	download	2024-01-15 21:47:03
127	6	cancel	2024-01-17 10:45:20
128	7	null	2024-01-13 22:52:22
129	1	approve	2024-01-09 11:00:35
130	2	logout	2024-01-08 11:59:46

Expected Output

user_id	user_name	a_timestamp
4	Daniel Wu	2024-01-09 23:30:00
2	Brian Lee	2024-01-11 10:00:00
1	Alice Chen	2024-01-12 08:00:00
8	Hana Kim	2024-01-14 00:00:00

Practice SQL

PostgreSQL

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

Up next

First and Last User EventsMedium ATop Active Users by Event CountMedium

Classify Power Users by ActivityMedium

Next question

Find Users Missing Follow-Up Action

Medium

SQL & Data Manipulation

Asked at 1 company1JoinsWindow FunctionsDate Functions

Also asked at

Problem

Task

At StreamForge, product analysts want to identify users who completed action A but did not complete action B within the next 24 hours. Write a PostgreSQL query to return those users.

Requirements

Consider each A event independently.
A user qualifies if they have at least one A event with no corresponding B event from the same user in the next 24 hours.
Return each qualifying user once, along with the timestamp of the qualifying A event.
Sort the result by a_timestamp ascending, then user_id ascending.

Table Definitions

Table	Column	Type	Description
users	user_id	INT	Unique user identifier
users	user_name	VARCHAR(100)	User display name
users	signup_date	DATE	Date the user signed up
events	event_id	INT	Unique event identifier
events	user_id	INT	User who performed the event
events	action_type	VARCHAR(10)	Event type such as `A`, `B`, or other actions
events	event_time	TIMESTAMP	Timestamp of the event

Sample Data

user_id	user_name	signup_date
3	Carla Diaz	2024-01-03
1	Alice Chen	2024-01-01
8	Hana Kim	2024-01-08

event_id	user_id	action_type	event_time
101	1	A	2024-01-10 09:00:00
102	1	B	2024-01-10 20:00:00
103	1	A	2024-01-12 08:00:00
104	1	B	2024-01-13 09:00:00
105	2	A	2024-01-11 10:00:00
106	2	C	2024-01-11 11:00:00
...	...	...	...

Expected Output

user_id	user_name	a_timestamp
4	Daniel Wu	2024-01-09 23:30:00
2	Brian Lee	2024-01-11 10:00:00
1	Alice Chen	2024-01-12 08:00:00
6	Fatima Noor	2024-01-12 12:00:00
8	Hana Kim	2024-01-14 00:00:00

Schema

users

Column	Type	Description
user_idPK	INT	Unique user identifier
user_name	VARCHAR(100)	User display name
signup_date	DATE	Date the user signed up

events

Column	Type	Description
event_idPK	INT	Unique event identifier
user_id	INT	User who performed the event
action_type	VARCHAR(10)	Event type such as A, B, or other actions
event_time	TIMESTAMP	Timestamp when the event occurred

users

user_id	user_name	signup_date
3	Carla Diaz	2024-01-03
1	Alice Chen	2024-01-01
8	Hana Kim	2024-01-08
2	Brian Lee	2024-01-02
5	Elena Rossi
4	Daniel Wu	2024-01-04
7	Grace Park	2024-01-07
6	Fatima Noor	2024-01-06
9	Ivan Petrov	2024-01-09
10	Zane Miller	2024-01-05
11	Victor Gomez	2024-01-05
12	Zane Miller	2024-01-12
13	Yara Hassan	2024-01-12
14	Xavier Cole	null
15	Hana Kim	2024-01-04
16	Ben Carter	2023-12-30
17	Rosa Martinez	2024-01-05
18	Carla Diaz	2024-01-07
19	Carla Diaz	2024-01-10
20	Rosa Martinez	2024-01-10
21	Ivan Petrov	2024-01-01
22	Daniel Wu	2024-01-08
23	Olivia Reed	2024-01-06
24	Rosa Martinez	2024-01-08
25	Liam Brooks	2024-01-08
26	Ben Carter	2024-01-07
27	Alice Chen	2024-01-05
28	Priya Nair	2024-01-04

events

event_id	user_id	action_type	event_time
101	1	A	2024-01-10 09:00:00
102	1	B	2024-01-10 20:00:00
103	1	A	2024-01-12 08:00:00
104	1	B	2024-01-13 09:00:00
105	2	A	2024-01-11 10:00:00
106	2	C	2024-01-11 11:00:00
107	3	A	2024-01-10 15:00:00
108	3	B	2024-01-11 14:59:00
109	4	A	2024-01-09 23:30:00
110	4	B	2024-01-11 00:00:01
111	5	B	2024-01-10 08:00:00
112	5	A	2024-01-10 09:00:00
113	5	B	2024-01-10 10:00:00
114	6	A	2024-01-12 12:00:00
115	6	A	2024-01-12 13:00:00
116	6	B	2024-01-13 11:00:00
117	7	A	2024-01-08 09:00:00
118	7	B	2024-01-09 09:00:00
119	8	A	2024-01-14 00:00:00
120	8	B
121	9	C	2024-01-10 12:00:00
122		A	2024-01-15 10:00:00
123	5	cancel	2024-01-18 09:40:46
124	3	open	2024-01-13 04:45:48
125	6	purchase	2024-01-16 12:36:06
126	2	download	2024-01-15 21:47:03
127	6	cancel	2024-01-17 10:45:20
128	7	null	2024-01-13 22:52:22
129	1	approve	2024-01-09 11:00:35
130	2	logout	2024-01-08 11:59:46

Expected Output

user_id	user_name	a_timestamp
4	Daniel Wu	2024-01-09 23:30:00
2	Brian Lee	2024-01-11 10:00:00
1	Alice Chen	2024-01-12 08:00:00
8	Hana Kim	2024-01-14 00:00:00

Problem

Task

At StreamForge, product analysts want to identify users who completed action A but did not complete action B within the next 24 hours. Write a PostgreSQL query to return those users.

Requirements

Consider each A event independently.
A user qualifies if they have at least one A event with no corresponding B event from the same user in the next 24 hours.
Return each qualifying user once, along with the timestamp of the qualifying A event.
Sort the result by a_timestamp ascending, then user_id ascending.

Table Definitions

Table	Column	Type	Description
users	user_id	INT	Unique user identifier
users	user_name	VARCHAR(100)	User display name
users	signup_date	DATE	Date the user signed up
events	event_id	INT	Unique event identifier
events	user_id	INT	User who performed the event
events	action_type	VARCHAR(10)	Event type such as `A`, `B`, or other actions
events	event_time	TIMESTAMP	Timestamp of the event

Sample Data

user_id	user_name	signup_date
3	Carla Diaz	2024-01-03
1	Alice Chen	2024-01-01
8	Hana Kim	2024-01-08

event_id	user_id	action_type	event_time
101	1	A	2024-01-10 09:00:00
102	1	B	2024-01-10 20:00:00
103	1	A	2024-01-12 08:00:00
104	1	B	2024-01-13 09:00:00
105	2	A	2024-01-11 10:00:00
106	2	C	2024-01-11 11:00:00
...	...	...	...

Expected Output

user_id	user_name	a_timestamp
4	Daniel Wu	2024-01-09 23:30:00
2	Brian Lee	2024-01-11 10:00:00
1	Alice Chen	2024-01-12 08:00:00
6	Fatima Noor	2024-01-12 12:00:00
8	Hana Kim	2024-01-14 00:00:00

Schema

users

Column	Type	Description
user_idPK	INT	Unique user identifier
user_name	VARCHAR(100)	User display name
signup_date	DATE	Date the user signed up

events

Column	Type	Description
event_idPK	INT	Unique event identifier
user_id	INT	User who performed the event
action_type	VARCHAR(10)	Event type such as A, B, or other actions
event_time	TIMESTAMP	Timestamp when the event occurred

users

user_id	user_name	signup_date
3	Carla Diaz	2024-01-03
1	Alice Chen	2024-01-01
8	Hana Kim	2024-01-08
2	Brian Lee	2024-01-02
5	Elena Rossi
4	Daniel Wu	2024-01-04
7	Grace Park	2024-01-07
6	Fatima Noor	2024-01-06
9	Ivan Petrov	2024-01-09
10	Zane Miller	2024-01-05
11	Victor Gomez	2024-01-05
12	Zane Miller	2024-01-12
13	Yara Hassan	2024-01-12
14	Xavier Cole	null
15	Hana Kim	2024-01-04
16	Ben Carter	2023-12-30
17	Rosa Martinez	2024-01-05
18	Carla Diaz	2024-01-07
19	Carla Diaz	2024-01-10
20	Rosa Martinez	2024-01-10
21	Ivan Petrov	2024-01-01
22	Daniel Wu	2024-01-08
23	Olivia Reed	2024-01-06
24	Rosa Martinez	2024-01-08
25	Liam Brooks	2024-01-08
26	Ben Carter	2024-01-07
27	Alice Chen	2024-01-05
28	Priya Nair	2024-01-04

events

event_id	user_id	action_type	event_time
101	1	A	2024-01-10 09:00:00
102	1	B	2024-01-10 20:00:00
103	1	A	2024-01-12 08:00:00
104	1	B	2024-01-13 09:00:00
105	2	A	2024-01-11 10:00:00
106	2	C	2024-01-11 11:00:00
107	3	A	2024-01-10 15:00:00
108	3	B	2024-01-11 14:59:00
109	4	A	2024-01-09 23:30:00
110	4	B	2024-01-11 00:00:01
111	5	B	2024-01-10 08:00:00
112	5	A	2024-01-10 09:00:00
113	5	B	2024-01-10 10:00:00
114	6	A	2024-01-12 12:00:00
115	6	A	2024-01-12 13:00:00
116	6	B	2024-01-13 11:00:00
117	7	A	2024-01-08 09:00:00
118	7	B	2024-01-09 09:00:00
119	8	A	2024-01-14 00:00:00
120	8	B
121	9	C	2024-01-10 12:00:00
122		A	2024-01-15 10:00:00
123	5	cancel	2024-01-18 09:40:46
124	3	open	2024-01-13 04:45:48
125	6	purchase	2024-01-16 12:36:06
126	2	download	2024-01-15 21:47:03
127	6	cancel	2024-01-17 10:45:20
128	7	null	2024-01-13 22:52:22
129	1	approve	2024-01-09 11:00:35
130	2	logout	2024-01-08 11:59:46

Expected Output

user_id	user_name	a_timestamp
4	Daniel Wu	2024-01-09 23:30:00
2	Brian Lee	2024-01-11 10:00:00
1	Alice Chen	2024-01-12 08:00:00
8	Hana Kim	2024-01-14 00:00:00

Practice SQL

PostgreSQL

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

Up next

First and Last User EventsMedium ATop Active Users by Event CountMedium

Classify Power Users by ActivityMedium

Next question