Interview Guides

Top Transaction Users by Country | Dataford Interview Questions - Dataford - Ace your Interview

Top Transaction Users by Country

Medium

SQL & Data Manipulation

Asked at 2 companies3JoinsAggregationsRanking

Also asked at

Problem

Task

FinEdge wants to identify its highest-volume users in each country based on transaction activity. Write a PostgreSQL query to return the top 3 users per country by total transaction volume.

Requirements

Join users and transactions.
Calculate each user's total transaction volume per country using the sum of amount.
Exclude transactions where amount is NULL.
Return the top 3 users within each country using a window function.
Order the final output by country, then rank, then user_id.

Table Definitions

`users`

column	type	description
user_id	INT	Primary key for each user
user_name	VARCHAR(100)	User display name
country	VARCHAR(50)	User country
signup_date	DATE	Date the user registered

`transactions`

column	type	description
transaction_id	INT	Primary key for each transaction
user_id	INT	References `users.user_id`
amount	DECIMAL(10,2)	Transaction amount
transaction_date	DATE	Transaction date

Sample Data

`users`

user_id	user_name	country	signup_date
4	Diego	Mexico	2024-01-20
1	Alice	USA	2024-01-10
7	Grace	Canada	2024-03-01
2	Bob	USA	2024-01-12
9	Ivan	USA	2024-03-15
5	Elena	Mexico	2024-02-01

`transactions`

transaction_id	user_id	amount	transaction_date
201	2	300.00	2024-04-01
203	1	250.00	2024-04-03
210	5	400.00	2024-04-10
214	7	100.00	2024-04-14
216	10	500.00	2024-04-16
218	11	50.00	2024-04-18

Expected Output

country	user_id	user_name	total_volume	country_rank
Canada	8	Hannah	500.00	1
Canada	6	Farah	300.00	2
Canada	7	Grace	100.00	3
Mexico	5	Elena	400.00	1
Mexico	4	Diego	250.00	2
Mexico	11	Kara	50.00	3
USA	2	Bob	450.00	1
USA	1	Alice	350.00	2
USA	9	Ivan	300.00	3

Schema

users

Column	Type	Description
user_idPK	INT	Unique identifier for each user
user_name	VARCHAR(100)	User display name
country	VARCHAR(50)	Country associated with the user
signup_date	DATE	Date the user signed up

transactions

Column	Type	Description
transaction_idPK	INT	Unique identifier for each transaction
user_id	INT	User who made the transaction
amount	DECIMAL(10,2)	Transaction amount
transaction_date	DATE	Date of the transaction

Sample Data

Example 1

Input

