Here is SQL test I took during a job interview. First is the description, or you can skip directly to the answer.
Description:
A social network under development needs a query that returns all profiles and the types of their relationships with each other.
The result should have the following columns: profile | %related_profile_1% .. %related_profile_N% - type of relation of specific profiles:
- The column name is the related profile username.
- Columns are sorted in ascending order by name.
The result should be sorted in ascending order by profile.
Schema:
profiles
| name | type | constraint | description |
|---|---|---|---|
| id | INT | PRIMARY KEY | Profile ID |
| username | VARCHAR(255) | Profile username |
relations
| name | type | constraint | description |
|---|---|---|---|
| profile_id | INT | FOREIGN KEY (profile_id => profiles.id) | Profile ID |
| related_profile_id | INT | FOREIGN KEY (related_profile_id => profiles.id) | Related profile ID |
| type | ENUM(‘family’,‘friends’,‘acquaintances’) | Relation type |
Sample Data Tables profiles
| id | username |
|---|---|
| 1 | jfarndale0 |
| 2 | bsyddall1 |
| 3 | cculkin2 |
relations
| profile_id | related_profile_id | type |
|---|---|---|
| 1 | 3 | acquaintances |
| 2 | 1 | family |
| 2 | 3 | acquaintances |
| 3 | 2 | friends |
Expected Output
| profile | bsyddall1 | cculkin2 | jfarndale0 |
|---|---|---|---|
| bsyddall1 | NULL | acquaintances | family |
| cculkin2 | friends | NULL | NULL |
| jfarndale0 | NULL | acquaintances | NULL |
Answer
I leaned heavily on ChatGPT-4 to get this answer. But in the end it was pretty straight forward. You have to dynamically make the column names for the resulting table as well as dynamically make the query that will return the results you are looking for.
DO
$$
DECLARE
column_list TEXT := '';
query TEXT;
rec RECORD;
BEGIN
-- Create a temporary table to hold the results
EXECUTE 'CREATE TEMPORARY TABLE temp_results (profile VARCHAR(255))';
-- Dynamically generate column names and alter the temporary table to add them
FOR rec IN
SELECT DISTINCT username
FROM profiles
LOOP
EXECUTE 'ALTER TABLE temp_results ADD COLUMN "' || rec.username || '" VARCHAR(255)';
END LOOP;
-- Dynamically generate the list of column expressions
SELECT STRING_AGG(
'MAX(CASE WHEN related_profile = ''' || username || ''' THEN relation_type END) AS "' || username || '"',
', '
) INTO column_list
FROM (SELECT DISTINCT username FROM profiles) AS usernames;
-- Construct the final query
query := 'INSERT INTO temp_results SELECT profile, ' || column_list || '
FROM (
SELECT
p1.username AS profile,
p2.username AS related_profile,
r.type AS relation_type
FROM
profiles p1
LEFT JOIN
relations r ON p1.id = r.profile_id
LEFT JOIN
profiles p2 ON r.related_profile_id = p2.id
) AS relation_matrix
GROUP BY profile
ORDER BY profile';
-- Execute the constructed query
EXECUTE query;
END
$$;
-- Select from the temporary table to view results
SELECT * FROM temp_results;