Resp.: need help in building a query

2008/11/7, Devil™ Dhuvader <gibsosmat(at)gmail(dot)com>:
> its like sum up entries of each user in order table backwards (i.e from last
> entry to the first) and find the entry that has sum > $500.
> If there is some user who didnt even make 500 till now in my shop return the
> first date of transaction/order.
>
> ex:
> Orders(order_id, user_id, amount_paid, create_timestamp)
> values:
> (1, 1, 100, 1)
> (2, 1, 300, 2)
> (3, 2, 100, 2)
> (4, 2, 100, 3)
> (5, 1, 100, 4)
> (6, 1, 200, 5)
> (7, 2, 150, 5)
>
> for user 1: the order_id = 2
> for user 2: the order_id = 3 (coz he couldnt make 500)
>

Try:

bdteste=# SELECT o1.user_id, o1.order_id, '>= 500' AS cond FROM Orders o1
bdteste-# WHERE (SELECT sum(o2.amount_paid) FROM Orders o2 WHERE
o2.user_id = o1.user_id AND o2.order_id > o1.order_id) < 500 AND
bdteste-# (SELECT sum(o2.amount_paid) FROM Orders o2 WHERE
o2.user_id = o1.user_id AND o2.order_id >= o1.order_id) >=500
bdteste-# UNION
bdteste-# SELECT user_id, min(order_id) AS "min order id", '< 500' AS
cond FROM Orders
bdteste-# WHERE user_id IN (SELECT user_id FROM Orders GROUP BY
user_id HAVING sum(amount_paid) < 500)
bdteste-# GROUP BY user_id;
user_id | order_id | cond
---------+----------+--------
1 | 2 | >= 500
2 | 3 | < 500

Osvaldo