Analyze Transactions and Classify by Amount in SQL
Company: PayPal
Role: Data Scientist
Category: Data Manipulation (SQL/Python)
Difficulty: medium
Interview Round: Onsite
transactions
+---------------+---------+--------+---------+---------------------+-----------------+
| transaction_id| user_id | amount | status | ts | payment_country |
+---------------+---------+--------+---------+---------------------+-----------------+
| 101 | 1 | 120.50 | success | 2023-04-01 10:00:00 | US |
| 102 | 2 | 980.00 | fraud | 2023-04-02 11:30:00 | CN |
| 103 | 1 | 250.00 | success | 2023-04-03 09:45:00 | UK |
| 104 | 3 | 1500.00| success | 2023-04-03 12:05:00 | US |
+---------------+---------+--------+---------+---------------------+-----------------+
##### Scenario
You are asked to explore the transactions table and answer ad-hoc SQL questions during a live interview.
##### Question
Return each user_id and the total amount of their successful transactions.
2) Add a column that classifies each transaction as 'small' (<
100), 'medium' (100-
999), or 'large' (>=
1000) using CASE WHEN.
3) List users whose total successful transaction amount exceeds 1,000; use an aggregation in the HAVING clause.
##### Hints
Be explicit with CASE syntax, GROUP BY vs HAVING, and mention dialect differences if relevant.
Overview: This question evaluates SQL data manipulation competencies, including aggregation, grouping vs HAVING for filtered aggregations, and conditional classification using CASE WHEN on transactional data.
Total success amount per user
Return each user_id and the total amount of their successful transactions.
Tables
transactions(transaction_id INTEGER, user_id INTEGER, amount DECIMAL(10,2), status VARCHAR(20), ts TIMESTAMP, payment_country VARCHAR(2))
Hints
- Filter rows with status = 'success'.
- Use SUM(amount) grouped by user_id.
Classify transactions by amount
Add a column that classifies each transaction as 'small' (<100), 'medium' (100-999), or 'large' (>=1000) using CASE.
Tables
transactions(transaction_id INTEGER, user_id INTEGER, amount DECIMAL(10,2), status VARCHAR(20), ts TIMESTAMP, payment_country VARCHAR(2))
Hints
- Use a CASE expression based on amount.
- Order of CASE conditions matters.
Users exceeding 1000 total
List users whose total successful transaction amount exceeds 1,000 using HAVING.
Tables
transactions(transaction_id INTEGER, user_id INTEGER, amount DECIMAL(10,2), status VARCHAR(20), ts TIMESTAMP, payment_country VARCHAR(2))
Hints
- Aggregate with GROUP BY user_id.
- Filter aggregates using HAVING SUM(amount) > 1000.