Write SQL using HAVING and window functions
Company: PayPal
Role: Data Scientist
Category: Data Manipulation (SQL/Python)
Difficulty: easy
Interview Round: Technical Screen
## Context
You work on fraud analytics. Assume the following schema (PostgreSQL-like types):
### `transactions`
- `txn_id` BIGINT (PK)
- `merchant_id` BIGINT
- `user_id` BIGINT
- `region` TEXT
- `amount_usd` NUMERIC(12,2)
- `created_at` TIMESTAMP (UTC)
- `is_fraud` BOOLEAN
## Question 1 (uses `HAVING`)
For the **last 30 days** (relative to `CURRENT_DATE`, assume UTC), find merchants that:
- Have **at least 100 transactions**, and
- Have a **fraud rate > 3%** (fraud rate = `#fraud_txns / #total_txns`).
**Output columns:** `merchant_id`, `total_txns`, `fraud_txns`, `fraud_rate`.
## Question 2 (uses window functions)
For each `user_id`, compute for every transaction:
- The user’s **running count of transactions in the prior 7 days** ending at the transaction time (inclusive).
- The user’s **rank of the transaction by recency** (1 = most recent transaction for that user).
**Output columns:** `txn_id`, `user_id`, `created_at`, `txns_last_7d`, `recency_rank`.
Quick Answer: This question evaluates SQL data manipulation competency, focusing on aggregation filtering with HAVING and the use of window functions for running counts and ranking within a fraud analytics scenario; Category: Data Manipulation (SQL/Python).