MODE aggregation function with redshift
<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>