Advanced Filtering Patterns AI Prompt

Write advanced SQL filtering conditions for these complex requirements.

Requirements: {{requirements}}
Tables: {{tables}}
Database: {{database}}

1. ANY / ALL operators:
   -- Customers who ordered every product in a list:
   SELECT customer_id FROM orders
   GROUP BY customer_id
   HAVING array_agg(product_id ORDER BY product_id)
          @> ARRAY[1,2,3]::int[];  -- contains all required products

   -- Value greater than all values in a subquery:
   WHERE amount > ALL(SELECT amount FROM orders WHERE year = 2023)

2. Relational division (find entities meeting ALL criteria):
   -- Find customers who purchased ALL products in a category
   SELECT customer_id
   FROM orders o
   JOIN products p ON o.product_id = p.id
   WHERE p.category = 'Electronics'
   GROUP BY customer_id
   HAVING COUNT(DISTINCT o.product_id) = (
     SELECT COUNT(*) FROM products WHERE category = 'Electronics'
   );

3. Latest record per group (common requirement):
   -- Method 1: ROW_NUMBER
   SELECT * FROM (
     SELECT *, ROW_NUMBER() OVER (PARTITION BY customer_id ORDER BY updated_at DESC) AS rn
     FROM customer_states
   ) WHERE rn = 1;

   -- Method 2: DISTINCT ON (PostgreSQL)
   SELECT DISTINCT ON (customer_id) *
   FROM customer_states
   ORDER BY customer_id, updated_at DESC;

4. NOT EXISTS vs NOT IN (important difference with NULLs):
   -- NOT IN fails silently if the subquery returns any NULLs
   WHERE id NOT IN (SELECT customer_id FROM blacklist)  -- wrong if blacklist has NULLs
   -- NOT EXISTS handles NULLs correctly:
   WHERE NOT EXISTS (SELECT 1 FROM blacklist b WHERE b.customer_id = c.id)

5. Overlapping intervals:
   -- Find all events that overlap with a given time window
   WHERE start_time < :window_end AND end_time > :window_start
   -- Allen's interval overlap condition: two intervals overlap if neither ends before the other starts

Return: SQL for each complex filtering requirement with explanations of edge cases and NULL handling.