CODEWITHSUNDEEP
sqldatabricks
Win
Win

Reputation: 99

Databricks SQL Query - without the use of looping

I am trying to create a sql query on databricks to retrieve and tag records using some conditions.

I have a Policy table that looks like this:

PolicyId ClientId AgentId
P1234 C1234 A1234
P1235 C1234 A1235
P1236 C1234 A1236
P1237 C1235 A1234
P1238 C1235 A1234
P1239 C1236 A1237
P1230 C1236 A1238
P1241 C1244 A1238
P1242 C1255 A1238

I also have a Agent main table:

AgentId BranchCode AgentName
P1234 10 John
P1235 10 Mike
P1236 10 Peter
P1237 10 Allan
P1238 11 Roger

I need to write a query in databricks sql that would determine Policies that belong to the same Customer but have different Agents that belong to the same BranchCode and tag them with a code ex: red. Furthermore, tag the policies that belong to the same Customer but have different Agents but doesnt share the same branch code as orange. Other policies that do not fall to these conditions are neutral or tagged white.

In the sample Policy data, PolicyIds - P1234,P1235,P1236 will be tagged red (since they are owned by the same Client but serviced by different agents under the same branch) while P1237 and P1238 will be white. Lastly, P1239 and P1230 will be tagged orange. The other policies that do not belong to the same customer are also tagged ok / white :

PolicyId ClientId AgentId tag
P1234 C1234 A1234 red
P1235 C1234 A1235 red
P1236 C1234 A1236 red
P1237 C1235 A1234 white
P1238 C1235 A1234 white
P1239 C1236 A1237 orange
P1230 C1236 A1238 orange
P1241 C1244 A1234 white
P1242 C1245 A1234 white

I hope I explained this well, I can give more information if needed.

Thank you for your help!

Upvotes: 0

Views: 687

Answers (1)

Kombajn zbożowy
Kombajn zbożowy

Reputation: 10693

Just count the number of distinct agents and branches per policy.

SELECT PolicyId,
       case when count(distinct AgentId) = 1 then 'White'
            when count(distinct BranchCode) = 1 then 'Red'
            else 'Orange' end as Code
  FROM Policy
  JOIN Agent USING (AgentId)
 GROUP BY PolicyId;

Upvotes: 1

Related Questions