Back to questions
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).
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!
Here's our algorithm for solving this question:
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.
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.
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.