title: Bucketing Data, If Bucket Size Greater Then 1 Set Value To 1 Else 0
body: <p>I'm using <code>snowflake</code> to attempt to bucket rows based on two columns and if that bucket size is greater then 1 then I need the value of of some new column to be 1, otherwise I would set it to 0. It's similar to <code>NTILE()</code> except that I would need this to be dynamic.</p>
  <p>The table that I have looks like the following:</p>
  <pre><code>ARTICLE_ID      USER_ID     COUNTRY_NAME    NUM_COUNTRIES   NUM_AUTHORS
  ------------------------------------------------------------------------
  30522143        ecf330d     Singapore       1               4
  30522143        86b0faa     Singapore       1               4
  30522143        d331b33     Singapore       1               4
  30522143        d331b33     Singapore       1               4
  ------------------------------------------------------------------------
  30522139        1d08cfd     United States   2               3
  30522139        ec9c065     Canada          2               3
  30522139        a750bff     United States   2               3
  ------------------------------------------------------------------------
  23410385        1d08cfd     United States   3               3
  23410385        ec9c065     Mexico          3               3
  23410385        a41b19c     France          3               3
  ------------------------------------------------------------------------
  30285132        d331b33     Chile           2               4
  30285132        1d08cf2     Peru            2               4
  30285132        a750bff     Chile           2               4
  30285132        d742bb5     Peru            2               4
  ------------------------------------------------------------------------
  ...
  </code></pre>
  <p>The two columns that I would need to bucket are by <code>ARTICLE_ID</code> then <code>COUNTRY_NAME</code>. Looking at the above table if we take <code>ARTICLE_ID</code> <code>30522143</code> we would have <code>1</code> bucket of <code>4</code> <code>USER_ID</code> since they are all <code>Singapore</code> then the value of <code>HAS_REPEATED_COUNTRY</code> would be 1.</p>
  <p>In the case of <code>ARTICLE_ID</code> <code>30522139</code> we would have two sub-buckets one for <code>United States</code> containing <code>2</code> <code>USER_ID</code>s and another sub-bucket <code>(Canada)</code> containing <code>1</code> <code>USER_ID</code> which would result in all those users associated with <code>United States</code> to have a value of <code>1</code> and the sole member associated with <code>Canada</code> to have a value of <code>0</code> in <code>HAS_REPEATED_COUNTRY</code>.</p>
  <p>Hence the table would resemble:</p>
  <pre><code>ARTICLE_ID      USER_ID     COUNTRY_NAME    NUM_COUNTRIES   NUM_AUTHORS     HAS_REPEATED_COUNTRY
  ------------------------------------------------------------------------------------------------
  30522143        ecf330d     Singapore       1               4               1
  30522143        86b0faa     Singapore       1               4               1
  30522143        d331b33     Singapore       1               4               1
  30522143        d331b33     Singapore       1               4               1
  ------------------------------------------------------------------------------------------------
  30522139        1d08cfd     United States   2               3               1
  30522139        ec9c065     Canada          2               3               0
  30522139        a750bff     United States   2               3               1
  ------------------------------------------------------------------------------------------------
  23410385        1d08cfd     United States   3               3               0
  23410385        ec9c065     Mexico          3               3               0
  23410385        a41b19c     France          3               3               0
  ------------------------------------------------------------------------------------------------
  30285132        d331b33     Chile           2               4               1
  30285132        1d08cf2     Peru            2               4               1
  30285132        a750bff     Chile           2               4               1
  30285132        d742bb5     Peru            2               4               1
  ------------------------------------------------------------------------------------------------
  ...
  </code></pre>
  <p>Is there any way of producing the latter table from the former?</p>