# 查找所有使用“Facebook 登录”的用户

**URL:** <https://meta.discourse.org/t/find-all-users-who-used-login-with-facebook/105313>\
**Category:** Data & reporting\
**Tags:** oauth2, sql-query\
**Created:** [2018 年12 月 30 日 13:42 UTC](https://meta.discourse.org/t/find-all-users-who-used-login-with-facebook/105313 "2018-12-30T13:42:08Z")\
**Posts on this page:** 1\
**Showing post:** 9

<div class="post-metadata">

作者： ![michebs](https://sea3.discourse-cdn.com/meta/user_avatar/meta.discourse.org/michebs/32/190102_2.png) [@michebs](https://meta.discourse.org/u/michebs)\
发布日期： [2020 年6 月 23 日 08:45 UTC](https://meta.discourse.org/t/find-all-users-who-used-login-with-facebook/105313/9 "2020-06-23T08:45:38Z")

</div>

## 这些 [Data Explorer](https://meta.discourse.org/t/32566?silent=true) 查询可以在这些情况下提供帮助。

* * *

#### 1. 按 Facebook 登录方式列出的用户列表

```
WITH target_user_ids AS (
    SELECT id
    FROM users
    WHERE staged = false
        AND active = true
        AND last_seen_at IS NOT NULL)

SELECT 
    provider_name,
    user_id,
    info->>'name' AS name_user,
    info->>'email' AS email_user,
    info->>'image' AS image_user
FROM user_associated_accounts ua
WHERE user_id IN (SELECT id FROM target_user_ids)
    AND provider_name = 'facebook'

```

| provider\_name | user\_id | name | email | image |
| --- | --- | --- | --- | --- |
| facebook | 1 | User1 | [user1@gmail.com](mailto:user1@gmail.com) | [https://graph.facebook.com/.../photo1.jpg](https://graph.facebook.com/.../photo1.jpg) |
| facebook | 2 | User2 | [user2@yahoo.com](mailto:user2@yahoo.com) | [https://graph.facebook.com/.../photo2.jpg](https://graph.facebook.com/.../photo2.jpg) |

* * *

* * *

#### 2. 按外部登录方式列出的用户列表

- discord
- facebook
- github
- google\_oauth2
- twitter

* * *

```
WITH target_user_ids AS (
    SELECT id
    FROM users
    WHERE staged = false
        AND active = true
        AND last_seen_at IS NOT NULL)

SELECT 
    provider_name,
    user_id,
    info->>'name' AS name,
    info->>'email' AS email,
    info->>'image' AS image
FROM user_associated_accounts ua
WHERE user_id IN (SELECT id FROM target_user_ids)

```

| provider\_name | user\_id | name | email | image |
| --- | --- | --- | --- | --- |
| google\_oauth2 | 1 | User1 | [user1@gmail.com](mailto:user1@gmail.com) | [https://lh3.googleusercontent.com/.../p1.jpg](https://lh3.googleusercontent.com/.../p1.jpg) |
| facebook | 2 | User2 | [user2@yahoo.com](mailto:user2@yahoo.com) | [https://graph.facebook.com/.../p2.jpg](https://graph.facebook.com/.../p2.jpg) |
| twitter | 3 | User3 | [user3@gmail.com](mailto:user3@gmail.com) | [http://pbs.twimg.com/profile\_img/.../p1.jpg](http://pbs.twimg.com/profile_img/.../p1.jpg) |

* * *

* * *

#### 3. 按外部登录方式统计的用户数量

```
WITH target_user_ids AS (
    SELECT id
    FROM users
    WHERE staged = false
        AND active = true
        AND last_seen_at IS NOT NULL)

SELECT 
    provider_name,
    COUNT(user_id) AS qtd
FROM user_associated_accounts ua
WHERE user_id IN (SELECT id FROM target_user_ids)
GROUP BY provider_name
UNION
SELECT 
    'github', 
    COUNT(user_id)
FROM github_user_infos
WHERE user_id IN (SELECT id FROM target_user_ids)
ORDER BY provider_name

```

| provider\_name | qtd |
| --- | --- |
| discord | 10 |
| facebook | 100 |
| github | 400 |
| google\_oauth2 | 500 |
| twitter | 200 |

---

_[View the full topic](https://meta.discourse.org/t/find-all-users-who-used-login-with-facebook/105313)._