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;