Back to questions
NYC Area Code [Verizon SQL Interview Question]
The first 3 digits of American phone numbers, after the international code (of +1) are called the area code.
How many phone calls have either a caller or receiver with a phone number with a Manhattan NYC area code (ie. +1-212-XXX-XXXX).
Table:
| Column Name | Type |
|---|---|
| caller_id | integer |
| receiver_id | integer |
| call_time | timestamp |
Example Input:
| caller_id | receiver_id | call_time |
|---|---|---|
| 1 | 2 | 2022-07-04 10:13:49 |
| 1 | 5 | 2022-08-21 23:54:56 |
| 5 | 1 | 2022-05-13 17:24:06 |
| 5 | 6 | 2022-03-18 12:11:49 |
Table:
| Column Name | Type |
|---|---|
| caller_id | integer |
| country_id | integer |
| network | integer |
| phone_number | string |
Example Input:
| caller_id | country_id | network | phone_number |
|---|---|---|---|
| 1 | US | Verizon | +1-212-897-1964 |
| 2 | US | Verizon | +1-703-346-9529 |
| 3 | US | Verizon | +1-650-828-4774 |
| 4 | US | Verizon | +1-415-224-6663 |
| 5 | IN | Vodafone | +91 7503-907302 |
| 6 | IN | Vodafone | +91 2287-664895 |
Example Output:
| nyc_count |
|---|
| 3 |
Explanation
Caller ID 1 is the only NYC phone number (see the 212 after the +1). Caller 1 is involved in 3 calls.
The dataset you are querying against may have different input & output - this is just an example!
Solution
Step 1: Join the tables to obtain caller and receiver phone numbers
To determine whether either caller or receiver has the NYC area code, we need the phone numbers for both callers and receivers. This can be achieved by left-joining to twice, one based on the caller, and the second based on the receiver.
Step 2: Use statement and operator to determine if the phone number matches the pattern
Next, we match the phone numbers against the pattern. To do this, SQL has a very good operator that can be used with wildcard characters. Read more about the ins and outs of pattern matching here.
Using the SQL syntax, we can write it in the following way: . The expression checks if a given string starts with . The wildcard symbol denotes that it can be followed by any combination of characters.
Clear? Now let's put this into practice! I have combined it with a statement that checks both the receivers' and the callers' phone numbers and returns 1 if the pattern matches the NYC area code.
Step 3: Add the matching rows
After having identified the callers/receivers with the area code, we have one final step - simply sum the results!
Solution #2: Using LEFT() function
Use the function to find phone numbers with the exact pattern and then, check for the existence of callers and receivers in the clause using an operator.
PostgreSQL 14