Katkat
Back

Explain Window Functions and Joins in SQL and Python

PayPalData Scientist · Onsite
MediumData ScientistOnsiteData Manipulation (SQL/Python)

Last updated

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.

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.