title: Select random percentage from a table in Snowflake (while using the WHERE clause)
body: <p>Using this page as a guide:  <a href="https://docs.snowflake.com/en/sql-reference/constructs/sample.html" rel="nofollow noreferrer">https://docs.snowflake.com/en/sql-reference/constructs/sample.html</a></p>
  <p>For this exercise, I need to split a portion of the records in a table 50/50:</p>
  <p><strong>These work. I get almost exactly 50% of the table row count:</strong></p>
  <pre><code>SELECT * FROM MyTable SAMPLE (50);
  SELECT * FROM MyTable TABLESAMPLE (50);
  </code></pre>
  <p>As soon as I apply a WHERE clause, SAMPLE no longer works:</p>
  <pre><code>SELECT * FROM MyTable
  WHERE country = ‘USA’ 
  AND load_date = CURRENT_DATE
  SAMPLE (50);
  </code></pre>
  <p><strong>This led me to this from the above snowflake page:</strong></p>
  <p><em>Method 1</em>; applies sample to one of the joined tables</p>
  <pre><code>select i, j 
      from table1 as t1 inner join table2 as t2 sample (50)
      where t2.j = t1.i 
      ;
  </code></pre>
  <p><em>Method 2</em>; applies sample to the result of the joined tables</p>
  <pre><code>select * 
     from ( 
           select * 
              from t1 join t2
                 on t1.a = t2.c
          ) sample (50);
  </code></pre>
  <p>Both methods work but the number of returned records is 57%, not 50% in both cases.</p>
  <p><strong>Is <code>QUALIFY ROW_NUMBER() OVER (ORDER BY RANDOM())</code> a better option?</strong> While this does work with a WHERE clause, I can’t figure out how to set a percentage instead of a row count max. Example:</p>
  <pre><code>SELECT * FROM MyTable
  WHERE country = ‘USA’
  AND load_date = CURRENT_DATE
  QUALIFY ROW_NUMBER() OVER (ORDER BY RANDOM()) = (50)
  </code></pre>
  <p>--this gives me 50 rows, not 50% of rows or 4,457 rows (total rows after where clause in this example is 8,914)</p>