Skip to main content

10

sql

-- Recursive CTE to trace referral chain starting from Sofia
WITH RECURSIVE referral_chain AS (
  SELECT user_id, name, referred_by, 1 AS depth
  FROM referrals
  WHERE referred_by IS NULL

  UNION ALL

  SELECT r.user_id, r.name, r.referred_by, rc.depth + 1
  FROM referrals r
  JOIN referral_chain rc ON r.referred_by = rc.user_id
)
SELECT *
FROM referral_chain
ORDER BY depth;

Want to learn more?

Join CodeFriends Plus membership or enroll in a course to start your journey.

sql