​数仓经典面试互相关注sql题

SELECT uid,
     to_uid,
     sum(relation) relation
FROM (
-- to_uid关注了uid
SELECT
  t1.uid,
  t1.to_uid,
  CASE WHEN t2.uid IS NULL THEN 0 ELSE 2 END AS relation
FROM t1
LEFT JOIN t2
ON t1.uid = t2.uid
AND t1.to_uid = t2.follow_uid

UNION ALL

-- uid关注了to_uid
SELECT
  t1.uid,
  t1.to_uid,
  CASE WHEN t2.uid IS NULL THEN 0 ELSE 1 END AS relation
FROM t1
LEFT JOIN t2
ON t1.uid = t2.follow_uid
AND t1.to_uid = t2.uid
) t
GROUP BY uid,to_uid;