CODEWITHSUNDEEP
sqlpostgresqllag
AzFlin
AzFlin

Reputation: 2030

SQL query to select today and previous day's price

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 |
+------- +-------------+------------+

Upvotes: 2

Views: 2264

Answers (2)

Ed Baker
Ed Baker

Reputation: 663

Best bet is to use a window function with an aggregated case statement which is used to create a pivot on the data.

You can see more on window functions here: http://www.postgresql.org/docs/current/static/tutorial-window.html

Below is a pseudo code version of where you may need to head to answer your question (sorry I couldn't validate it due to not have a postgres database setup).

Select
ticker, 
SUM(CASE WHEN rank = 1 THEN price ELSE 0 END) today, 
SUM(CASE WHEN rank = 2 THEN price ELSE 0 END) yesterday
FROM (
SELECT
ticker,
price, 
date, 
rank() OVER (PARTITION BY ticker ORDER BY date DESC) as rank
FROM your_table) p
WHERE rank in (1,2)
GROUP BY ticker.

Edit - Updated the case statement with an 'else'

Upvotes: 1

zedfoxus
zedfoxus

Reputation: 37099

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.

Upvotes: 1

Related Questions