Explain Window Functions and Joins in SQL and Python
Company: PayPal
Role: Data Scientist
Category: Data Manipulation (SQL/Python)
Difficulty: medium
Interview Round: Onsite
TABLE transactions
| transaction_id | user_id | merchant | amount | currency | transaction_ts |
| 1001 | 17 | Walmart | 45.80 | USD | 2023-07-12 14:02:08 |
| 1002 | 22 | Target | 23.15 | USD | 2023-07-12 15:10:44 |
| 1003 | 17 | Walmart | 120.99 | USD | 2023-07-13 09:21:11 |
| 1004 | 30 | eBay | 65.00 | USD | 2023-07-13 10:05:33 |
##### Scenario
SQL and Python data-manipulation screen using PayPal payment data.
##### Question
Explain window functions and list common use cases. Describe INNER, LEFT, RIGHT, FULL and CROSS joins. Difference between RANK() and DENSE_RANK(). Write a query that aggregates GMV per merchant (basic GROUP BY). Return each user’s merchants as a comma-separated string, handling type casts between DECIMAL and STRING when needed. Using Python, iterate through a list of dictionaries representing rows and build a mapping of user_id ➜ total_amount.
##### Hints
Show clean SQL, window-function syntax, GROUP_CONCAT/STRING_AGG usage, and Python dict accumulation.
Quick Answer: This question evaluates proficiency in SQL and Python data manipulation, covering window functions, join semantics (INNER, LEFT, RIGHT, FULL, CROSS), ranking functions, group and string aggregation, type casting, and building row-wise accumulations in Python, and it falls under the Data Manipulation (SQL/Python) category.