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 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?

• sql
• mysql

Share Improve this question Follow edited Mar 27 at 13:18 Wouter Bosch asked Mar 27 at 12:40 Wouter BoschWouter Bosch 10999 bronze badges 6

• Why are you using CURDATE()? Don't you want transaction_date instead? – Bart McEndree Commented Mar 27 at 12:56
• Do you have somewhere a note of what day a payment is to be made? If not then I don't think possible. eg payment 1 on 14th payment 2 on 15th payment 3 on 16th - here there is more than 1 day between payment 1 and payment 3 an according to the one day rule should not be included – P.Salmon Commented Mar 27 at 13:05
• The transaction_date is the day the payment was made. I'll put that in the post as well to clear things up – Wouter Bosch Commented Mar 27 at 13:10
• I get that but when is it SUPPOSED to be made as opposed to when it is actually made. – P.Salmon Commented Mar 27 at 13:12
• I'm sorry I do not understand your question. These are all transactions that have already happened. See it as a sort of logs table. The transaction date is when it happened. There is no difference between the supposed and actually made. – Wouter Bosch Commented Mar 27 at 13:14

 |  Show 1 more comment

3 Answers 3

Sorted by: Reset to default 1

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

Another way is to use FIRST_VALUE():

SELECT merchant, category, user, type, amount, dom FROM (
    SELECT t.*, DAY(transaction_date) AS dom,
        FIRST_VALUE(transaction_date) OVER(PARTITION BY merchant, category, user, type, amount, DAY(transaction_date)
            ORDER BY transaction_date DESC RANGE BETWEEN INTERVAL '3' MONTH PRECEDING AND INTERVAL '1' DAY PRECEDING
        ) AS fv
        , ROW_NUMBER() OVER(PARTITION BY merchant, category, user, type, amount, DAY(transaction_date)
          ORDER BY transaction_date ASC) AS rn
    FROM transactions t
) d
WHERE fv IS NOT NULL AND rn = 1
;

MERCHANT    CATEGORY  USR    TYPE        AMOUNT        DOM
----------- --------- ------ ------- ---------- ----------
Electricity utilities Wouter expense         50         15
Landlord    rent      Wouter expense        750          2

https://dbfiddle.uk/XxSjmy00

(you could also sort the row_number() by transaction_date DESC to have twice the same partition sorting, and then filter on rn = 2, because there will not be a row with fv not null and rn = 1)

Possible get desired rows by grouping and filter rows in HAVING clause

group by merchant,category,user,amount,day(transaction_date)

First example. Take payments repeated 3 last month on same merchant,category,user,amount,day of month

select max(id) id, merchant,category,user,amount,max(transaction_date) transaction_date
  ,day(transaction_date) day_of_the_month
from transactions t
where transaction_date>(curdate() - interval 3 month)
group by merchant,category,user,amount,day(transaction_date)
having count(*)>=3

Output

id	merchant	category	user	amount	transaction_date	day_of_the_month
14	Electricity	utilities	Wouter	50	2025-03-15	15

1. If you want take payments on the same day every month the last 3 months then

having count(*)>=3   -- every month in last 3 month

2. If you want take payments on the same day more than once in last 3 months then

having count(*)>1   

3. If you want take payments on the same day more than once in last 3 months including current month then

having count(*)>1
  and (max(month(transaction_date))-min(month(transaction_date))+1)=count(*)
  and max(month(transaction_date))=month(curdate())  

This is example, where more than once payment in the same day of month, including current month, without skipped months:

for (Landlord , rent , Wouter , 750) payment dates ('2025-01-01','2025-02-02','2025-03-02') - recurrence last 2 month

select max(id) id, merchant,category,user,amount,max(transaction_date) transaction_date
  ,day(transaction_date) day_of_the_month
from transactions t
where transaction_date>(curdate() - interval 3 month)
group by merchant,category,user,amount,day(transaction_date)
having count(*)>1
             -- check skipped months
  and (max(month(transaction_date))-min(month(transaction_date))+1)=count(*)
  and max(month(transaction_date))=month(curdate())
order by merchant,category,user,amount

id	merchant	category	user	amount	transaction_date	day_of_the_month
14	Electricity	utilities	Wouter	50	2025-03-15	15
12	Landlord	rent	Wouter	750	2025-03-02	2

fiddle

With test data (order by merchant,category,user,amount)

id	merchant	category	user	type	amount	transaction_date
2	amazon	shopping	Wouter	expense	2.1	2025-01-09
11	amazon	shopping	Wouter	expense	10.23	2025-02-26
3	amazon	shopping	Wouter	expense	26.1	2025-01-10
14	Electricity	utilities	Wouter	expense	50	2025-03-15
9	Electricity	utilities	Wouter	expense	50	2025-02-15
5	Electricity	utilities	Wouter	expense	50	2025-01-15
12	Landlord	rent	Wouter	expense	750	2025-03-02
7	Landlord	rent	Wouter	expense	750	2025-02-02
1	Landlord	rent	Wouter	expense	750	2025-01-01
15	Tesco	Groceries	Wouter	expense	5.2	2025-03-27
8	Tesco	Groceries	Wouter	expense	6.25	2025-02-09
10	Tesco	Groceries	Wouter	expense	15.25	2025-02-22
6	Tesco	Groceries	Wouter	expense	17.2	2025-01-27
13	Tesco	Groceries	Wouter	expense	70	2025-03-12
4	Tesco	Groceries	Wouter	expense	97.1	2025-01-11