title: Spark query to aggregate and update values in a table for a parent child relationship
body: <p>I have the following tables (Each table has other columns as well, but not needed here)</p>
  
  <p><strong>Item table</strong></p>
  
  <pre><code>      user_id, party_id, parent_user_id
          1,        X,      null
          2,        X,       1
          3,        X,       1
          4,        Z,       null
          5,        Z,       4
          6,        Y,       null
          7,        Y,       6
          8,        Y,       6
          1,        Y,       null
  </code></pre>
  
  <p>Where <em>null</em> <strong>parent_user_id</strong> indicates that its a parent user and then whichever columns have a parent_user_id, it denotes that its a child user.</p>
  
  <p><strong>Priority table</strong></p>
  
  <pre><code>      user_id, party_id, priority
            1,      X,     0.3 
            2,      X,     0.8
            3,      X,     0.5 
            4,      Z,     0.1
            5,      Z,     0.2
            6,      Y,     0.7
            7,      Y,     0.4
            8,      Y,     0.5
            1,      Y,     0.3
  </code></pre>
  
  <p>What I want to do is write a spark query to convert the priority table as below.
  Logic should be, for every parent user and its child user, calculate its max priority i.e max(parent user priority, child user1 priority, child user2 priority, ....) and then change the priorities to reflect the max value for that parent and all its child (as mentioned in the below updated table).</p>
  
  <p><strong>Priority table</strong></p>
  
  <pre><code>      user_id, party_id, priority
            1,      X,     0.8 
            2,      X,     0.8
            3,      X,     0.8 
            4,      Z,     0.2
            5,      Z,     0.2
            6,      Y,     0.7
            7,      Y,     0.7
            8,      Y,     0.7
            1,      Y,     0.3
  </code></pre>
  
  <p>How do you write a spark job to accomplish this? How do you even start by writing a basic sql query first to accomplish this.</p>