Analyze Transactions and Classify by Amount in SQL

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.

Total success amount per user

Return each user_id and the total amount of their successful transactions.

Table transactions

column	type	constraints
transaction_id	INTEGER	PRIMARY KEY, NOT NULL
user_id	INTEGER	NOT NULL
amount	DECIMAL(10,2)	NOT NULL
status	VARCHAR(20)	NOT NULL
ts	TIMESTAMP	NOT NULL
payment_country	VARCHAR(2)	NOT NULL

Sample data:

amount	payment_country	status	transaction_id	ts	user_id
120.5	US	success	101	2023-04-01 10:00:00	1
980	CN	fraud	102	2023-04-02 11:30:00	2
250	UK	success	103	2023-04-03 09:45:00	1
1500	US	success	104	2023-04-03 12:05:00	3

Expected output

One row per user with the sum of amounts for transactions where status = 'success'.

total_success_amount	user_id
370.5	1
1500	3

Hints

1. Filter rows with status = 'success'.
2. 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.

Table transactions

column	type	constraints
transaction_id	INTEGER	PRIMARY KEY, NOT NULL
user_id	INTEGER	NOT NULL
amount	DECIMAL(10,2)	NOT NULL
status	VARCHAR(20)	NOT NULL
ts	TIMESTAMP	NOT NULL
payment_country	VARCHAR(2)	NOT NULL

Sample data:

amount	payment_country	status	transaction_id	ts	user_id
120.5	US	success	101	2023-04-01 10:00:00	1
980	CN	fraud	102	2023-04-02 11:30:00	2
250	UK	success	103	2023-04-03 09:45:00	1
1500	US	success	104	2023-04-03 12:05:00	3

Expected output

All transactions with an amount_class column derived from amount thresholds.

amount	amount_class	transaction_id	user_id
120.5	medium	101	1
980	medium	102	2
250	medium	103	1
1500	large	104	3

Hints

1. Use a CASE expression based on amount.
2. Order of CASE conditions matters.

Users exceeding 1000 total

List users whose total successful transaction amount exceeds 1,000 using HAVING.

Table transactions

column	type	constraints
transaction_id	INTEGER	PRIMARY KEY, NOT NULL
user_id	INTEGER	NOT NULL
amount	DECIMAL(10,2)	NOT NULL
status	VARCHAR(20)	NOT NULL
ts	TIMESTAMP	NOT NULL
payment_country	VARCHAR(2)	NOT NULL

Sample data:

amount	payment_country	status	transaction_id	ts	user_id
120.5	US	success	101	2023-04-01 10:00:00	1
980	CN	fraud	102	2023-04-02 11:30:00	2
250	UK	success	103	2023-04-03 09:45:00	1
1500	US	success	104	2023-04-03 12:05:00	3

Expected output

Users whose sum of successful transaction amounts is greater than 1000.

total_success_amount	user_id
1500	3

Hints

1. Aggregate with GROUP BY user_id.
2. Filter aggregates using HAVING SUM(amount) > 1000.