Soft Deleting duplicate transactions with the help of windowing functions

soft-delete-duplicate-transactions.sql

-- DB: PostgreSQL
-- Soft-delete duplicate transactions made by the same user
-- of the same organization with the same amount within 3 seconds
-- of the last matched transaction
-- The first instance of a match will have a null time_diff, so it
-- will naturally not get included in duplicate_transactions

UPDATE transactions
SET deleted_at = now()
FROM (
	SELECT id FROM (
		SELECT
		*,
		ROW_NUMBER() OVER (PARTITION BY user_id, organization_id, amount ORDER BY created_at ASC) AS ordering,
		LAG(created_at) OVER (PARTITION BY user_id, organization_id, amount ORDER BY created_at ASC) AS last_transaction_time,
		created_at - LAG(created_at) OVER (PARTITION BY user_id, organization_id, amount ORDER BY created_at ASC) AS time_diff
		FROM transactions
	) windowed_transactions
	where windowed_transactions.time_diff < INTERVAL '3 seconds'
) duplicate_transactions
where transactions.id in (duplicate_transactions.id)