I have a relatively complicated query to write; we use Postgres. Basically, I have data across two tables:
Table 1: Historical ticket prices
ticket_seller_id | show_id | low_price | created_at
1 | 17 | 40 | 05/09/2015
2 | 23 | 50 | 06/23/2015
2 | 23 | 60 | 07/23/2015
2 | 23 | 70 | 08/23/2015
3 | 23 | 55 | 07/22/2015
Table 2: Price alerts created by members
show_id | Price | user_id | created_at
17 | 40 | 25 | 02/16/2016
17 | 40 | 26 | 02/16/2016
23 | 50 | 25 | 07/24/2015
What I want to do is: create a results table which has ONLY price alerts for which the "alert price" was lower than an EXISTING historical price. From the data above, it would look like this:
show_id | Price | user_id | created_at
17 | 40 | 25 | 02/16/2016
Note that the $50 price alert for show 23 from user 25 wouldn't be displayed, because by the time the user created this price alert, the low_price had risen to $60. Also note that each ticket_seller has its own low_price for each ticket; the answer needs to find the lowest price available from any ticket vendor that was created_at a date prior to the date the alert was created at. Prices are valid until updated, so the lowest price available for show 23 from seller 2 would be $60 on 8/22/2015, since that's the lowest price at that time.
Any and all help would be appreciated!
Try this:
select show_id, price, user_id, created_at
from price_alerts
where price < (
select price
from ticket_history
where show_id = price_alerts.show_id
and created_at = (
select max(created_at)
from ticket_history
where show_id = price_alerts.show_id
and created_at < price_alerts.created_at))
There are vendor-specific window functions they could be used here, but you have not tagged your question with the database you're using, so this is the vanilla version.