Thread: need help in building a query

<font color="midnightblue" face="Verdana, Arial, Helvetica" size="2"><span>hi,<br />I need some help in creating a
sql.<br/>the problem is as below.<br /><br />assume that:<br />I am a store keeper <br /> and I have the list of
customer(user_id)transactions in my order table.<br />schema: Orders(order_id, user_id, amount_paid,
create_timestamp)<br/><br />I want to give discount of 10% for the customer who made orders of worth (sum) at least
$500in the least time from now (i.e last few days, but NOT ALL TIME LEAST TIME TO BUY $500 WORTH GOODS).<br />and 9%
discountfor the customer who made $500 in second least time from now.<br />and so on<br /><br /></span></font><font
color="midnightblue"face="Verdana, Arial, Helvetica" size="2"><span>there is no time constraint.<br /> it customer
couldtake any amount of time to make $500.<br />the customer might not even made $500 bill till now in that case I
shouldget the first date.<br /><br />the result can take upto the first entry time of the table itself.<br /><br />in
shortits like sum up entries of each user in order table backwards (i.e from last entry to the first) and find the
entrythat has sum > $500. <br />If there is some user who didnt even make 500 till now in my shop return the first
dateof transaction/order</span></font>.<br /><br />can anyone help me on this?  

none can help me? <br /><br /><div class="gmail_quote">On Tue, Nov 4, 2008 at 9:08 PM, Devil™ Dhuvader <span
dir="ltr"><<ahref="mailto:gibsosmat@gmail.com">gibsosmat@gmail.com</a>></span> wrote:<br /><blockquote
class="gmail_quote"style="border-left: 1px solid rgb(204, 204, 204); margin: 0pt 0pt 0pt 0.8ex; padding-left:
1ex;"><fontcolor="midnightblue" face="Verdana, Arial, Helvetica" size="2"><span>hi,<br />I need some help in creating a
sql.<br/>the problem is as below.<br /><br />assume that:<br />I am a store keeper <br /> and I have the list of
customer(user_id)transactions in my order table.<br />schema: Orders(order_id, user_id, amount_paid,
create_timestamp)<br/><br />I want to give discount of 10% for the customer who made orders of worth (sum) at least
$500in the least time from now (i.e last few days, but NOT ALL TIME LEAST TIME TO BUY $500 WORTH GOODS).<br />and 9%
discountfor the customer who made $500 in second least time from now.<br />and so on<br /><br /></span></font><font
color="midnightblue"face="Verdana, Arial, Helvetica" size="2"><span>there is no time constraint.<br /> it customer
couldtake any amount of time to make $500.<br />the customer might not even made $500 bill till now in that case I
shouldget the first date.<br /><br />the result can take upto the first entry time of the table itself.<br /><br />in
shortits like sum up entries of each user in order table backwards (i.e from last entry to the first) and find the
entrythat has sum > $500. <br />If there is some user who didnt even make 500 till now in my shop return the first
dateof transaction/order</span></font>.<br /><br />can anyone help me on this? </blockquote></div><br /> 

I personally would help if I understood what you need. I'm sure others feel the same way. Provide DDL, sample data, and
expectedresult of the query. Maybe you'll have better luck...<br /><br /><div class="gmail_quote"> On Thu, Nov 6, 2008
at11:15 AM, Devil™ Dhuvader <span dir="ltr"><<a href="mailto:gibsosmat@gmail.com">gibsosmat@gmail.com</a>></span>
wrote:<br/><blockquote class="gmail_quote" style="border-left: 1px solid rgb(204, 204, 204); margin: 0pt 0pt 0pt 0.8ex;
padding-left:1ex;"> none can help me? <br /><br /><div class="gmail_quote">On Tue, Nov 4, 2008 at 9:08 PM, Devil™
Dhuvader<span dir="ltr"><<a href="mailto:gibsosmat@gmail.com" target="_blank">gibsosmat@gmail.com</a>></span>
wrote:<br/><blockquote class="gmail_quote" style="border-left: 1px solid rgb(204, 204, 204); margin: 0pt 0pt 0pt 0.8ex;
padding-left:1ex;"><font color="midnightblue" face="Verdana, Arial, Helvetica" size="2"><span>hi,<br />I need some help
increating a sql.<br />the problem is as below.<br /><br />assume that:<br />I am a store keeper <br /> and I have the
listof customer(user_id) transactions in my order table.<br />schema: Orders(order_id, user_id, amount_paid,
create_timestamp)<br/><br />I want to give discount of 10% for the customer who made orders of worth (sum) at least
$500in the least time from now (i.e last few days, but NOT ALL TIME LEAST TIME TO BUY $500 WORTH GOODS).<br />and 9%
discountfor the customer who made $500 in second least time from now.<br />and so on<br /><br /></span></font><font
color="midnightblue"face="Verdana, Arial, Helvetica" size="2"><span>there is no time constraint.<br /> it customer
couldtake any amount of time to make $500.<br />the customer might not even made $500 bill till now in that case I
shouldget the first date.<br /><br />the result can take upto the first entry time of the table itself.<br /><br />in
shortits like sum up entries of each user in order table backwards (i.e from last entry to the first) and find the
entrythat has sum > $500. <br />If there is some user who didnt even make 500 till now in my shop return the first
dateof transaction/order</span></font>.<br /><br />can anyone help me on this? </blockquote></div><br
/></blockquote></div><br/> 

