Interview Guides

Second Most Recent Marketplace Purchase | Dataford Interview Questions - Dataford - Ace your Interview

Second Most Recent Marketplace Purchase

Hard

SQL & Data Manipulation

Asked at 1 company1

Also asked at

Problem

Task

Meta Marketplace wants to identify each customer's second most recent completed purchase. Write a SQL query to return one row per customer for their second most recent purchase.

Requirements

Consider only rows where purchase_status = 'completed'.
Rank purchases within each customer_id by purchase_date descending.
If two purchases for the same customer have the same purchase_date, break ties using purchase_id descending.
Return only customers who have at least two completed purchases.
Output customer_id, customer_name, purchase_id, purchase_date, and amount.
Order the final result by customer_id ascending.

Table Definition

marketplace_purchases

column	type	description
purchase_id	INT	Unique purchase identifier
customer_id	INT	Customer identifier
customer_name	VARCHAR(100)	Customer name
purchase_date	DATE	Date of purchase
amount	DECIMAL(10,2)	Purchase amount
purchase_status	VARCHAR(20)	Purchase state such as completed, refunded, or pending

Sample Data

purchase_id	customer_id	customer_name	purchase_date	amount	purchase_status
1008	103	Priya Shah	2024-02-20	40.00	completed
1002	101	Ava Chen	2024-03-12	120.00	completed
1005	102	Marcus Lee	2024-03-15	75.00	refunded
1011	104	Sofia Gomez	2024-01-10	15.00	completed
1001	101	Ava Chen	2024-01-05	80.00	completed
1009	103	Priya Shah	2024-02-20	55.00	completed

Expected Output

customer_id	customer_name	purchase_id	purchase_date	amount
101	Ava Chen	1002	2024-03-12	120.00
103	Priya Shah	1008	2024-02-20	40.00

Schema

marketplace_purchases

Column	Type	Description
purchase_idPK	INT	Unique purchase identifier
customer_id	INT	Customer identifier
customer_name	VARCHAR(100)	Customer full name
purchase_date	DATE	Date the purchase was created
amount	DECIMAL(10,2)	Purchase amount
purchase_status	VARCHAR(20)	Purchase status such as completed, refunded, or pending

Sample Data

Example 1

Input

