Here is SQL test I took during a job interview. First is the description, or you can skip directly to the answer.
Description:
As part of developing the call history functionality of a dialer application, create a query that returns a list of all contacts, the total duration of their calls, and the number of calls during the work week and weekends.
The result should have the following column: full_name | phone | type | workweek | weekend.
- full_name - full contact name:
- Record format is ## ##, where the placeholders are ## the order they appear:
- Last name of the contact
- First name of the contact
- Record format is ## ##, where the placeholders are ## the order they appear:
- phone - phone number
- type - call type
- workweek - information about calls during the working week (from Monday to Friday):
- Record format is ##:##:## (##), where the placeholders are ## in the order they appear:
- Hours part of the total duration of calls, numeric (00..n)
- Minutes part of the total duration of calls, numeric (00..59)
- Seconds part of the total duration of calls, numeric (00..59)
- Total number of calls
- Record format is ##:##:## (##), where the placeholders are ## in the order they appear:
- weekend - information about calls during the weekend (from Saturday to Sunday):
- Record format is ##:##:## (##), where the placeholders are ## in the order they appear:
- Hours part of the total duration of calls, numeric (00..n)
- Minutes part of the total duration of calls, numeric (00..59)
- Seconds part of the total duration of calls, numeric (00..59)
- Total number of calls
- Record format is ##:##:## (##), where the placeholders are ## in the order they appear:
The result should be sorted in ascending order by full_name, then in ascending order by type.
Schema:
contacts
| name | type | constraint | description |
|---|---|---|---|
| id | INT | PRIMARY KEY | Contact ID |
| first_name | VARCHAR(255) | Contact first name | |
| last_name | VARCHAR(255) | Contact last name | |
| phone | VARCHAR(255) | Contact phone number |
calls
| name | type | constraint | description |
|---|---|---|---|
| contact_id | INT | FOREIGN KEY (contact_id => contacts.id) | Contact ID |
| dt | DATETIME | Call date and time | |
| type | ENUM(‘incoming’,‘outgoing’) | Call type | |
| duration | SMALLINT | Call duration |
Sample Data Tables
contacts
| id | first_name | last_name | phone |
|---|---|---|---|
| 1 | Spencer | Forrestor | +54 (929) 865-3063 |
| 2 | Ellis | Mattiato | +86 (490) 216-5428 |
| 3 | Robin | Rosendale | +86 (412) 441-4721 |
calls
| contact_id | dt | type | duration |
|---|---|---|---|
| 1 | 2022-09-14 05:12:20 | incoming | 1531 |
| 1 | 2022-09-15 09:29:55 | incoming | 1962 |
| 1 | 2022-09-12 00:07:23 | outgoing | 1213 |
| 1 | 2022-09-12 07:05:48 | outgoing | 556 |
| 1 | 2022-09-12 11:16:21 | outgoing | 1382 |
| 1 | 2022-09-12 11:30:28 | outgoing | 2622 |
| 1 | 2022-09-14 16:02:33 | outgoing | 3390 |
| 1 | 2022-09-15 07:07:27 | outgoing | 2267 |
| 2 | 2022-09-15 00:39:01 | incoming | 127 |
| 2 | 2022-09-15 00:53:08 | incoming | 375 |
| 2 | 2022-09-13 10:47:30 | outgoing | 2907 |
| 2 | 2022-09-16 09:58:15 | outgoing | 1065 |
| 2 | 2022-09-17 10:50:01 | outgoing | 2801 |
| 3 | 2022-09-13 14:37:12 | incoming | 904 |
| 3 | 2022-09-13 22:50:33 | incoming | 2925 |
| 3 | 2022-09-12 12:25:40 | outgoing | 352 |
| 3 | 2022-09-13 23:48:02 | outgoing | 744 |
| 3 | 2022-09-14 16:59:23 | outgoing | 1067 |
| 3 | 2022-09-15 09:24:01 | outgoing | 2344 |
| 3 | 2022-09-15 12:13:39 | outgoing | 3567 |
Expected Output:
| full_name | phone | type | workweek | weekend |
|---|---|---|---|---|
| Forrestor Spencer | +54 (929) 865-3063 | incoming | 00:58:13 (2) | NULL |
| Forrestor Spencer | +54 (929) 865-3063 | outgoing | 03:10:30 (6) | NULL |
| Mattiato Ellis | +86 (490) 216-5428 | incoming | 00:08:22 (2) | NULL |
| Mattiato Ellis | +86 (490) 216-5428 | outgoing | 01:06:12 (2) | 00:46:41 (1) |
| Rosendale Robin | +86 (412) 441-4721 | incoming | 01:03:49 (2) | NULL |
| Rosendale Robin | +86 (412) 441-4721 | outgoing | 02:14:34 (5) | NULL |
Answer
There are three challenges to writing this query
- Concatenating the full name as well as number of calls to the duration. Number of calls is found via a count, grouping on the contact information.
- Summing then changing the total seconds of each contacts calls to HH:MM:SS (you can see how to do that in my post here)
- Determine if the call is a weekday or weekend and putting that sum in the correct column. Use the ISODOW PostgreSQL function.
select b.last_name || ' ' || b.first_name full_name
, b.phone
, a.type
, (sum(case when extract(isodow from dt::date) < 6 then duration else null end) * interval '1 sec')::varchar(10) || ' (' || count(case when extract(isodow from dt::date) < 6 then duration else null end) || ')' as workweek
, (sum(case when extract(isodow from dt::date) >= 6 then duration else null end) * interval '1 sec')::varchar(10) || ' (' || count(case when extract(isodow from dt::date) >= 6 then duration else null end) || ')' as weekend
from calls a
join contacts b on a.contact_id = b.id
group by b.last_name || ' ' || b.first_name, b.phone, a.type
order by b.last_name || ' ' || b.first_name, a.type;