sqlmysql

How do I get all transactions that have been recurring on the same date in the past few months?

I have a table called transactions outlined below. Transaction_date is the day the payments were made.

id merchant category user type amount transaction_date
15 Tesco Groceries Wouter expense 5.20 2025-03-27
14 Electricity utilities Wouter expense 50.00 2025-03-15
13 Tesco Groceries Wouter expense 70.00 2025-03-12
12 Landlord rent Wouter expense 750.00 2025-03-02
11 amazon shopping Wouter expense 10.23 2025-02-26
10 Tesco Groceries Wouter expense 15.25 2025-02-22
9 Electricity utilities Wouter expense 50.00 2025-02-15
8 Tesco Groceries Wouter expense 6.25 2025-02-09
7 Landlord rent Wouter expense 750.00 2025-02-02
6 Tesco Groceries Wouter expense 17.20 2025-01-27
5 Electricity utilities Wouter expense 50.00 2025-01-15
4 Tesco Groceries Wouter expense 97.10 2025-01-11
3 amazon shopping Wouter expense 26.10 2025-01-10
2 amazon shopping Wouter expense 2.10 2025-01-09
1 Landlord rent Wouter expense 750.00 2025-01-01

I have been trying to create a SQL query that gets all recurring transactions grouped by merchant, category, user and amount that occurs every month for the past 3 months on the same day of the month (give or take a day). It should exclude records that don't have at least 1 transaction each month on that day.

So the end result I'm trying to get is this. Because both of these have transactions with the same amount, same type, same user, same category and same merchant on the same day every month the last 3 months (give or take a day).

id merchant category user type amount transaction_date day_of_the_month
14 Electricity utilities Wouter expense 50.00 2025-03-15 15
12 Landlord rent Wouter expense 750.00 2025-03-02 02

I have been trying to get that result and failing. I have this query, but it will only give me the transactions that occurred on today's date - 1 month and I have not been able to change it to get the result I want.

SELECT *
FROM transactions 
WHERE
    transaction_date = DATE_SUB(CURDATE(), INTERVAL 1 month) AND
    DAY(transaction_date) = DAY(DATE_SUB(CURDATE(), INTERVAL 1 month));

Can anyone with more knowledge of SQL help me with this?

Solution

  • This might return the results you want. The main improvment over your attempt is the self-join on the transactions table to match with the previous months transactions (day, merchant, and amount).

    SELECT Min(cur.ID) as id,
       cur.merchant, 
       min(cur.category) as category, 
       min(cur.user) as user, 
        min(cur.type) as type, 
       min(cur.amount) as amount, 
       max(cur.transaction_date) as last_transaction_date, 
       DAY(max(cur.transaction_date) ) as "Day of the month"
FROM transactions cur
INNER JOIN  transactions prev ON
  cur.transaction_date = DATE_SUB(prev.transaction_date, INTERVAL 1 month)
  AND DAY(cur.transaction_date) = DAY(DATE_SUB(prev.transaction_date, INTERVAL 1 
month))
  AND cur.merchant=prev.merchant 
  AND cur.amount=prev.amount
GROUP BY cur.merchant

    fiddle