Interview Guides

Second-Highest Marketplace Transactions User | Dataford Interview Questions - Dataford - Ace your Interview

Second-Highest Marketplace Transactions User

Medium

SQL & Data Manipulation

Asked at 1 company1AggregationsSubqueriesRanking

Also asked at

Problem

Task

Meta Marketplace wants to identify the user with the second-highest number of completed transactions. Write a SQL query to return that user.

Requirements

Count only transactions where status = 'completed'.
Return the user or users tied for the second-highest completed transaction count.
Include user_id, user_name, and completed_transaction_count in the output.
Order the final result by user_id ascending.

Table Definitions

`users`

column	type	description
user_id	INT	Primary key for the user
user_name	VARCHAR(100)	Meta Marketplace user name
region	VARCHAR(50)	User region
signup_date	DATE	Date the user signed up

`transactions`

column	type	description
transaction_id	INT	Primary key for the transaction
user_id	INT	User who initiated the transaction
amount	DECIMAL(10,2)	Transaction amount
status	VARCHAR(20)	Transaction status
transaction_date	DATE	Date of the transaction

Sample Data

`users`

user_id	user_name	region	signup_date
104	Priya Shah	APAC	2024-01-11
101	Ava Chen	NA	2023-11-03
108	Zoe Kim	APAC	2024-04-09

`transactions`

transaction_id	user_id	amount	status	transaction_date
205	101	45.00	completed	2024-05-03
201	104	80.00	completed	2024-05-01
214	107	55.00	failed	2024-05-09

Expected Output

user_id	user_name	completed_transaction_count
101	Ava Chen	3
104	Priya Shah	3

Schema

users

Column	Type	Description
user_idPK	INT	Primary key for the user
user_name	VARCHAR(100)	Meta Marketplace user name
region	VARCHAR(50)	User region
signup_date	DATE	Date the user signed up

transactions

Column	Type	Description
transaction_idPK	INT	Primary key for the transaction
user_id	INT	User who initiated the transaction
amount	DECIMAL(10,2)	Transaction amount
status	VARCHAR(20)	Transaction status
transaction_date	DATE	Date of the transaction

Sample Data

Example 1

Input

