title: MODE aggregation function with redshift
body: <p>I have the following tables:</p>
  
  <p><strong>customers</strong></p>
  
  <pre><code>customer_id name
  ----------------
  1           bob
  2           alice
  3           tim
  </code></pre>
  
  <p><strong>purchases</strong></p>
  
  <pre><code>id customer_id item_bought
  --------------------------
  1  1           hat
  2  1           shoes
  3  2           glasses
  3  2           glasses
  4  2           book
  5  3           shoes
  6  1           hat
  </code></pre>
  
  <p>And I want to following result:</p>
  
  <pre><code>customer_name item_bought_most_often
  ------------------------------------
  bob           hat
  alice         glasses
  tim           shoes
  </code></pre>
  
  <p>I would do this like this (not actually tried, just the idea):</p>
  
  <pre><code>SELECT customer.name as customer_name,
    MODE(item_bought) as item_bought_most_ofen
  FROM customers
  INNER JOIN purchases USING (customer_id)
  GROUP_BY customer_id
  </code></pre>
  
  <p>However, the <a href="https://wiki.postgresql.org/wiki/Aggregate_Mode" rel="noreferrer">MODE aggregation function</a> doesn't exist in Redshift.</p>
  
  <p>It seems that <a href="http://docs.aws.amazon.com/redshift/latest/dg/r_CREATE_FUNCTION.html" rel="noreferrer">Redshift user defined functions</a> are just regular scalar functions, not aggregations functions. So I don't think I can define it myself.</p>
  
  <p>Any workaround ?</p>