Conditional selection of data in palantir contour

Question

I have two datasets with Client, 1st Nationality, 2nd Nationality, 3rd Nationality and with Country Code, Continent.

I would need an expresion using an expresion board in Palantir Contour that would select for me a client from the furthest contienet (in case the client has more than one nationality, it selects the farthest one assuming I am in America) when I make ulitimately a pilot table having only Continents and count of clients in it. If not directly palantir expression, also an SQL statement for this would be helpful. THank you

Sample example

Answer 1

So we have to work on the following tables-

To find the furthest continent for each client, our condition is America is the closest continent and Asia is the furthest continent after Europe. Try the following SQL code-

WITH Client_Continents AS (
    SELECT 
        cn.Client, 
        c.Continent,
        CASE 
            WHEN c.Continent = 'America' THEN 1  
            WHEN c.Continent = 'Europe' THEN 2  
            WHEN c.Continent = 'Asia' THEN 3    
            ELSE 0  
        END AS ContinentScore
    FROM Client_Nationality cn
    LEFT JOIN Continent c ON cn.First_Nationality = c.Country_Code
    
    UNION ALL
    
    SELECT 
        cn.Client, 
        c.Continent, 
        CASE 
            WHEN c.Continent = 'America' THEN 1
            WHEN c.Continent = 'Europe' THEN 2
            WHEN c.Continent = 'Asia' THEN 3
            ELSE 0
        END AS ContinentScore
    FROM Client_Nationality cn
    LEFT JOIN Continent c ON cn.Second_Nationality = c.Country_Code
    
    UNION ALL
    
    SELECT 
        cn.Client, 
        c.Continent, 
        CASE 
            WHEN c.Continent = 'America' THEN 1
            WHEN c.Continent = 'Europe' THEN 2
            WHEN c.Continent = 'Asia' THEN 3
            ELSE 0
        END AS ContinentScore
    FROM Client_Nationality cn
    LEFT JOIN Continent c ON cn.third_Nationality = c.Country_Code
),

Max_Continent AS (
    SELECT 
        Client, 
        MAX(ContinentScore) AS MaxContinentScore  
    FROM Client_Continents
    GROUP BY Client
)

SELECT 
    cc.Continent, 
    COUNT(DISTINCT cc.Client) AS ClientCount  
FROM Client_Continents cc
JOIN Max_Continent mc ON cc.Client = mc.Client
WHERE cc.ContinentScore = mc.MaxContinentScore  
GROUP BY cc.Continent
ORDER BY ClientCount; 

Provides the result set -