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.
Quick Answer: 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.