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:
      1. Last name of the contact
      2. First name of the contact
  • 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:
      1. Hours part of the total duration of calls, numeric (00..n)
      2. Minutes part of the total duration of calls, numeric (00..59)
      3. Seconds part of the total duration of calls, numeric (00..59)
      4. Total number of calls
  • weekend - information about calls during the weekend (from Saturday to Sunday):
    • Record format is ##:##:## (##), where the placeholders are ## in the order they appear:
      1. Hours part of the total duration of calls, numeric (00..n)
      2. Minutes part of the total duration of calls, numeric (00..59)
      3. Seconds part of the total duration of calls, numeric (00..59)
      4. Total number of calls

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

  1. 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.
  2. 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)
  3. 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;