{"users":[[4,"Diego","Mexico","2024-01-20"],[1,"Alice","USA","2024-01-10"],[7,"Grace","Canada","2024-03-01"],[2,"Bob","USA","2024-01-12"],[9,"Ivan","USA","2024-03-15"],[5,"Elena","Mexico","2024-02-01"],[3,"Cara","USA","2024-01-15"],[8,"Hannah","Canada","2024-03-10"],[6,"Farah","Canada","2024-02-20"],[11,"Kara","Mexico","2024-03-25"],["1","Hannah","Canada","2024-01-16"],["2","Elena","USA","2024-01-23"],["3","Elena","USA","2024-02-05"],["4","Elena","Canada","2024-01-22"],["5","Alice","Canada","202

Output

[["Canada","6","Farah","507.00","1"],["Canada","8","Hannah","500.00","2"],["Canada","7","Grace","100.00","3"],["Mexico","10","Bob","500.00","1"],["Mexico","5","Elena","400.00","2"],["Mexico","4","Diego","250.00","3"],["USA","2","Bob","839.00","1"],["USA","1","Alice","350.00","2"],["USA","9","Ivan","300.00","3"]]

Problem

Task

FinEdge wants to identify its highest-volume users in each country based on transaction activity. Write a PostgreSQL query to return the top 3 users per country by total transaction volume.

Requirements

Join users and transactions.
Calculate each user's total transaction volume per country using the sum of amount.
Exclude transactions where amount is NULL.
Return the top 3 users within each country using a window function.
Order the final output by country, then rank, then user_id.

Table Definitions

`users`

column	type	description
user_id	INT	Primary key for each user
user_name	VARCHAR(100)	User display name
country	VARCHAR(50)	User country
signup_date	DATE	Date the user registered

`transactions`

column	type	description
transaction_id	INT	Primary key for each transaction
user_id	INT	References `users.user_id`
amount	DECIMAL(10,2)	Transaction amount
transaction_date	DATE	Transaction date

Sample Data

`users`

user_id	user_name	country	signup_date
4	Diego	Mexico	2024-01-20
1	Alice	USA	2024-01-10
7	Grace	Canada	2024-03-01
2	Bob	USA	2024-01-12
9	Ivan	USA	2024-03-15
5	Elena	Mexico	2024-02-01

`transactions`

transaction_id	user_id	amount	transaction_date
201	2	300.00	2024-04-01
203	1	250.00	2024-04-03
210	5	400.00	2024-04-10
214	7	100.00	2024-04-14
216	10	500.00	2024-04-16
218	11	50.00	2024-04-18

Expected Output

country	user_id	user_name	total_volume	country_rank
Canada	8	Hannah	500.00	1
Canada	6	Farah	300.00	2
Canada	7	Grace	100.00	3
Mexico	5	Elena	400.00	1
Mexico	4	Diego	250.00	2
Mexico	11	Kara	50.00	3
USA	2	Bob	450.00	1
USA	1	Alice	350.00	2
USA	9	Ivan	300.00	3

Schema

users

Column	Type	Description
user_idPK	INT	Unique identifier for each user
user_name	VARCHAR(100)	User display name
country	VARCHAR(50)	Country associated with the user
signup_date	DATE	Date the user signed up

transactions

Column	Type	Description
transaction_idPK	INT	Unique identifier for each transaction
user_id	INT	User who made the transaction
amount	DECIMAL(10,2)	Transaction amount
transaction_date	DATE	Date of the transaction

Sample Data

Example 1

Input

{"users":[[4,"Diego","Mexico","2024-01-20"],[1,"Alice","USA","2024-01-10"],[7,"Grace","Canada","2024-03-01"],[2,"Bob","USA","2024-01-12"],[9,"Ivan","USA","2024-03-15"],[5,"Elena","Mexico","2024-02-01"],[3,"Cara","USA","2024-01-15"],[8,"Hannah","Canada","2024-03-10"],[6,"Farah","Canada","2024-02-20"],[11,"Kara","Mexico","2024-03-25"],["1","Hannah","Canada","2024-01-16"],["2","Elena","USA","2024-01-23"],["3","Elena","USA","2024-02-05"],["4","Elena","Canada","2024-01-22"],["5","Alice","Canada","202

Output

[["Canada","6","Farah","507.00","1"],["Canada","8","Hannah","500.00","2"],["Canada","7","Grace","100.00","3"],["Mexico","10","Bob","500.00","1"],["Mexico","5","Elena","400.00","2"],["Mexico","4","Diego","250.00","3"],["USA","2","Bob","839.00","1"],["USA","1","Alice","350.00","2"],["USA","9","Ivan","300.00","3"]]

Practice SQL

PostgreSQL

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

Up next

Second-Highest Marketplace Transactions UserMedium

Running Deposit Totals by UserMedium WAverage Order Value by RegionEasy

Next question

Top Transaction Users by Country

Medium

SQL & Data Manipulation

Asked at 2 companies3JoinsAggregationsRanking

Also asked at

Problem

Task

FinEdge wants to identify its highest-volume users in each country based on transaction activity. Write a PostgreSQL query to return the top 3 users per country by total transaction volume.

Requirements

Join users and transactions.
Calculate each user's total transaction volume per country using the sum of amount.
Exclude transactions where amount is NULL.
Return the top 3 users within each country using a window function.
Order the final output by country, then rank, then user_id.

Table Definitions

`users`

column	type	description
user_id	INT	Primary key for each user
user_name	VARCHAR(100)	User display name
country	VARCHAR(50)	User country
signup_date	DATE	Date the user registered

`transactions`

column	type	description
transaction_id	INT	Primary key for each transaction
user_id	INT	References `users.user_id`
amount	DECIMAL(10,2)	Transaction amount
transaction_date	DATE	Transaction date

Sample Data

`users`

user_id	user_name	country	signup_date
4	Diego	Mexico	2024-01-20
1	Alice	USA	2024-01-10
7	Grace	Canada	2024-03-01
2	Bob	USA	2024-01-12
9	Ivan	USA	2024-03-15
5	Elena	Mexico	2024-02-01

`transactions`

transaction_id	user_id	amount	transaction_date
201	2	300.00	2024-04-01
203	1	250.00	2024-04-03
210	5	400.00	2024-04-10
214	7	100.00	2024-04-14
216	10	500.00	2024-04-16
218	11	50.00	2024-04-18

Expected Output

country	user_id	user_name	total_volume	country_rank
Canada	8	Hannah	500.00	1
Canada	6	Farah	300.00	2
Canada	7	Grace	100.00	3
Mexico	5	Elena	400.00	1
Mexico	4	Diego	250.00	2
Mexico	11	Kara	50.00	3
USA	2	Bob	450.00	1
USA	1	Alice	350.00	2
USA	9	Ivan	300.00	3

Schema

users

Column	Type	Description
user_idPK	INT	Unique identifier for each user
user_name	VARCHAR(100)	User display name
country	VARCHAR(50)	Country associated with the user
signup_date	DATE	Date the user signed up

transactions

Column	Type	Description
transaction_idPK	INT	Unique identifier for each transaction
user_id	INT	User who made the transaction
amount	DECIMAL(10,2)	Transaction amount
transaction_date	DATE	Date of the transaction

Sample Data

Example 1

Input

{"users":[[4,"Diego","Mexico","2024-01-20"],[1,"Alice","USA","2024-01-10"],[7,"Grace","Canada","2024-03-01"],[2,"Bob","USA","2024-01-12"],[9,"Ivan","USA","2024-03-15"],[5,"Elena","Mexico","2024-02-01"],[3,"Cara","USA","2024-01-15"],[8,"Hannah","Canada","2024-03-10"],[6,"Farah","Canada","2024-02-20"],[11,"Kara","Mexico","2024-03-25"],["1","Hannah","Canada","2024-01-16"],["2","Elena","USA","2024-01-23"],["3","Elena","USA","2024-02-05"],["4","Elena","Canada","2024-01-22"],["5","Alice","Canada","202

Output

[["Canada","6","Farah","507.00","1"],["Canada","8","Hannah","500.00","2"],["Canada","7","Grace","100.00","3"],["Mexico","10","Bob","500.00","1"],["Mexico","5","Elena","400.00","2"],["Mexico","4","Diego","250.00","3"],["USA","2","Bob","839.00","1"],["USA","1","Alice","350.00","2"],["USA","9","Ivan","300.00","3"]]

Problem

Task

FinEdge wants to identify its highest-volume users in each country based on transaction activity. Write a PostgreSQL query to return the top 3 users per country by total transaction volume.

Requirements

Join users and transactions.
Calculate each user's total transaction volume per country using the sum of amount.
Exclude transactions where amount is NULL.
Return the top 3 users within each country using a window function.
Order the final output by country, then rank, then user_id.

Table Definitions

`users`

column	type	description
user_id	INT	Primary key for each user
user_name	VARCHAR(100)	User display name
country	VARCHAR(50)	User country
signup_date	DATE	Date the user registered

`transactions`

column	type	description
transaction_id	INT	Primary key for each transaction
user_id	INT	References `users.user_id`
amount	DECIMAL(10,2)	Transaction amount
transaction_date	DATE	Transaction date

Sample Data

`users`

user_id	user_name	country	signup_date
4	Diego	Mexico	2024-01-20
1	Alice	USA	2024-01-10
7	Grace	Canada	2024-03-01
2	Bob	USA	2024-01-12
9	Ivan	USA	2024-03-15
5	Elena	Mexico	2024-02-01

`transactions`

transaction_id	user_id	amount	transaction_date
201	2	300.00	2024-04-01
203	1	250.00	2024-04-03
210	5	400.00	2024-04-10
214	7	100.00	2024-04-14
216	10	500.00	2024-04-16
218	11	50.00	2024-04-18

Expected Output

country	user_id	user_name	total_volume	country_rank
Canada	8	Hannah	500.00	1
Canada	6	Farah	300.00	2
Canada	7	Grace	100.00	3
Mexico	5	Elena	400.00	1
Mexico	4	Diego	250.00	2
Mexico	11	Kara	50.00	3
USA	2	Bob	450.00	1
USA	1	Alice	350.00	2
USA	9	Ivan	300.00	3

Schema

users

Column	Type	Description
user_idPK	INT	Unique identifier for each user
user_name	VARCHAR(100)	User display name
country	VARCHAR(50)	Country associated with the user
signup_date	DATE	Date the user signed up

transactions

Column	Type	Description
transaction_idPK	INT	Unique identifier for each transaction
user_id	INT	User who made the transaction
amount	DECIMAL(10,2)	Transaction amount
transaction_date	DATE	Date of the transaction

Sample Data

Example 1

Input

{"users":[[4,"Diego","Mexico","2024-01-20"],[1,"Alice","USA","2024-01-10"],[7,"Grace","Canada","2024-03-01"],[2,"Bob","USA","2024-01-12"],[9,"Ivan","USA","2024-03-15"],[5,"Elena","Mexico","2024-02-01"],[3,"Cara","USA","2024-01-15"],[8,"Hannah","Canada","2024-03-10"],[6,"Farah","Canada","2024-02-20"],[11,"Kara","Mexico","2024-03-25"],["1","Hannah","Canada","2024-01-16"],["2","Elena","USA","2024-01-23"],["3","Elena","USA","2024-02-05"],["4","Elena","Canada","2024-01-22"],["5","Alice","Canada","202

Output

[["Canada","6","Farah","507.00","1"],["Canada","8","Hannah","500.00","2"],["Canada","7","Grace","100.00","3"],["Mexico","10","Bob","500.00","1"],["Mexico","5","Elena","400.00","2"],["Mexico","4","Diego","250.00","3"],["USA","2","Bob","839.00","1"],["USA","1","Alice","350.00","2"],["USA","9","Ivan","300.00","3"]]

Practice SQL

PostgreSQL

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

Up next

Second-Highest Marketplace Transactions UserMedium

Running Deposit Totals by UserMedium WAverage Order Value by RegionEasy

Next question