Write SQL and Python for transaction analytics

You are given user and transaction data. Part A (SQL): Use a join and window functions to answer the prompts below using the following schema and sample data.

Schema:

Users(user_id INT PRIMARY KEY, name TEXT, country TEXT)
Transactions(transaction_id INT PRIMARY KEY, from_user_id INT, to_user_id INT, amount INT, created_at TIMESTAMP)

Sample tables:

Users
+---------+-------+---------+
| user_id | name | country |
+---------+-------+---------+
| 1 | Alice | US |
| 2 | Bob | US |
| 3 | Chen | CN |
| 4 | Divya | IN |
| 5 | Emma | US |
+---------+-------+---------+

Transactions
+----------------+--------------+------------+--------+---------------------+
| transaction_id | from_user_id | to_user_id | amount | created_at |
+----------------+--------------+------------+--------+---------------------+
| 1001 | 1 | 2 | 50 | 2025-08-30 10:00:00 |
| 1002 | 2 | 1 | -20 | 2025-08-31 09:00:00 |
| 1003 | 3 | 1 | 10 | 2025-09-01 11:00:00 |
| 1004 | 1 | 3 | -5 | 2025-09-02 12:00:00 |
| 1005 | 4 | 5 | 15 | 2025-09-03 08:00:00 |
+----------------+--------------+------------+--------+---------------------+

Interpretation rule: If amount < 0, the actual sender is to_user_id and the actual receiver is from_user_id, and the normalized amount is ABS(amount).

Tasks:

1. Write a single SQL query that returns, for every row in Transactions, the columns: sender_user_id, receiver_user_id, normalized_amount, created_at, sender_country, receiver_country. Use a join to Users and a CASE expression (or equivalent) to normalize direction based on the sign of amount.
2. Using window functions, produce a result set with columns (country, user_id, sent_txn_count, country_rank_by_sent) where sent_txn_count is the number of transactions the user sent (after direction normalization) and country_rank_by_sent is the dense rank of the user within their country ordered by sent_txn_count desc, then user_id asc for tie-breaks. Return only the top 3 users per country.

Part B (Python): You receive an iterable of records formatted as (transaction_id: int, pair: str "from_user_id:to_user_id", amount: int). Apply the same direction rule as above (amount < 0 means the transaction direction is reversed). Implement an efficient function that returns two lists: top 5 sender user_ids and top 5 receiver user_ids ranked by their respective transaction counts (descending count, then ascending user_id as a tie-breaker). Aim for O(n) time and O(u) additional space where u is the number of unique users; do not materialize per-transaction expansions beyond what is necessary.

Normalize transaction direction and enrich with sender/receiver countries

You are given two tables: Users and Transactions.

Interpretation rule (direction normalization):

• If amount >= 0, the sender is from_user_id and the receiver is to_user_id.
• If amount < 0, the sender is to_user_id and the receiver is from_user_id.
• normalized_amount is ABS(amount).

Write a single SQL query that returns, for every row in Transactions, exactly these columns:

• sender_user_id
• receiver_user_id
• normalized_amount
• created_at formatted as YYYY-MM-DD HH24:MI:SS
• sender_country
• receiver_country

You must join to Users to get sender_country and receiver_country, and use a CASE expression (or equivalent) to normalize direction based on the sign of amount.

Table Users

column	type	constraints
user_id	INT	PRIMARY KEY, NOT NULL
name	VARCHAR(50)	NOT NULL
country	VARCHAR(2)	NOT NULL

Sample data:

country	name	user_id
US	Alice	1
US	Bob	2
CN	Chen	3
IN	Divya	4
US	Emma	5

Table Transactions

column	type	constraints
transaction_id	INT	PRIMARY KEY, NOT NULL
from_user_id	INT	NOT NULL
to_user_id	INT	NOT NULL
amount	INT	NOT NULL
created_at	TIMESTAMP	NOT NULL

Sample data:

amount	created_at	from_user_id	to_user_id	transaction_id
50	2025-08-30 10:00:00	1	2	1001
-20	2025-08-31 09:00:00	2	1	1002
10	2025-09-01 11:00:00	3	1	1003
-5	2025-09-02 12:00:00	1	3	1004
15	2025-09-03 08:00:00	4	5	1005

Expected output

One row per transaction with normalized sender/receiver direction, absolute amount, and each side's country.

created_at	normalized_amount	receiver_country	receiver_user_id	sender_country	sender_user_id
2025-08-30 10:00:00	50	US	2	US	1
2025-08-31 09:00:00	20	US	2	US	1
2025-09-01 11:00:00	10	US	1	CN	3
2025-09-02 12:00:00	5	US	1	CN	3
2025-09-03 08:00:00	15	US	5	IN	4

Hints

1. Use CASE WHEN amount < 0 to swap sender and receiver IDs for negative transactions.
2. Join Users twice: once for the normalized sender and once for the normalized receiver.
3. Use TO_CHAR(t.created_at, 'YYYY-MM-DD HH24:MI:SS') for the output timestamp.

Top senders per country using window functions after direction normalization

Using the same Users and Transactions tables and the same direction normalization rule:

• If amount >= 0, sender = from_user_id
• If amount < 0, sender = to_user_id

Produce a result set with columns:

• country
• user_id
• sent_txn_count (number of transactions the user sent after normalization)
• country_rank_by_sent

Where country_rank_by_sent is the DENSE_RANK of the user within their country ordered by:

1. sent_txn_count DESC
2. user_id ASC (tie-break)

Return only the top 3 users per country (i.e., country_rank_by_sent <= 3).

Table Users

column	type	constraints
user_id	INT	PRIMARY KEY, NOT NULL
name	VARCHAR(50)	NOT NULL
country	VARCHAR(2)	NOT NULL

Sample data:

country	name	user_id
US	Alice	1
US	Bob	2
CN	Chen	3
IN	Divya	4
US	Emma	5

Table Transactions

column	type	constraints
transaction_id	INT	PRIMARY KEY, NOT NULL
from_user_id	INT	NOT NULL
to_user_id	INT	NOT NULL
amount	INT	NOT NULL
created_at	TIMESTAMP	NOT NULL

Sample data:

amount	created_at	from_user_id	to_user_id	transaction_id
50	2025-08-30 10:00:00	1	2	1001
-20	2025-08-31 09:00:00	2	1	1002
10	2025-09-01 11:00:00	3	1	1003
-5	2025-09-02 12:00:00	1	3	1004
15	2025-09-03 08:00:00	4	5	1005

Expected output

Top 3 users per country ranked by number of sent transactions (after normalizing direction). Includes users with 0 sends if they fall in the top 3 for their country.

country	country_rank_by_sent	sent_txn_count	user_id
CN	1	2	3
IN	1	1	4
US	1	2	1
US	2	0	2
US	3	0	5

Hints

1. First normalize the sender_user_id in a CTE, then aggregate counts per user.
2. Use LEFT JOIN from Users to counts so users with 0 sent transactions can still appear.
3. Apply DENSE_RANK() partitioned by country and filter to rank <= 3.