Need help writing SQL query to filter records by date range and amount criteria

Data and Databases
1

I’m working with a database table that contains the following information:

user_id | amount | transaction_date
----------------------------------
205     | 15     | 2012/01/15
205     | 20     | 2012/02/10
205     | 5      | 2012/03/20
205     | 7      | 2012/04/05
205     | 25     | 2012/06/12
...
888     | 12     | 2012/08/03
888     | 45     | 2012/09/18

I’m looking to create a SQL statement that retrieves all records from the last three months for each user_id, considering their last transaction_date. Additionally, I want to filter out records where the amount is below 10.

Since the last transaction dates vary by user_id, I need to calculate the three-month period for each user individually.

Could someone assist me with the SQL syntax necessary for this type of query?

2

You can solve this using a window function to find each user’s maximum transaction date, then filter based on that. Here’s the approach:

WITH user_max_dates AS (
  SELECT user_id, MAX(transaction_date) as last_transaction
  FROM your_table_name
  GROUP BY user_id
)
SELECT t.*
FROM your_table_name t
JOIN user_max_dates umd ON t.user_id = umd.user_id
WHERE t.transaction_date >= DATE_SUB(umd.last_transaction, INTERVAL 3 MONTH)
  AND t.amount >= 10;

This query first identifies the latest transaction date for each user, then joins back to filter records within three months of that date while excluding amounts below 10. The DATE_SUB function works in MySQL - adjust to DATE_TRUNC or similar depending on your database system.

3

hmm this looks tricky! are you using a specific database like mysql or postgres? the date functions can vary between them. also just curious - when you say “last three months” do you mean exactly 90 days or calendar months? that might affect the solution approach

4

another aproach would be using ROW_NUMBER() if you want more control over the filtering. something like SELECT * FROM (SELECT *, ROW_NUMBER() OVER (PARTITION BY user_id ORDER BY transaction_date DESC) as rn FROM table WHERE amount >= 10) WHERE transaction_date >= DATEADD(month, -3, max_date) but you’d need to tweek it for your specific db engine