Calculate and Find Average Contacts and Sync Percentage
Company: PayPal
Role: Data Scientist
Category: Data Manipulation (SQL/Python)
Difficulty: medium
Interview Round: Technical Screen
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.
Overview: This question evaluates proficiency in data aggregation, deduplication, and relational joins for computing user-level metrics such as average synced contacts and the percentage of users with at least one synced contact.
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).
Tables
dw_peers(user_id BIGINT, synced_contact VARCHAR(15), date_synced DATE)
dw_users(user_id BIGINT, phone_number VARCHAR(15))
Hints
- Aggregate dw_peers by user_id and count contacts, e.g., COUNT(DISTINCT synced_contact).
- Average those per-user counts to get the average contacts per syncing user.