How would you identify duplicate records based on a composite key in SQL?

To identify duplicates by composite key in SQL, use GROUP BY with HAVING COUNT() > 1, or window functions. Example: SELECT order_id, product_id, COUNT() cnt FROM orders GROUP BY order_id, product_id HAVING COUNT() > 1. Window approach: SELECT FROM (SELECT *, ROW_NUMBER() OVER (PARTITION BY order_id, product_id ORDER BY created_at) rn FROM orders) t WHERE rn > 1. The GROUP BY method returns only the composite key and count; the window method returns full rows of duplicates. For removal, use DELETE with a CTE: WITH dups AS (SELECT order_id, product_id, ROW_NUMBER() OVER (PARTITION BY order_id, product_id ORDER BY created_at DESC) rn FROM orders) DELETE FROM orders WHERE (order_id, product_id) IN (SELECT order_id, product_id FROM dups WHERE rn > 1); Why it matters: Design choices compound at scale—wrong approach can cause 100× overhead. Scalability trade-offs: Profile before optimizing; validate on sample then full. Cost implications: Suboptimal choices multiply at billion-row scale.