wHATcOLOR
09-13-2009, 04:07 AM
i got a question.
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? |