samedi 25 juin 2016

PostgreSQL backdating query

I am trying to write a query that will return counted records from the time they were created. The primary key is a particular house which is unique. Another variable is bidder. The house to bidder relationship is 1:1 but there can be multiple records for each bidder (different houses). Another variable is a count (CASE) of results of previous bids that were won. I want to be able to set the count to return the number of previous bids won at the time each house record was created. Currently, my query logs the overall number of previous bids won regardless of the time the house record was created. Any help would be great! Example:

SELECT h.house_id,
    h.bidder_id,
    h.created_date,
    b.bids_won
FROM house h
LEFT JOIN bid_transactions b
ON h.house_id = b.house_id
LEFT JOIN (
SELECT bidder_id,
    COUNT(CASE WHEN created_date IS NOT NULL AND transaction_kind = 'Successful Bid' THEN 1 END) bids_won
FROM bid_transactions
GROUP BY user_id
) b
ON h.bidder_id = b.bidder_id
ORDER BY j.created_date DESC

Aucun commentaire:

Enregistrer un commentaire