{"users":[[104,"Priya Shah","APAC","2024-01-11"],[101,"Ava Chen","NA","2023-11-03"],[106,"Noah Patel","EU","2024-02-20"],[103,"Liam Garcia","LATAM","2024-01-05"],[108,"Zoe Kim","APAC","2024-04-09"],[102,"Marcus Lee","EU","2023-12-14"],[105,"Elena Rossi",null,"2024-03-01"],[107,"Omar Khan","NA","2024-03-18"],["1","Jack Walker","Spain","2023-12-24"],["2","Omar Khan","India","2023-12-05"],["3","Alexander Clark","Japan","2024-04-12"],["4","Evelyn Harris","EU","2024-04-12"],["5","Benjamin White","Ger

Output[["102","Marcus Lee","2"],["103","Liam Garcia","2"]]

Problem

Task

Meta Marketplace wants to identify the user with the second-highest number of completed transactions. Write a SQL query to return that user.

Requirements

Count only transactions where status = 'completed'.
Return the user or users tied for the second-highest completed transaction count.
Include user_id, user_name, and completed_transaction_count in the output.
Order the final result by user_id ascending.

Table Definitions

`users`

column	type	description
user_id	INT	Primary key for the user
user_name	VARCHAR(100)	Meta Marketplace user name
region	VARCHAR(50)	User region
signup_date	DATE	Date the user signed up

`transactions`

column	type	description
transaction_id	INT	Primary key for the transaction
user_id	INT	User who initiated the transaction
amount	DECIMAL(10,2)	Transaction amount
status	VARCHAR(20)	Transaction status
transaction_date	DATE	Date of the transaction

Sample Data

`users`

user_id	user_name	region	signup_date
104	Priya Shah	APAC	2024-01-11
101	Ava Chen	NA	2023-11-03
108	Zoe Kim	APAC	2024-04-09

`transactions`

transaction_id	user_id	amount	status	transaction_date
205	101	45.00	completed	2024-05-03
201	104	80.00	completed	2024-05-01
214	107	55.00	failed	2024-05-09

Expected Output

user_id	user_name	completed_transaction_count
101	Ava Chen	3
104	Priya Shah	3

Schema

users

Column	Type	Description
user_idPK	INT	Primary key for the user
user_name	VARCHAR(100)	Meta Marketplace user name
region	VARCHAR(50)	User region
signup_date	DATE	Date the user signed up

transactions

Column	Type	Description
transaction_idPK	INT	Primary key for the transaction
user_id	INT	User who initiated the transaction
amount	DECIMAL(10,2)	Transaction amount
status	VARCHAR(20)	Transaction status
transaction_date	DATE	Date of the transaction

Sample Data

Example 1

Input

{"users":[[104,"Priya Shah","APAC","2024-01-11"],[101,"Ava Chen","NA","2023-11-03"],[106,"Noah Patel","EU","2024-02-20"],[103,"Liam Garcia","LATAM","2024-01-05"],[108,"Zoe Kim","APAC","2024-04-09"],[102,"Marcus Lee","EU","2023-12-14"],[105,"Elena Rossi",null,"2024-03-01"],[107,"Omar Khan","NA","2024-03-18"],["1","Jack Walker","Spain","2023-12-24"],["2","Omar Khan","India","2023-12-05"],["3","Alexander Clark","Japan","2024-04-12"],["4","Evelyn Harris","EU","2024-04-12"],["5","Benjamin White","Ger

Output[["102","Marcus Lee","2"],["103","Liam Garcia","2"]]

Practice SQL

PostgreSQL

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

Up next

Second Purchase Within 24 HoursEasy

Second Most Recent Marketplace PurchaseHard

Top Transaction Users by CountryMedium

Next question

Second-Highest Marketplace Transactions User

Medium

SQL & Data Manipulation

Asked at 1 company1AggregationsSubqueriesRanking

Also asked at

Problem

Task

Meta Marketplace wants to identify the user with the second-highest number of completed transactions. Write a SQL query to return that user.

Requirements

Count only transactions where status = 'completed'.
Return the user or users tied for the second-highest completed transaction count.
Include user_id, user_name, and completed_transaction_count in the output.
Order the final result by user_id ascending.

Table Definitions

`users`

column	type	description
user_id	INT	Primary key for the user
user_name	VARCHAR(100)	Meta Marketplace user name
region	VARCHAR(50)	User region
signup_date	DATE	Date the user signed up

`transactions`

column	type	description
transaction_id	INT	Primary key for the transaction
user_id	INT	User who initiated the transaction
amount	DECIMAL(10,2)	Transaction amount
status	VARCHAR(20)	Transaction status
transaction_date	DATE	Date of the transaction

Sample Data

`users`

user_id	user_name	region	signup_date
104	Priya Shah	APAC	2024-01-11
101	Ava Chen	NA	2023-11-03
108	Zoe Kim	APAC	2024-04-09

`transactions`

transaction_id	user_id	amount	status	transaction_date
205	101	45.00	completed	2024-05-03
201	104	80.00	completed	2024-05-01
214	107	55.00	failed	2024-05-09

Expected Output

user_id	user_name	completed_transaction_count
101	Ava Chen	3
104	Priya Shah	3

Schema

users

Column	Type	Description
user_idPK	INT	Primary key for the user
user_name	VARCHAR(100)	Meta Marketplace user name
region	VARCHAR(50)	User region
signup_date	DATE	Date the user signed up

transactions

Column	Type	Description
transaction_idPK	INT	Primary key for the transaction
user_id	INT	User who initiated the transaction
amount	DECIMAL(10,2)	Transaction amount
status	VARCHAR(20)	Transaction status
transaction_date	DATE	Date of the transaction

Sample Data

Example 1

Input

{"users":[[104,"Priya Shah","APAC","2024-01-11"],[101,"Ava Chen","NA","2023-11-03"],[106,"Noah Patel","EU","2024-02-20"],[103,"Liam Garcia","LATAM","2024-01-05"],[108,"Zoe Kim","APAC","2024-04-09"],[102,"Marcus Lee","EU","2023-12-14"],[105,"Elena Rossi",null,"2024-03-01"],[107,"Omar Khan","NA","2024-03-18"],["1","Jack Walker","Spain","2023-12-24"],["2","Omar Khan","India","2023-12-05"],["3","Alexander Clark","Japan","2024-04-12"],["4","Evelyn Harris","EU","2024-04-12"],["5","Benjamin White","Ger

Output[["102","Marcus Lee","2"],["103","Liam Garcia","2"]]

Problem

Task

Meta Marketplace wants to identify the user with the second-highest number of completed transactions. Write a SQL query to return that user.

Requirements

Count only transactions where status = 'completed'.
Return the user or users tied for the second-highest completed transaction count.
Include user_id, user_name, and completed_transaction_count in the output.
Order the final result by user_id ascending.

Table Definitions

`users`

column	type	description
user_id	INT	Primary key for the user
user_name	VARCHAR(100)	Meta Marketplace user name
region	VARCHAR(50)	User region
signup_date	DATE	Date the user signed up

`transactions`

column	type	description
transaction_id	INT	Primary key for the transaction
user_id	INT	User who initiated the transaction
amount	DECIMAL(10,2)	Transaction amount
status	VARCHAR(20)	Transaction status
transaction_date	DATE	Date of the transaction

Sample Data

`users`

user_id	user_name	region	signup_date
104	Priya Shah	APAC	2024-01-11
101	Ava Chen	NA	2023-11-03
108	Zoe Kim	APAC	2024-04-09

`transactions`

transaction_id	user_id	amount	status	transaction_date
205	101	45.00	completed	2024-05-03
201	104	80.00	completed	2024-05-01
214	107	55.00	failed	2024-05-09

Expected Output

user_id	user_name	completed_transaction_count
101	Ava Chen	3
104	Priya Shah	3

Schema

users

Column	Type	Description
user_idPK	INT	Primary key for the user
user_name	VARCHAR(100)	Meta Marketplace user name
region	VARCHAR(50)	User region
signup_date	DATE	Date the user signed up

transactions

Column	Type	Description
transaction_idPK	INT	Primary key for the transaction
user_id	INT	User who initiated the transaction
amount	DECIMAL(10,2)	Transaction amount
status	VARCHAR(20)	Transaction status
transaction_date	DATE	Date of the transaction

Sample Data

Example 1

Input

{"users":[[104,"Priya Shah","APAC","2024-01-11"],[101,"Ava Chen","NA","2023-11-03"],[106,"Noah Patel","EU","2024-02-20"],[103,"Liam Garcia","LATAM","2024-01-05"],[108,"Zoe Kim","APAC","2024-04-09"],[102,"Marcus Lee","EU","2023-12-14"],[105,"Elena Rossi",null,"2024-03-01"],[107,"Omar Khan","NA","2024-03-18"],["1","Jack Walker","Spain","2023-12-24"],["2","Omar Khan","India","2023-12-05"],["3","Alexander Clark","Japan","2024-04-12"],["4","Evelyn Harris","EU","2024-04-12"],["5","Benjamin White","Ger

Output[["102","Marcus Lee","2"],["103","Liam Garcia","2"]]

Practice SQL

PostgreSQL

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

Up next

Second Purchase Within 24 HoursEasy

Second Most Recent Marketplace PurchaseHard

Top Transaction Users by CountryMedium

Next question