Calculate and Find Average Contacts and Sync Percentage

dw_peers

+--------------+---------------+-------------+
| user_id      | synced_contact| date_synced |
+--------------+---------------+-------------+
| 2589654316571| 4359086122    | 2023-02-01  |
| 1111111111111| 9999999999    | 2023-02-03  |
| 1111111111111| 8888888888    | 2023-02-03  |
| 2222222222222| 5555555555    | 2023-02-10  |
+--------------+---------------+-------------+

dw_users

+--------------+--------------+
| user_id      | phone_number |
+--------------+--------------+
| 2589654316571| 2349087633   |
| 1111111111111| 7777777777   |
| 2222222222222| 6666666666   |
| 3333333333333| 4444444444   |
+--------------+--------------+

Scenario

PayPal allows users to opt-in to sync their phone contacts. Contacts are stored in dw_peers; basic user info is stored in dw_users.

Question

Write SQL to calculate the average number of contacts synced per user. Write SQL to find the percentage of all PayPal users who have synced at least one contact.

Hints

Use aggregation on dw_peers, left-join to dw_users for denominator; COUNT(DISTINCT user_id) may help.

Average Contacts per Syncing User and Percentage of Users Syncing Contacts

PayPal allows users to opt in to sync their phone contacts. Contacts are stored in dw_peers; basic user info is stored in dw_users.

Write a single SQL query that returns one row with two metrics:

1. The average number of contacts synced per user, considering only users who have synced at least one contact.
2. The percentage of all PayPal users (from dw_users) who have synced at least one contact (i.e., who appear in dw_peers).

Table dw_peers

column	type	constraints
user_id	BIGINT	NOT NULL, FOREIGN KEY REFERENCES dw_users(user_id)
synced_contact	VARCHAR(15)	NOT NULL
date_synced	DATE	NOT NULL

Sample data:

date_synced	synced_contact	user_id
2023-02-01	4359086122	2589654316571
2023-02-03	9999999999	1111111111111
2023-02-03	8888888888	1111111111111
2023-02-10	5555555555	2222222222222

Table dw_users

column	type	constraints
user_id	BIGINT	PRIMARY KEY, NOT NULL
phone_number	VARCHAR(15)	NOT NULL

Sample data:

phone_number	user_id
2349087633	2589654316571
7777777777	1111111111111
6666666666	2222222222222
4444444444	3333333333333

Expected output

One row with two metrics: the average number of distinct contacts per user among users who have synced at least one contact, and the percentage of all users who have synced at least one contact. Based on the sample data, three users have synced contacts with per-user counts 1, 2, and 1, giving an average of 4/3 = 1.3333. Out of four total users, three have synced at least one contact, so the percentage is 75.00.

avg_contacts_per_syncing_user	pct_users_synced_at_least_one
1.3333	75

Hints

1. Aggregate dw_peers by user_id and count contacts, e.g., COUNT(DISTINCT synced_contact).
2. Average those per-user counts to get the average contacts per syncing user.
3. Use the number of distinct users in dw_peers over the total users in dw_users to compute the percentage.
4. A LEFT JOIN from dw_users to dw_peers can also be used to ensure all users are considered in the denominator.