title: Find happy ducks sql puzzle
body: <p>Adapted from here: <a href="http://sridhargaddam28.blogspot.com/2017/03/sql-puzzle.html" rel="nofollow noreferrer">http://sridhargaddam28.blogspot.com/2017/03/sql-puzzle.html</a></p>
  <p><strong>Setup</strong></p>
  <p>Each duck belongs to a particular species and lives in one of the several ponds. Each pond is described by its temperature and location city. Each species is described by two values: its thermal preferences and its temperature limit. Depending on their thermal preferences, duck from a given species may prefer temperatures not higher or not lower than the temperature limit. A duck of a certain species will only feel comfortable if the temperature in the pond fulfills its thermal preferences(i.e. it is above/below or equal to the species' limit temperature).</p>
  <p>The column temp_preferences in table species determines whether temp_limit is minimum (&quot;+&quot;) or maximum (&quot;-&quot;) acceptable temperature for the given species. Rows in all tables are ordered by the column id.
  For each pond we would like to count the ducks which live in it and feel comfortable.</p>
  <p>You are giving 3 tables: species, ponds and ducks</p>
  <p><strong>Question</strong></p>
  <p>Write an SQL query that returns a table consisting of 2 columns: pond_id, happy_ducks, ordered by pond_id. <strong>Every pond should appear in this table.</strong></p>
  <p><strong>Setup Code for SQLFiddle (sqlfiddle.com)</strong></p>
  <pre><code>create table species(
  id integer not null,
  temp_preferences varchar(1) check(temp_preferences in('+', '-')),
  temp_limit integer not null,
    unique(id)
  );
  
  create table ponds (
  id integer not null,
  temperature integer not null,
  city varchar(10),
  unique(id)
  );
  
  create table ducks (
  id integer not null,
  name varchar(10),
  species_id integer not null,
  pond_id integer not null,
  unique(id)
  );
  
  INSERT Into species VALUES (30, '+', 15);
  INSERT Into species VALUES (40, '-', 20);
  INSERT Into species VALUES (50, '-', 31);
  
  INSERT Into ponds VALUES (1, 13, 'Oregon');
  INSERT Into ponds VALUES (2, 31, 'Oregano');
  INSERT Into ponds VALUES (3, 25, 'Test Lake');
  
  
  INSERT Into ducks VALUES (1, 'Martin', 50,2 );
  INSERT Into ducks VALUES (3, 'Bruno', 30,1 );
  INSERT Into ducks VALUES (9, 'Ignacio', 40,2 );
  INSERT Into ducks VALUES (27, 'Hedwig', 40,1 );
  INSERT Into ducks VALUES (81, 'Marina', 30,2 );
  </code></pre>
  <p><strong>My Answer &amp; Problem</strong></p>
  <p>I have the following but it doesnt return the empty pond (pond 3). Can anyone help me out?</p>
  <pre><code>SELECT p.id, COUNT(d.id) as happy_ducks FROM ducks d
  FULL OUTER JOIN ponds p on d.pond_id=p.id
  FULL OUTER JOIN species s ON d.species_id=s.id
  WHERE
  (p.temperature &lt;= s.temp_limit and s.temp_preferences='+') OR
  (p.temperature &gt;= s.temp_limit and s.temp_preferences='-')
  GROUP BY p.id
  </code></pre>