Analyze Transaction Flow and User Engagement Efficiently

transactions

+---------------+-------------+------------+--------+
| transaction_id| from_user_id| to_user_id | amount |
+---------------+-------------+------------+--------+
| 1             | 101         | 202        | 50     |
| 2             | 303         | 101        | -20    |
| 3             | 101         | 404        | 30     |
| 4             | 202         | 303        | 100    |
| 5             | 404         | 101        | -60    |
+---------------+-------------+------------+--------+

users

+---------+----------+
| user_id | name     |
+---------+----------+
| 101     | Alice    |
| 202     | Bob      |
| 303     | Carol    |
| 404     | Dave     |
+---------+----------+

Scenario

A peer-to-peer payments start-up wants quick insights from its transaction log and user directory to understand money flow and user engagement.

Question

SQL – Using tables users(user_id, name) and transactions(transaction_id, from_user_id, to_user_id, amount), write a query that joins the two tables to return each user’s name, total_amount_sent and total_amount_received.

SQL – Add a window function to the previous query to show, for every transaction, the running cumulative amount_sent per from_user_id ordered by transaction_id.

Python – Given the same transactions dataframe, output two Series: the top 5 user_ids by number of sent transactions and the top 5 user_ids by number of received transactions (treat negative amounts as money flowing in the opposite direction).

Hints

For SQL, think LEFT JOINs/Aggregate and SUM with PARTITION BY. For Python, normalise sender/receiver per row, then value_counts.

Totals sent and received

You work with a peer-to-peer payments system with tables users(user_id, name) and transactions(transaction_id, from_user_id, to_user_id, amount). For each user, return name, total_amount_sent, and total_amount_received. Treat negative transaction amounts as reversing direction: if amount < 0, the true sender is to_user_id and the true recipient is from_user_id, and the transfer size is ABS(amount).

Table transactions

column	type	constraints
transaction_id	INTEGER	PRIMARY KEY, NOT NULL
from_user_id	INTEGER	NOT NULL, FOREIGN KEY REFERENCES users(user_id)
to_user_id	INTEGER	NOT NULL, FOREIGN KEY REFERENCES users(user_id)
amount	DECIMAL(10,2)	NOT NULL

Sample data:

amount	from_user_id	to_user_id	transaction_id
50	101	202	1
-20	303	101	2
30	101	404	3
100	202	303	4
-60	404	101	5

Table users

column	type	constraints
user_id	INTEGER	PRIMARY KEY, NOT NULL
name	VARCHAR(100)	NOT NULL

Sample data:

name	user_id
Alice	101
Bob	202
Carol	303
Dave	404

Expected output

Each user's total amount sent and received, with negative amounts treated as reversed-direction transfers using absolute values.

name	total_amount_received	total_amount_sent
Alice	0	160
Bob	50	100
Carol	120	0
Dave	90	0

Hints

1. Normalize direction using CASE and ABS(amount).
2. Aggregate separately for sent and received, then join to users.

Running sent per sender

Using the same users and transactions tables, return every transaction along with a running cumulative sum of the transaction amount per from_user_id, ordered by transaction_id. Use the raw signed amount values (negative amounts decrease the running total). Include sender and recipient names.

Table transactions

column	type	constraints
transaction_id	INTEGER	PRIMARY KEY, NOT NULL
from_user_id	INTEGER	NOT NULL, FOREIGN KEY REFERENCES users(user_id)
to_user_id	INTEGER	NOT NULL, FOREIGN KEY REFERENCES users(user_id)
amount	DECIMAL(10,2)	NOT NULL

Sample data:

amount	from_user_id	to_user_id	transaction_id
50	101	202	1
-20	303	101	2
30	101	404	3
100	202	303	4
-60	404	101	5

Table users

column	type	constraints
user_id	INTEGER	PRIMARY KEY, NOT NULL
name	VARCHAR(100)	NOT NULL

Sample data:

name	user_id
Alice	101
Bob	202
Carol	303
Dave	404

Expected output

Each transaction with the running cumulative sum of raw signed amounts sent by that sender (from_user_id), ordered by transaction_id.

amount	from_user_id	from_user_name	running_amount_sent	to_user_id	to_user_name	transaction_id
50	101	Alice	50	202	Bob	1
-20	303	Carol	-20	101	Alice	2
30	101	Alice	80	404	Dave	3
100	202	Bob	100	303	Carol	4
-60	404	Dave	-60	101	Alice	5

Hints

1. Use SUM(amount) OVER (PARTITION BY from_user_id ORDER BY transaction_id).
2. Join users twice to get sender and recipient names.