Quick Overview

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.

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.

Overview: 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.

You are given a `transactions` table with PayPal-style payment data. For the executable SQL part of this exercise, write a PostgreSQL query that aggregates GMV (gross merchandise volume) per `merchant` and `currency` by summing the `amount` column. Return exactly `merchant`, `currency`, and `gmv`, sorted by `gmv` descending and then `merchant` ascending. In an interview discussion, be ready to explain where window functions, join types, comma-separated merchant lists with `STRING_AGG`, and simple Python dictionary accumulation would fit, but the graded SQL output for this console is the GMV aggregation.

Tables

transactions(transaction_id INTEGER, user_id INTEGER, merchant VARCHAR(100), amount DECIMAL(10,2), currency CHAR(3), transaction_ts TIMESTAMP)

Hints

  1. Use `GROUP BY merchant, currency`.
  2. Keep the executable answer to one result set that matches the expected output.

Loading coding console...