2008/11/7, Devil™ Dhuvader <gibsosmat@gmail.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

Devil™ Dhuvader wrote:
>   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)


I might also take a crack at the SQL if I understood the question; but I 
still don't know what you want.  You're initial question indicated you 
were trying to calculate the proper discount for each customer.  This 
example mentions neither customers nor discounts at all and appears to 
be looking for a specific transaction instead.

Frank

yes, I am picking up the specific transaction (order_id) so that I can pickup the create_timestamp and sort it
descending.<br/>that will list me those users who did transaction more than 500 in the least time.<br />then I can give
discountfor top 10 users<br /> expected output: user_id, create_timestamp(desc) with limit 10.<br />then I will give
discountof 10%, 9%, .. etc.<br /><br />if you know ageing credit. its just like that.<br /><br /><div
class="gmail_quote">OnSat, Nov 8, 2008 at 2:30 AM, Frank Bax <span dir="ltr"><<a
href="mailto:fbax@sympatico.ca">fbax@sympatico.ca</a>></span>wrote:<br /><blockquote class="gmail_quote"
style="border-left:1px solid rgb(204, 204, 204); margin: 0pt 0pt 0pt 0.8ex; padding-left: 1ex;"><div
class="Ih2E3d">Devil™Dhuvader wrote:<br /><blockquote class="gmail_quote" style="border-left: 1px solid rgb(204, 204,
204);margin: 0pt 0pt 0pt 0.8ex; padding-left: 1ex;">  its like sum up entries of each user in order table backwards
(i.efrom last entry to the first) and find the entry that has sum > $500.<br /> If there is some user who didnt even
make500 till now in my shop return the first date of transaction/order .<br /><br /> ex:<br /> Orders(order_id,
user_id,amount_paid, create_timestamp)<br /> values:<br /> (1, 1, 100, 1)<br /> (2, 1, 300, 2)<br /> (3, 2, 100, 2)<br
/>(4, 2, 100, 3)<br /> (5, 1, 100, 4)<br /> (6, 1, 200, 5)<br /> (7, 2, 150, 5)<br /><br /> for user 1: the order_id =
2<br/> for user 2: the order_id = 3 (coz he couldnt make 500)<br /></blockquote><br /><br /></div> I might also take a
crackat the SQL if I understood the question; but I still don't know what you want.  You're initial question indicated
youwere trying to calculate the proper discount for each customer.  This example mentions neither customers nor
discountsat all and appears to be looking for a specific transaction instead.<br /><br /> Frank<br /><font
color="#888888"><br/> -- <br /> Sent via pgsql-sql mailing list (<a href="mailto:pgsql-sql@postgresql.org"
target="_blank">pgsql-sql@postgresql.org</a>)<br/> To make changes to your subscription:<br /><a
href="http://www.postgresql.org/mailpref/pgsql-sql"target="_blank">http://www.postgresql.org/mailpref/pgsql-sql</a><br
/></font></blockquote></div><br/> 

<div class="gmail_quote"><blockquote class="gmail_quote" style="border-left: 1px solid rgb(204, 204, 204); margin: 0pt
0pt0pt 0.8ex; padding-left: 1ex;"><div class="Ih2E3d"><br /></div>Try:<br /><br /> bdteste=# SELECT o1.user_id,
o1.order_id,'>= 500' AS cond FROM Orders o1<br /> bdteste-#  WHERE (SELECT sum(o2.amount_paid) FROM Orders o2
WHERE<br/> o2.user_id = o1.user_id AND o2.order_id > o1.order_id) < 500 AND<br /> bdteste-#        (SELECT
sum(o2.amount_paid)FROM Orders o2 WHERE<br /> o2.user_id = o1.user_id AND o2.order_id >= o1.order_id) >=500<br />
bdteste-#UNION<br /> bdteste-# SELECT user_id, min(order_id) AS "min order id", '< 500' AS<br /> cond FROM Orders<br
/>bdteste-#  WHERE user_id IN (SELECT user_id FROM Orders GROUP BY<br /> user_id HAVING sum(amount_paid) < 500)<br
/>bdteste-#  GROUP BY user_id;<br />  user_id | order_id |  cond<br /> ---------+----------+--------<br />       1 |  
    2 | >= 500<br />       2 |        3 | < 500<br /><font color="#888888"><br /> Osvaldo<br
/></font></blockquote></div><br/>this sounds too heavy. as the order table has too many entries (4105258)<br />