View Full Version : anyone on the board right now decent at sql?


wHATcOLOR
09-13-2009, 04:07 AM
i got a question.

wHATcOLOR
09-13-2009, 04:08 AM
I have a table where each record is a purchase made. Each record contains basic information such as who made the purchase, when they made it, what the amount was, and what was purchased.

What I'm looking to do is separate the data between first-time purchases, and repeat purchases. A first time purchase is the earliest purchase for a user. And a repeat purchase is any purchase by that user that occurs after their first purchase. I know how to do this in excel, but I'd like to do it in sql.

I was thinking a good way to do it would be to return in my results a new field called "initial purchase" where it'd say "yes" for the first purchases, and "no" for the subsequent ones. But I'm struggling with how to do this.

Do you have any ideas how to write something like that in SQL?

Future Boy
09-13-2009, 04:17 AM
did you try KGB yet

wHATcOLOR
09-13-2009, 04:28 AM
no, i don't know what that is

ohnoitsbonnie
09-13-2009, 10:43 AM
The Russians are pretty good at this kinda stuff. You could hire them…

nth0
09-13-2009, 10:48 AM
You could add a new table called first_purchases where you'd have two fields, user_id and purchase_id. That way you only have to store the actual first purchases, instead of adding a new field which will be "no" for most of the data.

slunken
09-13-2009, 11:28 AM
I came in here because i don't know what sql is.

ohnoitsbonnie
09-13-2009, 12:54 PM
It is times like these we need a roast beef troll

Future Boy
09-13-2009, 01:32 PM
It never occurred to me that those kgb fools might be a regional thing.

distance
09-13-2009, 11:10 PM
I have a table where each record is a purchase made. Each record contains basic information such as who made the purchase, when they made it, what the amount was, and what was purchased.

What I'm looking to do is separate the data between first-time purchases, and repeat purchases. A first time purchase is the earliest purchase for a user. And a repeat purchase is any purchase by that user that occurs after their first purchase. I know how to do this in excel, but I'd like to do it in sql.

I was thinking a good way to do it would be to return in my results a new field called "initial purchase" where it'd say "yes" for the first purchases, and "no" for the subsequent ones. But I'm struggling with how to do this.

Do you have any ideas how to write something like that in SQL?

couldn't you just do a query for purchases by an ID, sort by date and take the earliest one rather than having to add an extra field in the table?