Explain Window Functions and Joins in SQL and Python

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.

GMV per Merchant, Merchants per User, and Window Function Basics

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.

Table transactions

column	type	constraints
transaction_id	INTEGER	PRIMARY KEY, NOT NULL
user_id	INTEGER	NOT NULL
merchant	VARCHAR(100)	NOT NULL
amount	DECIMAL(10,2)	NOT NULL
currency	CHAR(3)	NOT NULL
transaction_ts	TIMESTAMP	NOT NULL

Sample data:

amount	currency	merchant	transaction_id	transaction_ts	user_id
45.8	USD	Walmart	1001	2023-07-12 14:02:08	17
23.15	USD	Target	1002	2023-07-12 15:10:44	22
120.99	USD	Walmart	1003	2023-07-13 09:21:11	17
65	USD	eBay	1004	2023-07-13 10:05:33	30

Expected output

GMV per merchant and currency, summing amount for each (merchant, currency) pair.

currency	gmv	merchant
USD	166.79	Walmart
USD	65	eBay
USD	23.15	Target

Hints

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