title: Get the top patent countries, codes in a BQ public dataset
body: <p>I am trying to use an analytic function to get the top 2 countries with patent applications, and within those top 2 countries, get the top 2 application kinds. For example, the answer will look something like this:</p>
  
  <pre><code>country  -   code 
  US           P
  US           A
  GB           X
  GB           P
  </code></pre>
  
  <p>Here is the query I am using to get this:</p>
  
  <pre><code>SELECT
    country_code,
    MIN(count_country_code) count_country_code,
    application_kind
  FROM (
    WITH
      A AS (
      SELECT
        country_code,
        COUNT(country_code) OVER (PARTITION BY country_code) AS count_country_code,
        application_kind
      FROM
        `patents-public-data.patents.publications`),
      B AS (
      SELECT
        country_code,
        count_country_code,
        DENSE_RANK() OVER(ORDER BY count_country_code DESC) AS country_code_num,
        application_kind,
        DENSE_RANK() OVER(PARTITION BY country_code ORDER BY count_country_code DESC) AS application_kind_num
      FROM
        A)
    SELECT
      country_code,
      count_country_code,
      application_kind
    FROM
      B
    WHERE
      country_code_num &lt;= 2
      AND application_kind_num &lt;= 2) x
  GROUP BY
    country_code,
    application_kind
  ORDER BY
    count_country_code DESC
  </code></pre>
  
  <p>However, unfortunately, I get a "memory exceeded" error due to the over/order/partition. Here is the message:</p>
  
  <blockquote>
    <p>Resources exceeded during query execution: The query could not be executed in the allotted memory. Peak usage: 112% of limit. Top memory consumer(s): sort operations used for analytic OVER() clauses: 98% other/unattributed: 2%</p>
  </blockquote>
  
  <p>How would I go about doing the above query (or other similar queries) without running into these memory errors? This can be tested on the public dataset <a href="https://console.cloud.google.com/marketplace/details/google_patents_public_datasets/google-patents-public-data?filter=solution-type:dataset&amp;q=google%20patents%20public%20datasets&amp;id=2877ec09-debc-41bd-a2d7-df1fd089e4d0" rel="nofollow noreferrer">here</a>. </p>
  
  <p>One crude way to do it (which only works if the fields have a semi-low cardinality), would be to do it as a straightforward aggregation operation and sort the results in-memory outside the DB. For example:</p>
  
  <p><a href="https://i.stack.imgur.com/r1WNt.png" rel="nofollow noreferrer"><img src="https://i.stack.imgur.com/r1WNt.png" alt="enter image description here"></a></p>