I'm having some trouble with a sqlite query:

select t.* from transactions t
join (
    select description, amount, created_at, count(*) from transactions
    where account_id = 1
    group by description, amount having count(*) > 1
) t2
ON t.description = t2.description
    and t.amount = t2.amount
    and strftime('%s', t2.created_at) - strftime('%s', t.created_at) between -60 and 60
;

Basically, I have a table with 3 important columns, I want to get duplicated rows if 3 conditions meet:

1. same description.
2. same amount.
3. the duplicated rows must have been created within 60 seconds apart.

It works partially, if the difference in time is between -60 and 60 then it shows 2 duplicated rows correctly, anything beyond that range it shows only one which is not supposed to happen.

Sample data:

insert into transactions (description, amount, created_at) values ('Internet', 19.99, '2021-11-29 11:30:00');
insert into transactions (description, amount, created_at) values ('Internet', 12.99, '2021-11-29 11:31:00');

When executing the query with these values nothing should be shown. Because the amount is different even if the dates are within 1 minute.

insert into transactions (description, amount, created_at) values ('Internet', 12.99, '2021-11-29 11:33:00');
insert into transactions (description, amount, created_at) values ('Internet', 12.99, '2021-11-29 11:35:00');

When executing the query with these values nothing should be shown. Because the dates are not within 1 minute.

insert into transactions (description, amount, created_at) values ('Internet', 19.99, '2021-11-29 11:30:00');
insert into transactions (description, amount, created_at) values ('Internet', 19.99, '2021-11-29 11:31:00');

When executing the query with these values it must show both rows.

CodePudding user response：

You can do it with EXISTS:

SELECT t1.*
FROM transactions t1
WHERE EXISTS (
  SELECT 1 
  FROM transactions t2
  WHERE t2.rowid <> t1.rowid
    AND t2.description = t1.description
    AND t2.amount = t1.amount
    AND ABS(strftime('%s', t2.created_at) - strftime('%s', t1.created_at)) <= 60
);

See the demo.