title: Selecting all tickers where price has increased by 5 percent within a time range
body: <p>This one is not easy at all it seems. </p>
  
  <p>I have a table <strong>data</strong>: </p>
  
  <pre><code>String ticker, Double price, Date time
  --------------------------------------
  </code></pre>
  
  <p>How can I given the above table: </p>
  
  <p><strong>SELECT ticker WHERE price has increased x percent AND time BETWEEN '2014-10-01' AND '2014-10-31'</strong> ?</p>
  
  <p>What one needs to do is for each ticker, determine the last and first value and divide them. </p>
  
  <p>I have tried this but it is not working for obvious reasons: </p>
  
  <pre><code>SELECT * FROM (
      SELECT ticker, min(ctid) as min, max(ctid) as max
      FROM data 
      WHERE 
      time BETWEEN '2014-10-01' AND '2014-10-31'  
      GROUP by ticker, time
      ORDER by ticker, time ASC
      ) X
  WHERE 
      1.05 &lt; (
          SELECT value
          FROM data 
          WHERE 
          time BETWEEN '2014-10-01' AND '2014-10-31'
          AND
          ticker = X.ticker
          AND 
          ctid = X.min
      )
      / 
      (
          SELECT value
          FROM data 
          WHERE 
          time BETWEEN '2014-10-01' AND '2014-10-31'
          AND
          ticker = X.ticker
          AND 
          ctid = X.max
      )
  </code></pre>
  
  <p>The above query is grouped by ticker and time, and the min and max are should be for the entire dataset. But I do get several tickers as a returned result, so I am not sure what is actually going on there. </p>
  
  <p>I have also investigated doing this through WINDOW functions, there is a sample that's also not working: </p>
  
  <pre><code>SELECT 
  ticker,
  first_value(price) over W as first, last_value(price) over W as last 
  FROM data 
  WHERE 
  time BETWEEN '2014-10-01' AND '2014-10-31'
  WINDOW W as (
      partition by ticker, time
  ) 
  ORDER BY ticker, time ASC
  </code></pre>
  
  <p>Does anyone know how to do this kind of query, on any database?</p>
  
  <p>I am using PostgreSQL, why you are seeing <strong>ctid</strong> which in other databases is the same as <strong>ROW_ID</strong>. </p>
  
  <p>But this problem is not related to PostgreSQL only.</p>
  
  <p><strong>Dataset:</strong> </p>
  
  <pre><code>create table data
  (
   ticker varchar(5),
   price numeric(5,2), 
   time date);
  
  insert into data (ticker, price, time) values ('ABC',1,'2014-10-01');
  insert into data (ticker, price, time) values ('ABC',0.95,'2014-10-02');
  insert into data (ticker, price, time) values ('ABC',1,'2014-10-03');
  insert into data (ticker, price, time) values ('ABC',1.04,'2014-10-04');
  insert into data (ticker, price, time) values ('ABC',1.05,'2014-10-05');
  insert into data (ticker, price, time) values ('ABC',1.06,'2014-10-06');
  insert into data (ticker, price, time) values ('ABC',1.07,'2014-10-07');
  insert into data (ticker, price, time) values ('ABC',1.09,'2014-10-08');
  insert into data (ticker, price, time) values ('ABC',2,'2014-10-09');
  insert into data (ticker, price, time) values ('ABC',2,'2014-10-10');
  insert into data (ticker, price, time) values ('ABC',1.9,'2014-10-11');
  insert into data (ticker, price, time) values ('ABC',1.8,'2014-10-12');
  insert into data (ticker, price, time) values ('ABC',1.7,'2014-10-13');
  insert into data (ticker, price, time) values ('ABC',1.6,'2014-10-14');
  insert into data (ticker, price, time) values ('ABC',1.5,'2014-10-15');
  insert into data (ticker, price, time) values ('ABC',1.4,'2014-10-16');
  insert into data (ticker, price, time) values ('ABC',1.6,'2014-10-17');
  insert into data (ticker, price, time) values ('ABC',1.4,'2014-10-18');
  insert into data (ticker, price, time) values ('ABC',1.3,'2014-10-19');
  insert into data (ticker, price, time) values ('ABC',1.2,'2014-10-31');
  insert into data (ticker, price, time) values ('XYZ',.95,'2014-10-01');
  insert into data (ticker, price, time) values ('XYZ',1,'2014-10-31');
  insert into data (ticker, price, time) values ('PDQ',1.4,'2014-10-01');
  insert into data (ticker, price, time) values ('PDQ',1.3,'2014-10-31');
  insert into data (ticker, price, time) values ('XKCD',.01,'2014-10-01');
  insert into data (ticker, price, time) values ('XKCD',100,'2014-10-31');
  insert into data (ticker, price, time) values ('Z8T',1,'2014-10-01');
  insert into data (ticker, price, time) values ('Z8T',1.04,'2014-10-31');
  </code></pre>