Skip to content

Instantly share code, notes, and snippets.

@ramses-lopez

ramses-lopez/find_duplicates.sql

Created May 21, 2019 20:40
Show Gist options
  • Save ramses-lopez/ab7abdcff685f40faeb9977bbc392fa5 to your computer and use it in GitHub Desktop.
Save ramses-lopez/ab7abdcff685f40faeb9977bbc392fa5 to your computer and use it in GitHub Desktop.
Download ZIP
Example query for finding duplicates and using window functions
Raw
find_duplicates.sql
SELECT
opportunities.id "opp_id",
title,
expiry_date,
ROW_NUMBER() OVER dupes,
FIRST_VALUE(opportunities.id) OVER dupes "parent"
FROM opportunities
WINDOW dupes AS (PARTITION BY opportunities.title, opportunities.expiry_date ORDER BY opportunities.created_at)
Sign up for free to join this conversation on GitHub. Already have an account? Sign in to comment