共通の友人を非表示にしているユーザーを抽出するVRCX.sqlite3へのSQL

get-optout-mutual-friends-users.sql

WITH mutual_friend_ids AS (
    SELECT uid, COUNT(*)
    FROM (
        SELECT friend_id AS uid
        FROM usr0b83d9be985242dd98e2625062400acc_mutual_graph_links
        UNION ALL
        SELECT mutual_id AS uid
        FROM usr0b83d9be985242dd98e2625062400acc_mutual_graph_links
    )
    WHERE uid <> 'usr_00000000-0000-0000-0000-000000000000'
    GROUP BY uid
),
friend_list AS (
    SELECT user_id, display_name
    FROM usr0b83d9be985242dd98e2625062400acc_friend_log_current
)

SELECT *
FROM friend_list
LEFT JOIN mutual_friend_ids
ON friend_list.user_id = mutual_friend_ids.uid
WHERE mutual_friend_ids.uid IS NULL