SQL query to select today and previous day's price

Question

I have historic stock price data that looks like the below. I want to generate a new table that has one row for each ticker with the most recent day's price and its previous day's price. What would be the best way to do this? My database is Postgres.

+---------+------------+------------+
| ticker  | price      | date       |
+---------+------------+------------|
|   AAPL  |          6 | 10-23-2015 |
|   AAPL  |          5 | 10-22-2015 |
|   AAPL  |          4 | 10-21-2015 |
|   AXP   |          5 | 10-23-2015 |
|   AXP   |          3 | 10-22-2015 |
|   AXP   |          5 | 10-21-2015 |
+------- +-------------+------------+

Answer 1

You can do something like this:

with ranking as (
  select ticker, price, dt, 
  rank() over (partition by ticker order by dt desc) as rank
  from stocks
)
select * from ranking where rank in (1,2);

Example: http://sqlfiddle.com/#!15/e45ea/3

Results for your example will look like this:

| ticker | price |                        dt | rank |
|--------|-------|---------------------------|------|
|   AAPL |     6 | October, 23 2015 00:00:00 |    1 |
|   AAPL |     5 | October, 22 2015 00:00:00 |    2 |
|   AXP  |     5 | October, 23 2015 00:00:00 |    1 |
|   AXP  |     3 | October, 22 2015 00:00:00 |    2 |

If your table is large and have performance issues, use a where to restrict the data to last 30 days or so.