lesson 22 Joins & sets · intermediate

Self-joins & referral relationships

Join a table to itself to explore relationships.

the idea

A self-join joins a table to itself under two aliases. users.referred_by_user_id points at another row of users, so joining users u to users r ON r.user_id = u.referred_by_user_id puts each user beside their referrer. LEFT JOIN keeps users who were not referred.

Dataset: Product / SaaS Analytics

The tables the challenges use, with their first 6 rows. Under each column name: the kind of value it holds, and which table it links to.

worked example

Run these steps in order to see the idea before you write your own query. The step marked “the pattern” is what the challenges ask for. Running the example does not count toward progress.

1. 1

   See who referred whom. Run this. referred_by_user_id points at another row in the same users table.

   step-1.sql Run

   sql›

   SELECT user_id, account_id, referred_by_user_id
   FROM users
   WHERE referred_by_user_id IS NOT NULL
   LIMIT 10;

2. 2

   Self-join to show referrer names. Run this. Alias u for the user and r for the referrer row matched on user_id.the pattern

   example.sql Run

   sql›

   SELECT u.user_id, u.full_name, r.full_name AS referred_by
   FROM users u
   JOIN users r ON r.user_id = u.referred_by_user_id
   LIMIT 10;

common mistake

Without distinct aliases the column names are ambiguous, and an inner self-join drops everyone without a referrer.