title: shape 2 complex GROUP BY / PARTITION BY queries into 1 query
body: <p>I was trying to solve a task with just 1 query using <code>GROUP BY</code>, but ended up having a complex solution using <code>WITH</code> clause - so I have 2 queries combined. But I suspect I tend to over-engineer it.</p>
  
  <p>Say, we have 3 tables:</p>
  
  <p>(see the whole SQL for the tables' layout under section <em>schema</em> below)</p>
  
  <ul>
  <li>authors</li>
  <li>subjects</li>
  <li>books</li>
  </ul>
  
  <p>For example, the task reads</p>
  
  <blockquote>
    <p>find authors who have written books on more than 2 subjects</p>
  </blockquote>
  
  <p>During my research I have come up with complex, over-engineered 2 solutions:</p>
  
  <p><strong>solution 1</strong> - using <code>PARTITION BY</code> clause work out the row number</p>
  
  <pre><code>WITH temp1 AS 
  (
      SELECT author_id, subject_id, ROW_NUMBER () OVER (PARTITION BY author_id) as rn
      FROM books
      GROUP BY author_id, subject_id
  )
  SELECT DISTINCT author_id FROM temp1 WHERE rn &gt; 2
  ;
  </code></pre>
  
  <p><strong>solution 2</strong> - combining 2 queries containing <code>GROUP BY</code> clause</p>
  
  <pre><code>WITH temp1 AS 
  (
      SELECT author_id, subject_id
      FROM books
      GROUP BY author_id, subject_id
  )
  SELECT DISTINCT author_id, COUNT(*) FROM temp1
  GROUP BY author_id
  HAVING COUNT(*) &gt; 2
  ;
  </code></pre>
  
  <p><strong>Is there any way to solve such a task with just 1 query?</strong></p>
  
  <hr>
  
  <h2>Schema</h2>
  
  <p>The SQL below applies to PostgreSQL</p>
  
  <pre><code>CREATE SEQUENCE authors_id_seq;
  
  CREATE TABLE authors
  (
      id bigint NOT NULL DEFAULT nextval('authors_id_seq'::regclass),
      name TEXT NOT NULL,
      CONSTRAINT pk_authors PRIMARY KEY (id)
  );
  
  CREATE SEQUENCE subjects_id_seq;
  
  CREATE TABLE subjects
  (
      id bigint NOT NULL DEFAULT nextval('subjects_id_seq'::regclass),
      name TEXT NOT NULL,
      CONSTRAINT pk_subjects PRIMARY KEY (id)
  );
  
  CREATE SEQUENCE books_id_seq;
  
  CREATE TABLE books
  (
      id bigint NOT NULL DEFAULT nextval('books_id_seq'::regclass),
      name TEXT NOT NULL,
      popularity int NOT NULL,
      author_id bigint NOT NULL,
      subject_id bigint NOT NULL,
      CONSTRAINT pk_books PRIMARY KEY (id),
      CONSTRAINT fk_books_authors_author_id FOREIGN KEY (author_id)
          REFERENCES authors (id) MATCH SIMPLE
          ON UPDATE NO ACTION
          ON DELETE CASCADE,
      CONSTRAINT fk_books_subjects_subject_id FOREIGN KEY (subject_id)
          REFERENCES subjects (id) MATCH SIMPLE
          ON UPDATE NO ACTION
          ON DELETE CASCADE
  );
  </code></pre>
  
  <p>seed data:</p>
  
  <pre><code>INSERT INTO authors (name) VALUES 
  ('John Doe'),
  ('Friedrich Nietzsche');
  INSERT INTO subjects (name) VALUES 
  ('anatomy'),
  ('geography'),
  ('nature');
  INSERT INTO books (name, popularity, author_id, subject_id) VALUES
  ('body', 5, 1, 1),
  ('earth', 10, 1, 2),
  ('ocean', 9, 1, 2),
  ('animals', 9, 1, 3),
  ('birds', 7, 2, 3);
  </code></pre>