AI.SIMILARITY(
  embedding_1,
  embedding_2
)

-- Get query embedding
DECLARE query_embedding ARRAY<float64>;

SET query_embedding = (
  SELECT AI.EMBED(
    model => 'text-embedding-004',
    content => 'noise cancelling wireless headphones',
    task_type => 'RETRIEVAL_QUERY',
    connection_id => 'us.my_vertex_connection'
  )
);

-- Find top 10 similar products
SELECT 
  product_id,
  product_name,
  AI.SIMILARITY(
    query_embedding,
    product_embedding
  ) AS similarity_score
FROM products_with_embeddings
ORDER BY similarity_score DESC
LIMIT 10

SELECT 
  doc1.document_id AS doc1_id,
  doc2.document_id AS doc2_id,
  AI.SIMILARITY(
    doc1.embedding,
    doc2.embedding
  ) AS similarity
FROM document_embeddings doc1
CROSS JOIN document_embeddings doc2
WHERE doc1.document_id < doc2.document_id  -- Avoid duplicate pairs
  AND AI.SIMILARITY(doc1.embedding, doc2.embedding) > 0.8  -- Only high similarity
ORDER BY similarity DESC

WITH pairs AS (
  SELECT 
    a.article_id AS id1,
    b.article_id AS id2,
    AI.SIMILARITY(a.embedding, b.embedding) AS similarity
  FROM article_embeddings a
  CROSS JOIN article_embeddings b
  WHERE a.article_id < b.article_id
)
SELECT 
  id1,
  id2,
  similarity
FROM pairs
WHERE similarity > 0.95  -- Very high similarity threshold for duplicates
ORDER BY similarity DESC

-- Given a user's favorite product, find similar items
DECLARE favorite_product_embedding ARRAY<float64>;

SET favorite_product_embedding = (
  SELECT product_embedding
  FROM products_with_embeddings
  WHERE product_id = 'PROD-12345'
);

SELECT 
  product_id,
  product_name,
  price,
  category,
  AI.SIMILARITY(
    favorite_product_embedding,
    product_embedding
  ) AS similarity
FROM products_with_embeddings
WHERE product_id != 'PROD-12345'  -- Exclude the original product
  AND category IN ('electronics', 'accessories')  -- Filter by category
ORDER BY similarity DESC
LIMIT 5

-- Compare content against high-quality reference examples
DECLARE reference_embedding ARRAY<float64>;

SET reference_embedding = (
  SELECT AVG(embedding)  -- Average of multiple good examples
  FROM article_embeddings
  WHERE quality_rating >= 4.5
);

SELECT 
  article_id,
  title,
  AI.SIMILARITY(
    reference_embedding,
    embedding
  ) AS quality_similarity
FROM article_embeddings
WHERE published_date >= CURRENT_DATE() - 7
ORDER BY quality_similarity DESC

-- Search with multiple related queries and aggregate results
WITH query_embeddings AS (
  SELECT 
    AI.EMBED(
      model => 'text-embedding-004',
      content => query_text,
      task_type => 'RETRIEVAL_QUERY',
      connection_id => 'us.my_vertex_connection'
    ) AS embedding
  FROM UNNEST([
    'wireless headphones',
    'bluetooth earbuds',
    'noise cancelling audio'
  ]) AS query_text
),
scores AS (
  SELECT 
    p.product_id,
    p.product_name,
    MAX(AI.SIMILARITY(q.embedding, p.product_embedding)) AS max_similarity
  FROM products_with_embeddings p
  CROSS JOIN query_embeddings q
  GROUP BY p.product_id, p.product_name
)
SELECT *
FROM scores
ORDER BY max_similarity DESC
LIMIT 20