Compute Total Spent in 2023 Excluding Refunds

transactions

+----------------+---------+-------------+--------+------------------+
| transaction_id | user_id | merchant_id | amount | transaction_date |
+----------------+---------+-------------+--------+------------------+
| 1              | 101     | 2001        | 120    | 2023-01-10       |
| 2              | 102     | 2002        | -20    | 2023-02-14       |
| 3              | 101     | 2001        | 30     | 2022-12-31       |
| 4              | 103     | 2003        | 55     | 2023-03-05       |
| 5              | 101     | 2002        | 75     | 2023-04-17       |
+----------------+---------+-------------+--------+------------------+

Scenario

Visa analytics role – technical round requiring the same data-manipulation task solved first in Python and then in SQL.

Question

Using Python (pandas), compute each user’s total amount spent in 2023, excluding refunds (negative amounts). Return user_id and total_spent sorted by total_spent descending. Rewrite the same solution in SQL, using a CTE and GROUP BY.

Hints

Filter dates, ignore negative amounts, aggregate, sort.

Total amount spent per user in 2023 excluding refunds

Using SQL with a CTE and GROUP BY, compute each user's total amount spent in calendar year 2023, excluding refunds (negative amounts). Return user_id and total_spent sorted by total_spent in descending order. Only include users who have at least one positive-amount transaction in 2023.

Table transactions

column	type	constraints
transaction_id	INTEGER	PRIMARY KEY, NOT NULL
user_id	INTEGER	NOT NULL
merchant_id	INTEGER	NOT NULL
amount	DECIMAL(10,2)	NOT NULL
transaction_date	DATE	NOT NULL

Sample data:

amount	merchant_id	transaction_date	transaction_id	user_id
120	2001	2023-01-10	1	101
-20	2002	2023-02-14	2	102
30	2001	2022-12-31	3	101
55	2003	2023-03-05	4	103
75	2002	2023-04-17	5	101

Expected output

For each user with at least one positive-amount transaction in calendar year 2023, sum the amounts and order by the sum descending. Users with only refunds (negative amounts) in 2023 are excluded.

total_spent	user_id
195	101
55	103

Hints

1. Filter to 2023 using a date range
2. Exclude negative amounts (refunds)
3. Aggregate with SUM grouped by user_id
4. Order by total_spent descending
5. Use a CTE to prefilter then aggregate