SQL from zero · Chapter 36 of 37

People like you

By the end of this chapter you can find the members most like another member by measuring the distance between their embeddings, and index that search. Build it in your browser: a real editor and terminal, AI that explains, and tests that tell you when it works.

  • Part: Fast
  • 4 tasks
  • SQL
  • In the all labs plan

The first 2 chapters of SQL from zero are free. This one comes with the all labs plan.

Why you need this

Meera wants a new box on the home page: "People like you", the three members whose interests are closest to yours. Rohan already has the numbers: every member has an embedding, three numbers for how much they are into tech, arts and sports.

SELECT username, interests, embedding FROM users
WHERE username IN ('rohan', 'arjun', 'priya') ORDER BY username;
 username │        interests         │   embedding
──────────┼──────────────────────────┼────────────────
 arjun    │ {cricket,gaming}         │ [0.4,0.2,0.9]
 priya    │ {music,travel,food}      │ [0.1,0.95,0.3]
 rohan    │ {analytics,cricket,chai} │ [0.8,0.15,0.7]
(3 rows)

With what you know, "close" would mean comparing the interests arrays for shared words, and Rohan and Priya share none, so you learn nothing about them. Or you would pull the three numbers apart and write the distance formula by hand in every query, and no index could help it.

What you build: 4 tasks

Each task has tests. You write the code in the lab's editor and press Check.

  1. Task 1

    nearest-to-anya.sql: the 3 members nearest to anya

    Find the 3 members whose embedding is closest to anya's by straight-line distance (the <-> operator). Leave out anya herself, members who left (a date in deleted_at) and members with no embedding. Leave anya out by who she is, not by a distance of 0: another member can have exactly her embedding. distance is rounded to 3 decimals.

    Return two columns: username and distance. Closest first; when two members are at the same distance, sort them by username A to Z.

  2. Task 2

    meera-matches.sql: meera's 3 best matches by cosine

    Find meera's 3 best matches by cosine similarity, which is 1 minus the cosine distance (the <=> operator), rounded to 3 decimals. Leave out meera herself, members who left (a date in deleted_at) and members with no embedding.

    Return two columns: username and similarity. Most similar first; when two members are equally similar, sort them by username A to Z.

  3. Task 3 · interview question

    Interview: best-match.sql, every member's single best match

    For every member who has not left and has an embedding, find their single best match: the other member, also not left and with an embedding, with the smallest cosine distance (the <=> operator) to them. When two candidates are at the same distance, the one whose username comes first A to Z wins.

    Return two columns: username and best_match (the match's username). Any order.

  4. Task 4

    vector-index.sql: an hnsw index for cosine searches

    Create an index on the embedding column of users for nearest-neighbour searches by cosine distance (sorting by the <=> operator with a limit): pgvector's hnsw index with the vector_cosine_ops operator class.

    Afterwards, a cosine nearest-neighbour search on users uses your index.

What you will learn

  • Vector distance with pgvector: <-> (straight-line) and <=> (cosine).
  • Nearest-neighbour search: ORDER BY a distance, then LIMIT.
  • LATERAL to find every member's best match in one query.
  • An hnsw index that serves one distance operator.

More in Fast

Build "People like you" yourself

MingleSphereQL, a social app, needs answers from its data every day. A real editor and terminal in your browser, AI that explains, and a Check that says when it works.

The first 2 chapters of SQL from zero are free. This one comes with the all labs plan.