Interview Guides

Second Purchase Within 24 Hours | Dataford Interview Questions - Dataford - Ace your Interview

Second Purchase Within 24 Hours

Easy

SQL & Data Manipulation

Asked at 1 company1

Also asked at

Problem

Task

On Meta Marketplace, the analytics team wants to identify users whose second completed transaction happened within 24 hours of their first completed transaction.

Write a SQL query to return the users who meet this condition.

Requirements

Consider only rows where status = 'completed'.
For each user, identify their first and second completed transactions by transaction_time.
Return only users whose second completed transaction occurred within 24 hours of their first.
Output user_id, first_transaction_time, second_transaction_time, and the time difference as hours_between.
Order the final result by user_id.

Table Definitions

`marketplace_transactions`

column	type	description
transaction_id	INT	Unique transaction ID
user_id	INT	Meta user ID
transaction_time	TIMESTAMP	Time the transaction was created
amount	DECIMAL(10,2)	Transaction amount
status	VARCHAR(20)	Transaction status
payment_method	VARCHAR(30)	Payment method used

Sample Data

transaction_id	user_id	transaction_time	amount	status	payment_method
1004	102	2024-03-02 11:00:00	40.00	completed	card
1001	101	2024-03-01 09:00:00	25.50	completed	card
1008	103	2024-03-05 08:00:00	15.00	completed	paypal
1002	101	2024-03-02 08:30:00	10.00	completed	paypal
1011	104	2024-03-07 09:00:00	12.00	failed	card
1012	104	2024-03-07 10:00:00	18.00	completed	card
1015	106	2024-03-10 10:00:00	50.00	completed	apple_pay
1016	106	2024-03-11 10:00:00	60.00	completed	apple_pay
1019	108	2024-03-12 09:00:00	22.00	completed	card
1020	108	2024-03-12 20:00:00	18.00	completed	null

Expected Output

user_id	first_transaction_time	second_transaction_time	hours_between
102	2024-03-02 11:00:00	2024-03-03 09:00:00	22.00
104	2024-03-07 10:00:00	2024-03-08 08:00:00	22.00
106	2024-03-10 10:00:00	2024-03-11 10:00:00	24.00
108	2024-03-12 09:00:00	2024-03-12 20:00:00	11.00

Schema

marketplace_transactions

Column	Type	Description
transaction_idPK	INT	Unique transaction identifier
user_id	INT	Meta user identifier
transaction_time	TIMESTAMP	Timestamp when the transaction occurred
amount	DECIMAL(10,2)	Transaction amount
status	VARCHAR(20)	Transaction status such as completed, failed, pending, or refunded
payment_method	VARCHAR(30)	Payment method used for the transaction

Sample Data

Example 1

Input

