Explain Window Functions and Joins in SQL and Python

Q: How do I practice SQL interview questions?

PracHub provides an interactive SQL console where you can write and test queries against real database schemas. Get instant feedback and compare your solution with the expected output.

Q: What difficulty level is this coding question?

This is a medium difficulty Data Manipulation (SQL/Python) question, commonly asked during Onsite rounds at PayPal.

Q: What role is this question designed for?

This question is commonly asked for Data Scientist candidates at PayPal during technical interviews.

Question

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.

PracHub · Accepted 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.

Quick Overview

Explain Window Functions and Joins in SQL and Python