title: How can I combine these 3 queries to achieve my desired, formatted, result
body: <p>I have 3 queries that run at the same time in order to get 3 different lists of profiles. It's cacheable data for the same page, so I'd like to do it all in a single query. This is my single biggest hurdle when using Postgres. Can someone help me begin understanding the process of combining queries and formatting?</p>
  
  <p>Here are the queries:</p>
  
  <ul>
  <li><p>Retrieves the 10 users with the sum highest loan balance</p>
  
  <pre><code>select user_loans.user_id,
    sum(user_loans.balance) as total_balance,
    sum(user_loans.origination_principal_amount) as origination_principal_amount,
    mode() within group (order by user_profiles.first_name) as first_name,
    mode() within group (order by user_profiles.last_name) as last_name,
    mode() within group (order by user_profiles.city) as city,
    mode() within group (order by user_profiles.state) as state
  from user_loans
  join user_profiles on user_loans.user_id = user_profiles.user_id
  group by user_loans.user_id
  order by total_balance desc
  limit 10;
  </code></pre></li>
  <li><p>Retrieves the 10 newest users with a loan added</p>
  
  <pre><code>select user_loans.user_id,
    user_profiles.created_at,
    sum(user_loans.balance) as total_balance,
    sum(user_loans.origination_principal_amount) as origination_principal_amount,
    mode() within group (order by user_profiles.first_name) as first_name,
    mode() within group (order by user_profiles.last_name) as last_name,
    mode() within group (order by user_profiles.city) as city,
    mode() within group (order by user_profiles.state) as state
  from user_loans 
  join user_profiles on user_loans.user_id = user_profiles.user_id 
  group by user_loans.user_id, user_profiles.created_at
  order by user_profiles.created_at desc
  limit 10;
  </code></pre></li>
  <li><p>Retrieves the 10 newest users with a loan added and marked as a featured profile</p>
  
  <pre><code>select user_loans.user_id,
    user_profiles.created_at,
    sum(user_loans.balance) as total_balance,
    sum(user_loans.origination_principal_amount) as origination_principal_amount,
    mode() within group (order by user_profiles.first_name) as first_name,
    mode() within group (order by user_profiles.last_name) as last_name,
    mode() within group (order by user_profiles.city) as city,
    mode() within group (order by user_profiles.state) as state
  from user_loans 
  join user_profiles on user_loans.user_id = user_profiles.user_id
  where user_profiles.featured = true
  group by user_loans.user_id, user_profiles.created_at
  order by user_profiles.created_at
  limit 10;
  </code></pre></li>
  </ul>
  
  <p>They all work as expected individually, but I prefer if I could combine them, and format them so that my end result is:</p>
  
  <pre><code>most_loans             | newest                 | featured
  -----------------------+------------------------+-----------------------
  [results_from_query#1] | [results_from_query#2] | [results_from_query#3]
  </code></pre>
  
  <p>I want the results_from_query to be an array of objects created by the results of the queries mentioned above.</p>
  
  <p>I tried using a combination of <code>json_agg</code> and <code>json_build_object</code> but I got errors about having nested aggregate functions. I'm stumped on where to go from here.</p>
  
  <p>At this point I'm ok not formatting so specifically. I can handle that programatically. If I could just get the results of the three queries combined that would be a good starting point for me. They all have the same columns.</p>
  
  <p>So I'm trying <code>union</code></p>
  
  <pre><code>(select user_loans.user_id,
         user_profiles.created_at,
         sum(user_loans.balance) as total_balance,
         sum(user_loans.origination_principal_amount) as origination_principal_amount,
         mode() within group (order by user_profiles.first_name) as first_name,
         mode() within group (order by user_profiles.last_name) as last_name,
         mode() within group (order by user_profiles.city) as city,
         mode() within group (order by user_profiles.state) as state,
         'top' as type
  from user_loans
  join user_profiles on user_loans.user_id = user_profiles.user_id
  group by user_loans.user_id, user_profiles.created_at
  order by total_balance desc
  limit 10)
  union all
  (select user_loans.user_id,
         user_profiles.created_at,
         sum(user_loans.balance) as total_balance,
         sum(user_loans.origination_principal_amount) as origination_principal_amount,
         mode() within group (order by user_profiles.first_name) as first_name,
         mode() within group (order by user_profiles.last_name) as last_name,
         mode() within group (order by user_profiles.city) as city,
         mode() within group (order by user_profiles.state) as state,
         'newest' as type
  from user_loans 
  join user_profiles on user_loans.user_id = user_profiles.user_id 
  group by user_loans.user_id, user_profiles.created_at
  order by user_profiles.created_at desc
  limit 10)
  union all
  (select user_loans.user_id,
         user_profiles.created_at,
         sum(user_loans.balance) as total_balance,
         sum(user_loans.origination_principal_amount) as origination_principal_amount,
         mode() within group (order by user_profiles.first_name) as first_name,
         mode() within group (order by user_profiles.last_name) as last_name,
         mode() within group (order by user_profiles.city) as city,
         mode() within group (order by user_profiles.state) as state,
         'featured' as type
  from user_loans 
  join user_profiles on user_loans.user_id = user_profiles.user_id
  where user_profiles.featured = true
  group by user_loans.user_id, user_profiles.created_at
  order by user_profiles.created_at
  limit 10)
  </code></pre>
  
  <p>Even closer. I have all the queries combined, and a column indicating which query the row came from. This is enough where I can do the formatting programmatically. I'm curious if I can group by <code>type</code> and use <code>json_agg</code> and <code>json_build_object</code> at this point to achieve my original goal. </p>
  
  <hr>
  
  <p>I think I'll end with this. It's not 100% what I wanted but it accomplishes the same end goal. A single query that returns 3 arrays with results. With this approach they are 3 separate rows with a single value, the array of objects. Not sure how I can combine this into a single row with named columns, but close enough.</p>
  
  <pre><code>SELECT json_agg(row_to_json(all_featured)) as results
  from
  ((select user_loans.user_id,
         user_profiles.created_at,
         sum(user_loans.balance) as total_balance,
         sum(user_loans.origination_principal_amount) as origination_principal_amount,
         mode() within group (order by user_profiles.first_name) as first_name,
         mode() within group (order by user_profiles.last_name) as last_name,
         mode() within group (order by user_profiles.city) as city,
         mode() within group (order by user_profiles.state) as state,
         'top' as type
  from user_loans
  join user_profiles on user_loans.user_id = user_profiles.user_id
  group by user_loans.user_id, user_profiles.created_at
  order by total_balance desc
  limit 10)
  union all
  (select user_loans.user_id,
         user_profiles.created_at,
         sum(user_loans.balance) as total_balance,
         sum(user_loans.origination_principal_amount) as origination_principal_amount,
         mode() within group (order by user_profiles.first_name) as first_name,
         mode() within group (order by user_profiles.last_name) as last_name,
         mode() within group (order by user_profiles.city) as city,
         mode() within group (order by user_profiles.state) as state,
         'newest' as type
  from user_loans 
  join user_profiles on user_loans.user_id = user_profiles.user_id 
  group by user_loans.user_id, user_profiles.created_at
  order by user_profiles.created_at desc
  limit 10)
  union all
  (select user_loans.user_id,
         user_profiles.created_at,
         sum(user_loans.balance) as total_balance,
         sum(user_loans.origination_principal_amount) as origination_principal_amount,
         mode() within group (order by user_profiles.first_name) as first_name,
         mode() within group (order by user_profiles.last_name) as last_name,
         mode() within group (order by user_profiles.city) as city,
         mode() within group (order by user_profiles.state) as state,
         'featured' as type
  from user_loans 
  join user_profiles on user_loans.user_id = user_profiles.user_id
  where user_profiles.featured = true
  group by user_loans.user_id, user_profiles.created_at
  order by user_profiles.created_at
  limit 10)) as all_featured
  group by type
  </code></pre>