Set Operations and Deduplication AI Prompt

Write SQL for set operations (UNION, INTERSECT, EXCEPT) and deduplication tasks.

Problem: {{problem}}
Tables: {{tables}}
Database: {{database}}

1. Set operations:
   UNION: all rows from both queries, removing duplicates
   UNION ALL: all rows including duplicates (faster than UNION when duplicates are acceptable)
   INTERSECT: rows present in BOTH queries
   EXCEPT / MINUS: rows in the first query but NOT in the second

   Find customers who ordered last year but not this year:
   SELECT customer_id FROM orders WHERE EXTRACT(YEAR FROM order_date) = 2023
   EXCEPT
   SELECT customer_id FROM orders WHERE EXTRACT(YEAR FROM order_date) = 2024;

2. Deduplication with ROW_NUMBER:
   -- Keep the most recent version of each customer record
   WITH ranked AS (
     SELECT *,
            ROW_NUMBER() OVER (PARTITION BY customer_id ORDER BY updated_at DESC) AS rn
     FROM customers
   )
   SELECT * FROM ranked WHERE rn = 1;

3. DISTINCT ON (PostgreSQL):
   -- Efficient alternative to ROW_NUMBER deduplication
   SELECT DISTINCT ON (customer_id)
     customer_id, name, email, updated_at
   FROM customers
   ORDER BY customer_id, updated_at DESC;

4. Fuzzy deduplication:
   -- Find potential duplicate customers by similarity
   SELECT a.customer_id, b.customer_id,
          SIMILARITY(a.email, b.email) AS email_sim
   FROM customers a
   JOIN customers b ON a.customer_id < b.customer_id
   WHERE SIMILARITY(a.name, b.name) > 0.7
     AND a.zip_code = b.zip_code;
   -- Requires: CREATE EXTENSION pg_trgm;

5. Merge deduplication (upsert):
   INSERT INTO customers (customer_id, name, email, updated_at)
   VALUES (...)
   ON CONFLICT (customer_id)
   DO UPDATE SET
     name = EXCLUDED.name,
     email = EXCLUDED.email,
     updated_at = EXCLUDED.updated_at
   WHERE EXCLUDED.updated_at > customers.updated_at;

Return: SQL for each set operation or deduplication task, DISTINCT ON pattern for PostgreSQL, and upsert pattern.