{"marketplace_transactions":[["1004","102","2024-03-02 11:00:00","40.00","completed","card"],["1001","101","2024-03-01 09:00:00","25.50","completed","card"],["1008","103","2024-03-05 08:00:00","15.00","completed","paypal"],["1002","101","2024-03-02 08:30:00","10.00","completed","paypal"],["1011","104","2024-03-07 09:00:00","12.00","failed","card"],["1012","104","2024-03-07 10:00:00","18.00","completed","card"],["1015","106","2024-03-10 10:00:00","50.00","completed","apple_pay"],["1016","106","20

Output

[["101","2024-03-01 09:00:00","2024-03-02 08:30:00","23.50"],["102","2024-03-02 11:00:00","2024-03-03 09:00:00","22.00"],["104","2024-03-07 10:00:00","2024-03-08 08:00:00","22.00"],["106","2024-03-10 10:00:00","2024-03-11 10:00:00","24.00"],["108","2024-03-12 09:00:00","2024-03-12 20:00:00","11.00"]]

Problem

Task

On Meta Marketplace, the analytics team wants to identify users whose second completed transaction happened within 24 hours of their first completed transaction.

Write a SQL query to return the users who meet this condition.

Requirements

Consider only rows where status = 'completed'.
For each user, identify their first and second completed transactions by transaction_time.
Return only users whose second completed transaction occurred within 24 hours of their first.
Output user_id, first_transaction_time, second_transaction_time, and the time difference as hours_between.
Order the final result by user_id.

Table Definitions

`marketplace_transactions`

column	type	description
transaction_id	INT	Unique transaction ID
user_id	INT	Meta user ID
transaction_time	TIMESTAMP	Time the transaction was created
amount	DECIMAL(10,2)	Transaction amount
status	VARCHAR(20)	Transaction status
payment_method	VARCHAR(30)	Payment method used

Sample Data

transaction_id	user_id	transaction_time	amount	status	payment_method
1004	102	2024-03-02 11:00:00	40.00	completed	card
1001	101	2024-03-01 09:00:00	25.50	completed	card
1008	103	2024-03-05 08:00:00	15.00	completed	paypal
1002	101	2024-03-02 08:30:00	10.00	completed	paypal
1011	104	2024-03-07 09:00:00	12.00	failed	card
1012	104	2024-03-07 10:00:00	18.00	completed	card
1015	106	2024-03-10 10:00:00	50.00	completed	apple_pay
1016	106	2024-03-11 10:00:00	60.00	completed	apple_pay
1019	108	2024-03-12 09:00:00	22.00	completed	card
1020	108	2024-03-12 20:00:00	18.00	completed	null

Expected Output

user_id	first_transaction_time	second_transaction_time	hours_between
102	2024-03-02 11:00:00	2024-03-03 09:00:00	22.00
104	2024-03-07 10:00:00	2024-03-08 08:00:00	22.00
106	2024-03-10 10:00:00	2024-03-11 10:00:00	24.00
108	2024-03-12 09:00:00	2024-03-12 20:00:00	11.00

Schema

marketplace_transactions

Column	Type	Description
transaction_idPK	INT	Unique transaction identifier
user_id	INT	Meta user identifier
transaction_time	TIMESTAMP	Timestamp when the transaction occurred
amount	DECIMAL(10,2)	Transaction amount
status	VARCHAR(20)	Transaction status such as completed, failed, pending, or refunded
payment_method	VARCHAR(30)	Payment method used for the transaction

Sample Data

Example 1

Input

{"marketplace_transactions":[["1004","102","2024-03-02 11:00:00","40.00","completed","card"],["1001","101","2024-03-01 09:00:00","25.50","completed","card"],["1008","103","2024-03-05 08:00:00","15.00","completed","paypal"],["1002","101","2024-03-02 08:30:00","10.00","completed","paypal"],["1011","104","2024-03-07 09:00:00","12.00","failed","card"],["1012","104","2024-03-07 10:00:00","18.00","completed","card"],["1015","106","2024-03-10 10:00:00","50.00","completed","apple_pay"],["1016","106","20

Output

[["101","2024-03-01 09:00:00","2024-03-02 08:30:00","23.50"],["102","2024-03-02 11:00:00","2024-03-03 09:00:00","22.00"],["104","2024-03-07 10:00:00","2024-03-08 08:00:00","22.00"],["106","2024-03-10 10:00:00","2024-03-11 10:00:00","24.00"],["108","2024-03-12 09:00:00","2024-03-12 20:00:00","11.00"]]

Practice SQL

PostgreSQL

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

Up next

Second Most Recent Marketplace PurchaseHard

Second-Highest Marketplace Transactions UserMedium

Average Time to Second Uber RideEasy

Next question

Second Purchase Within 24 Hours

Easy

SQL & Data Manipulation

Asked at 1 company1

Also asked at

Problem

Task

On Meta Marketplace, the analytics team wants to identify users whose second completed transaction happened within 24 hours of their first completed transaction.

Write a SQL query to return the users who meet this condition.

Requirements

Consider only rows where status = 'completed'.
For each user, identify their first and second completed transactions by transaction_time.
Return only users whose second completed transaction occurred within 24 hours of their first.
Output user_id, first_transaction_time, second_transaction_time, and the time difference as hours_between.
Order the final result by user_id.

Table Definitions

`marketplace_transactions`

column	type	description
transaction_id	INT	Unique transaction ID
user_id	INT	Meta user ID
transaction_time	TIMESTAMP	Time the transaction was created
amount	DECIMAL(10,2)	Transaction amount
status	VARCHAR(20)	Transaction status
payment_method	VARCHAR(30)	Payment method used

Sample Data

transaction_id	user_id	transaction_time	amount	status	payment_method
1004	102	2024-03-02 11:00:00	40.00	completed	card
1001	101	2024-03-01 09:00:00	25.50	completed	card
1008	103	2024-03-05 08:00:00	15.00	completed	paypal
1002	101	2024-03-02 08:30:00	10.00	completed	paypal
1011	104	2024-03-07 09:00:00	12.00	failed	card
1012	104	2024-03-07 10:00:00	18.00	completed	card
1015	106	2024-03-10 10:00:00	50.00	completed	apple_pay
1016	106	2024-03-11 10:00:00	60.00	completed	apple_pay
1019	108	2024-03-12 09:00:00	22.00	completed	card
1020	108	2024-03-12 20:00:00	18.00	completed	null

Expected Output

user_id	first_transaction_time	second_transaction_time	hours_between
102	2024-03-02 11:00:00	2024-03-03 09:00:00	22.00
104	2024-03-07 10:00:00	2024-03-08 08:00:00	22.00
106	2024-03-10 10:00:00	2024-03-11 10:00:00	24.00
108	2024-03-12 09:00:00	2024-03-12 20:00:00	11.00

Schema

marketplace_transactions

Column	Type	Description
transaction_idPK	INT	Unique transaction identifier
user_id	INT	Meta user identifier
transaction_time	TIMESTAMP	Timestamp when the transaction occurred
amount	DECIMAL(10,2)	Transaction amount
status	VARCHAR(20)	Transaction status such as completed, failed, pending, or refunded
payment_method	VARCHAR(30)	Payment method used for the transaction

Sample Data

Example 1

Input

{"marketplace_transactions":[["1004","102","2024-03-02 11:00:00","40.00","completed","card"],["1001","101","2024-03-01 09:00:00","25.50","completed","card"],["1008","103","2024-03-05 08:00:00","15.00","completed","paypal"],["1002","101","2024-03-02 08:30:00","10.00","completed","paypal"],["1011","104","2024-03-07 09:00:00","12.00","failed","card"],["1012","104","2024-03-07 10:00:00","18.00","completed","card"],["1015","106","2024-03-10 10:00:00","50.00","completed","apple_pay"],["1016","106","20

Output

[["101","2024-03-01 09:00:00","2024-03-02 08:30:00","23.50"],["102","2024-03-02 11:00:00","2024-03-03 09:00:00","22.00"],["104","2024-03-07 10:00:00","2024-03-08 08:00:00","22.00"],["106","2024-03-10 10:00:00","2024-03-11 10:00:00","24.00"],["108","2024-03-12 09:00:00","2024-03-12 20:00:00","11.00"]]

Problem

Task

On Meta Marketplace, the analytics team wants to identify users whose second completed transaction happened within 24 hours of their first completed transaction.

Write a SQL query to return the users who meet this condition.

Requirements

Consider only rows where status = 'completed'.
For each user, identify their first and second completed transactions by transaction_time.
Return only users whose second completed transaction occurred within 24 hours of their first.
Output user_id, first_transaction_time, second_transaction_time, and the time difference as hours_between.
Order the final result by user_id.

Table Definitions

`marketplace_transactions`

column	type	description
transaction_id	INT	Unique transaction ID
user_id	INT	Meta user ID
transaction_time	TIMESTAMP	Time the transaction was created
amount	DECIMAL(10,2)	Transaction amount
status	VARCHAR(20)	Transaction status
payment_method	VARCHAR(30)	Payment method used

Sample Data

transaction_id	user_id	transaction_time	amount	status	payment_method
1004	102	2024-03-02 11:00:00	40.00	completed	card
1001	101	2024-03-01 09:00:00	25.50	completed	card
1008	103	2024-03-05 08:00:00	15.00	completed	paypal
1002	101	2024-03-02 08:30:00	10.00	completed	paypal
1011	104	2024-03-07 09:00:00	12.00	failed	card
1012	104	2024-03-07 10:00:00	18.00	completed	card
1015	106	2024-03-10 10:00:00	50.00	completed	apple_pay
1016	106	2024-03-11 10:00:00	60.00	completed	apple_pay
1019	108	2024-03-12 09:00:00	22.00	completed	card
1020	108	2024-03-12 20:00:00	18.00	completed	null

Expected Output

user_id	first_transaction_time	second_transaction_time	hours_between
102	2024-03-02 11:00:00	2024-03-03 09:00:00	22.00
104	2024-03-07 10:00:00	2024-03-08 08:00:00	22.00
106	2024-03-10 10:00:00	2024-03-11 10:00:00	24.00
108	2024-03-12 09:00:00	2024-03-12 20:00:00	11.00

Schema

marketplace_transactions

Column	Type	Description
transaction_idPK	INT	Unique transaction identifier
user_id	INT	Meta user identifier
transaction_time	TIMESTAMP	Timestamp when the transaction occurred
amount	DECIMAL(10,2)	Transaction amount
status	VARCHAR(20)	Transaction status such as completed, failed, pending, or refunded
payment_method	VARCHAR(30)	Payment method used for the transaction

Sample Data

Example 1

Input

{"marketplace_transactions":[["1004","102","2024-03-02 11:00:00","40.00","completed","card"],["1001","101","2024-03-01 09:00:00","25.50","completed","card"],["1008","103","2024-03-05 08:00:00","15.00","completed","paypal"],["1002","101","2024-03-02 08:30:00","10.00","completed","paypal"],["1011","104","2024-03-07 09:00:00","12.00","failed","card"],["1012","104","2024-03-07 10:00:00","18.00","completed","card"],["1015","106","2024-03-10 10:00:00","50.00","completed","apple_pay"],["1016","106","20

Output

[["101","2024-03-01 09:00:00","2024-03-02 08:30:00","23.50"],["102","2024-03-02 11:00:00","2024-03-03 09:00:00","22.00"],["104","2024-03-07 10:00:00","2024-03-08 08:00:00","22.00"],["106","2024-03-10 10:00:00","2024-03-11 10:00:00","24.00"],["108","2024-03-12 09:00:00","2024-03-12 20:00:00","11.00"]]

Practice SQL

PostgreSQL

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

Up next

Second Most Recent Marketplace PurchaseHard

Second-Highest Marketplace Transactions UserMedium

Average Time to Second Uber RideEasy

Next question