Skip to content

Instantly share code, notes, and snippets.

@alkrauss48

alkrauss48/soft-delete-duplicate-transactions.sql

Created December 16, 2022 18:57
Show Gist options

  • Select an option

    Learn more about clone URLs
  • Save alkrauss48/1345e4ddc399fe5c9f67a5b249860ea7 to your computer and use it in GitHub Desktop.

Select an option

Learn more about clone URLs
Save alkrauss48/1345e4ddc399fe5c9f67a5b249860ea7 to your computer and use it in GitHub Desktop.
Download ZIP

Revisions

  1. alkrauss48 created this gist Dec 16, 2022.
    21 changes: 21 additions & 0 deletions soft-delete-duplicate-transactions.sql
    View file
    Original file line number Diff line number Diff line change
    @@ -0,0 +1,21 @@
    -- 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)