Number of Trusted Contacts
ASKED IN INTERVIEWWhats App
Problem Statement
WhatsApp uses a verification flag to identify trusted contacts. You need to find the total number of trusted contacts for each user.
A contact is considered trusted if the is_verified flag in the contacts table is set to 1.
Rules:
- Only include users who have at least one trusted contact.
- Output columns: user_id, user_name, trusted_contact_count.
- Results must be sorted by user_id in ascending order.
Tests your understanding of
Grouping, Aggregation and Filtering
Input Tables
users
| user_id(INTEGER) | user_name(VARCHAR) |
|---|---|
| 1 | Alice |
| 2 | Bob |
| 3 | Charlie |
contacts
| user_id(INTEGER) | contact_id(INTEGER) | is_verified(INTEGER) |
|---|---|---|
| 1 | 10 | 1 |
| 1 | 11 | 0 |
| 1 | 12 | 1 |
| 2 | 13 | 1 |
| 3 | 14 | 0 |
user_settings
| user_id(INTEGER) | privacy_level(VARCHAR) |
|---|---|
| 1 | High |
| 2 | Medium |
Expected Output
| user_id(INTEGER) | user_name(VARCHAR) | trusted_contact_count(INTEGER) |
|---|---|---|
| 1 | Alice | 2 |
| 2 | Bob | 1 |
Tags
EasyASKED IN INTERVIEWGroupingAggregationFiltering
10-15 min
74%