Write SQL and Python for transaction analytics
Transaction Analytics with SQL and Python
You are provided with user and transaction data. Your task involves two parts: first, using SQL to perform joins and window functions for specific analytical queries, and second, implementing an efficient Python function for transaction analytics.
Schema and Sample Data
Users Table
| user_id (INT PRIMARY KEY) | name (TEXT) | country (TEXT) |
|---|---|---|
| 1 | Alice | US |
| 2 | Bob | US |
| 3 | Chen | CN |
| 4 | Divya | IN |
| 5 | Emma | US |
Transactions Table
| transaction_id (INT PRIMARY KEY) | from_user_id (INT) | to_user_id (INT) | amount (INT) | created_at (TIMESTAMP) |
|---|---|---|---|---|
| 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. The normalized amount for such transactions is ABS(amount).
Part A (SQL)
- Transaction Normalization: Write a single SQL query that returns, for every row in
Transactions, the following columns:sender_user_id,receiver_user_id,normalized_amount,created_at,sender_country,receiver_country. This query must use aJOINto theUserstable and aCASEexpression (or equivalent) to normalize the transaction direction based on the sign of theamount. - Country-wise Sender Ranking: Using window functions, produce a result set with columns (
country,user_id,sent_txn_count,country_rank_by_sent).sent_txn_countshould represent the total number of transactions the user sent (after applying the direction normalization rule).country_rank_by_sentshould be the dense rank of the user within their respective country, ordered bysent_txn_countin descending order, and then byuser_idin ascending order for tie-breaks.- Return only the top 3 users per country based on this ranking.
Part B (Python)
You will receive an iterable of records, each formatted as a tuple: (transaction_id: int, pair: str "from_user_id:to_user_id", amount: int). Apply the same direction rule as above (if amount < 0, the transaction direction is reversed).
Implement an efficient function that returns two lists: the top 5 sender user_ids and the top 5 receiver user_ids. These lists should be ranked by their respective transaction counts (descending count, then ascending user_id as a tie-breaker).
Constraints:
- Aim for
O(n)time complexity, wherenis the number of transactions. - Aim for
O(u)additional space complexity, whereuis the number of unique users. - Do not materialize per-transaction expansions beyond what is necessary.
AI interview prep
Core dilemma
The central engineering conflict is correctly and efficiently applying complex conditional business logic (transaction direction normalization) across different data processing paradigms (SQL and Python) while adhering to strict performance and resource constraints for large datasets.
3 tradeoffs and 3 stress tests locked
Interview prep
Unlock the interviewer X-Ray rubric, tradeoff matrix, stress tests, clarifying questions, and key concepts.
Write SQL and Python for transaction analytics
Transaction Analytics with SQL and Python
You are provided with user and transaction data. Your task involves two parts: first, using SQL to perform joins and window functions for specific analytical queries, and second, implementing an efficient Python function for transaction analytics.
Schema and Sample Data
Users Table
| user_id (INT PRIMARY KEY) | name (TEXT) | country (TEXT) |
|---|---|---|
| 1 | Alice | US |
| 2 | Bob | US |
| 3 | Chen | CN |
| 4 | Divya | IN |
| 5 | Emma | US |
Transactions Table
| transaction_id (INT PRIMARY KEY) | from_user_id (INT) | to_user_id (INT) | amount (INT) | created_at (TIMESTAMP) |
|---|---|---|---|---|
| 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. The normalized amount for such transactions is ABS(amount).
Part A (SQL)
- Transaction Normalization: Write a single SQL query that returns, for every row in
Transactions, the following columns:sender_user_id,receiver_user_id,normalized_amount,created_at,sender_country,receiver_country. This query must use aJOINto theUserstable and aCASEexpression (or equivalent) to normalize the transaction direction based on the sign of theamount. - Country-wise Sender Ranking: Using window functions, produce a result set with columns (
country,user_id,sent_txn_count,country_rank_by_sent).sent_txn_countshould represent the total number of transactions the user sent (after applying the direction normalization rule).country_rank_by_sentshould be the dense rank of the user within their respective country, ordered bysent_txn_countin descending order, and then byuser_idin ascending order for tie-breaks.- Return only the top 3 users per country based on this ranking.
Part B (Python)
You will receive an iterable of records, each formatted as a tuple: (transaction_id: int, pair: str "from_user_id:to_user_id", amount: int). Apply the same direction rule as above (if amount < 0, the transaction direction is reversed).
Implement an efficient function that returns two lists: the top 5 sender user_ids and the top 5 receiver user_ids. These lists should be ranked by their respective transaction counts (descending count, then ascending user_id as a tie-breaker).
Constraints:
- Aim for
O(n)time complexity, wherenis the number of transactions. - Aim for
O(u)additional space complexity, whereuis the number of unique users. - Do not materialize per-transaction expansions beyond what is necessary.