Skip to content

Instantly share code, notes, and snippets.

@alkrauss48
Created December 16, 2022 18:57
Show Gist options
  • Save alkrauss48/1345e4ddc399fe5c9f67a5b249860ea7 to your computer and use it in GitHub Desktop.
Save alkrauss48/1345e4ddc399fe5c9f67a5b249860ea7 to your computer and use it in GitHub Desktop.
Soft Deleting duplicate transactions with the help of windowing functions
-- 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)
Sign up for free to join this conversation on GitHub. Already have an account? Sign in to comment