SQL One-Liner: Detect Duplicate Records Instantly

❓ Problem You have a table where duplicate records might exist. Example table: users user_id email 1 a@email.com 2 b@email.com 3 a@email.com You want to find duplicate emails. ❌ Long Way Many beginners write multiple queries. SELECT email, COUNT(*) FROM users GROUP BY email HAVING COUNT(*) v 1; Then they run another query to fetch full rows: SELECT * FROM users WHERE email IN ( SELECT email FROM users GROUP BY email HAVING COUNT(*) v 1 ); Problem Two queries Extra scanning Not efficient 🚀 SQL One-Liner Shortcut SELECT * FROM ( SELECT *, COUNT(*) OVER (PARTITION BY email) AS dup_count FROM users ) t WHERE dup_count v 1; 🧠 Step-by-Step Explanation 1️⃣ Window Function COUNT(*) OVER (PARTITION BY email) Counts how many rows share the same email. 2️⃣ Partition Logic PARTITION BY email groups rows without collapsing them. Unlike GROUP BY, this keeps all original rows. 3️⃣ Duplicate Filter WHERE dup_count v 1 Returns only records that appear multiple times. 📌 Why This One-Liner Is Powerful Works in: PostgreSQL BigQuery Snowflake SQL Server DuckDB Benefits: ✔ single query ✔ faster debugging ✔ shows all duplicate rows ✔ production friendly In this SQL short from CodeVisium, you’ll learn how to detect duplicate records instantly using a powerful SQL window function one-liner. Instead of writing multiple queries with GROUP BY and subqueries, this technique uses: COUNT() OVER (PARTITION BY column) to identify duplicates while keeping the original rows intact. This approach is widely used in: • data cleaning pipelines • analytics datasets • data warehouse quality checks • interview SQL questions Example use cases: • detect duplicate emails • identify duplicate orders • find repeated user records • check data integrity issues SQL tools where this works perfectly: • PostgreSQL • BigQuery • Snowflake • SQL Server • DuckDB If you want to master SQL shortcuts, one-liners, and analytics tricks, follow CodeV