{"marketplace_purchases":[[1008,103,"Priya Shah","2024-02-20","40.00","completed"],[1002,101,"Ava Chen","2024-03-12","120.00","completed"],[1005,102,"Marcus Lee","2024-03-15","75.00","refunded"],[1011,104,"Sofia Gomez","2024-01-10","15.00","completed"],[1001,101,"Ava Chen","2024-01-05","80.00","completed"],[1009,103,"Priya Shah","2024-02-20","55.00","completed"],[1003,101,"Ava Chen","2024-04-01","60.00","pending"],[1006,102,"Marcus Lee","2024-03-20","90.00","completed"],[1010,103,"Priya Shah","2

Output

[["101","Ava Chen","1001","2024-01-05","80.00"],["102","Marcus Lee","1004","2024-02-10","45.00"],["103","Priya Shah","1008","2024-02-20","40.00"]]

Problem

Task

Meta Marketplace wants to identify each customer's second most recent completed purchase. Write a SQL query to return one row per customer for their second most recent purchase.

Requirements

Consider only rows where purchase_status = 'completed'.
Rank purchases within each customer_id by purchase_date descending.
If two purchases for the same customer have the same purchase_date, break ties using purchase_id descending.
Return only customers who have at least two completed purchases.
Output customer_id, customer_name, purchase_id, purchase_date, and amount.
Order the final result by customer_id ascending.

Table Definition

marketplace_purchases

column	type	description
purchase_id	INT	Unique purchase identifier
customer_id	INT	Customer identifier
customer_name	VARCHAR(100)	Customer name
purchase_date	DATE	Date of purchase
amount	DECIMAL(10,2)	Purchase amount
purchase_status	VARCHAR(20)	Purchase state such as completed, refunded, or pending

Sample Data

purchase_id	customer_id	customer_name	purchase_date	amount	purchase_status
1008	103	Priya Shah	2024-02-20	40.00	completed
1002	101	Ava Chen	2024-03-12	120.00	completed
1005	102	Marcus Lee	2024-03-15	75.00	refunded
1011	104	Sofia Gomez	2024-01-10	15.00	completed
1001	101	Ava Chen	2024-01-05	80.00	completed
1009	103	Priya Shah	2024-02-20	55.00	completed

Expected Output

customer_id	customer_name	purchase_id	purchase_date	amount
101	Ava Chen	1002	2024-03-12	120.00
103	Priya Shah	1008	2024-02-20	40.00

Schema

marketplace_purchases

Column	Type	Description
purchase_idPK	INT	Unique purchase identifier
customer_id	INT	Customer identifier
customer_name	VARCHAR(100)	Customer full name
purchase_date	DATE	Date the purchase was created
amount	DECIMAL(10,2)	Purchase amount
purchase_status	VARCHAR(20)	Purchase status such as completed, refunded, or pending

Sample Data

Example 1

Input

{"marketplace_purchases":[[1008,103,"Priya Shah","2024-02-20","40.00","completed"],[1002,101,"Ava Chen","2024-03-12","120.00","completed"],[1005,102,"Marcus Lee","2024-03-15","75.00","refunded"],[1011,104,"Sofia Gomez","2024-01-10","15.00","completed"],[1001,101,"Ava Chen","2024-01-05","80.00","completed"],[1009,103,"Priya Shah","2024-02-20","55.00","completed"],[1003,101,"Ava Chen","2024-04-01","60.00","pending"],[1006,102,"Marcus Lee","2024-03-20","90.00","completed"],[1010,103,"Priya Shah","2

Output

[["101","Ava Chen","1001","2024-01-05","80.00"],["102","Marcus Lee","1004","2024-02-10","45.00"],["103","Priya Shah","1008","2024-02-20","40.00"]]

Practice SQL

PostgreSQL

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

Up next

Second Purchase Within 24 HoursEasy

Second-Highest Marketplace Transactions UserMedium

Second Most Recent Marketplace Purchase

Hard

SQL & Data Manipulation

Asked at 1 company1

Also asked at

Problem

Task

Meta Marketplace wants to identify each customer's second most recent completed purchase. Write a SQL query to return one row per customer for their second most recent purchase.

Requirements

Consider only rows where purchase_status = 'completed'.
Rank purchases within each customer_id by purchase_date descending.
If two purchases for the same customer have the same purchase_date, break ties using purchase_id descending.
Return only customers who have at least two completed purchases.
Output customer_id, customer_name, purchase_id, purchase_date, and amount.
Order the final result by customer_id ascending.

Table Definition

marketplace_purchases

column	type	description
purchase_id	INT	Unique purchase identifier
customer_id	INT	Customer identifier
customer_name	VARCHAR(100)	Customer name
purchase_date	DATE	Date of purchase
amount	DECIMAL(10,2)	Purchase amount
purchase_status	VARCHAR(20)	Purchase state such as completed, refunded, or pending

Sample Data

purchase_id	customer_id	customer_name	purchase_date	amount	purchase_status
1008	103	Priya Shah	2024-02-20	40.00	completed
1002	101	Ava Chen	2024-03-12	120.00	completed
1005	102	Marcus Lee	2024-03-15	75.00	refunded
1011	104	Sofia Gomez	2024-01-10	15.00	completed
1001	101	Ava Chen	2024-01-05	80.00	completed
1009	103	Priya Shah	2024-02-20	55.00	completed

Expected Output

customer_id	customer_name	purchase_id	purchase_date	amount
101	Ava Chen	1002	2024-03-12	120.00
103	Priya Shah	1008	2024-02-20	40.00

Schema

marketplace_purchases

Column	Type	Description
purchase_idPK	INT	Unique purchase identifier
customer_id	INT	Customer identifier
customer_name	VARCHAR(100)	Customer full name
purchase_date	DATE	Date the purchase was created
amount	DECIMAL(10,2)	Purchase amount
purchase_status	VARCHAR(20)	Purchase status such as completed, refunded, or pending

Sample Data

Example 1

Input

{"marketplace_purchases":[[1008,103,"Priya Shah","2024-02-20","40.00","completed"],[1002,101,"Ava Chen","2024-03-12","120.00","completed"],[1005,102,"Marcus Lee","2024-03-15","75.00","refunded"],[1011,104,"Sofia Gomez","2024-01-10","15.00","completed"],[1001,101,"Ava Chen","2024-01-05","80.00","completed"],[1009,103,"Priya Shah","2024-02-20","55.00","completed"],[1003,101,"Ava Chen","2024-04-01","60.00","pending"],[1006,102,"Marcus Lee","2024-03-20","90.00","completed"],[1010,103,"Priya Shah","2

Output

[["101","Ava Chen","1001","2024-01-05","80.00"],["102","Marcus Lee","1004","2024-02-10","45.00"],["103","Priya Shah","1008","2024-02-20","40.00"]]

Problem

Task

Meta Marketplace wants to identify each customer's second most recent completed purchase. Write a SQL query to return one row per customer for their second most recent purchase.

Requirements

Consider only rows where purchase_status = 'completed'.
Rank purchases within each customer_id by purchase_date descending.
If two purchases for the same customer have the same purchase_date, break ties using purchase_id descending.
Return only customers who have at least two completed purchases.
Output customer_id, customer_name, purchase_id, purchase_date, and amount.
Order the final result by customer_id ascending.

Table Definition

marketplace_purchases

column	type	description
purchase_id	INT	Unique purchase identifier
customer_id	INT	Customer identifier
customer_name	VARCHAR(100)	Customer name
purchase_date	DATE	Date of purchase
amount	DECIMAL(10,2)	Purchase amount
purchase_status	VARCHAR(20)	Purchase state such as completed, refunded, or pending

Sample Data

purchase_id	customer_id	customer_name	purchase_date	amount	purchase_status
1008	103	Priya Shah	2024-02-20	40.00	completed
1002	101	Ava Chen	2024-03-12	120.00	completed
1005	102	Marcus Lee	2024-03-15	75.00	refunded
1011	104	Sofia Gomez	2024-01-10	15.00	completed
1001	101	Ava Chen	2024-01-05	80.00	completed
1009	103	Priya Shah	2024-02-20	55.00	completed

Expected Output

customer_id	customer_name	purchase_id	purchase_date	amount
101	Ava Chen	1002	2024-03-12	120.00
103	Priya Shah	1008	2024-02-20	40.00

Schema

marketplace_purchases

Column	Type	Description
purchase_idPK	INT	Unique purchase identifier
customer_id	INT	Customer identifier
customer_name	VARCHAR(100)	Customer full name
purchase_date	DATE	Date the purchase was created
amount	DECIMAL(10,2)	Purchase amount
purchase_status	VARCHAR(20)	Purchase status such as completed, refunded, or pending

Sample Data

Example 1

Input

{"marketplace_purchases":[[1008,103,"Priya Shah","2024-02-20","40.00","completed"],[1002,101,"Ava Chen","2024-03-12","120.00","completed"],[1005,102,"Marcus Lee","2024-03-15","75.00","refunded"],[1011,104,"Sofia Gomez","2024-01-10","15.00","completed"],[1001,101,"Ava Chen","2024-01-05","80.00","completed"],[1009,103,"Priya Shah","2024-02-20","55.00","completed"],[1003,101,"Ava Chen","2024-04-01","60.00","pending"],[1006,102,"Marcus Lee","2024-03-20","90.00","completed"],[1010,103,"Priya Shah","2

Output

[["101","Ava Chen","1001","2024-01-05","80.00"],["102","Marcus Lee","1004","2024-02-10","45.00"],["103","Priya Shah","1008","2024-02-20","40.00"]]

Practice SQL

PostgreSQL

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

Up next

Second Purchase Within 24 HoursEasy

Second-Highest Marketplace Transactions UserMedium