SQL help 1

[ridhirao22]

Using Oracle 11g

We have Orders and Customers table. I need to identify the order where the customers are first time buyers or repeated buyers. Could anyone please suggest how I can do this in SQL?

TIA,
RR

 

[ggriffit]

how are your tables structured ? you could look for duplicated addresses with the same name or similar depending on the structure and how detailed you want to go. Personally to remove issues like typos you could use the contacts email address as the unique username type thing.

Greg Griffiths
Livelink Certified Developer & ECM Global Star Champion 2005 & 2006

http://www.greggriffiths.org/livelink/

 

[ridhirao22]

Thank you for quickly responding, Greg.

Table structure for
Orders

Order_id
Order_date
Order amt
Cust_id


Customers

Cust_id
Fname
Lname
Address1
Address2
Address3
City
State
Country
Phone
Email

 

[LKBrwnDBA]

Perhaps all you need to do is count the orders for each customer, and if more than one then customer is repeat buyer else it's first time buyer.

Show us what you have coded.



----------------------------------------------------------------------------
The person who says it can't be done should not interrupt the person doing it. -- Chinese proverb

 

[SantaMufasa]

Or, as an alternative...

break on cust_id
select c.cust_id
      ,c.name
      ,o.order_id
      ,o.order_date
      ,decode(o.order_date
             ,f.min_date,'First-time Customer'
             ,'Returning  Customer') Customer_Status
  from orders o
      ,customers c
      ,(select c2.cust_id,min(order_date) min_date
          from orders o2
              ,customers c2
         where o2.cust_id = c2.cust_id
         group by c2.cust_id) F
 where o.cust_id = c.cust_id
   and o.cust_id = f.cust_id
 order by cust_id,order_date
/

 CUST_ID NAME                         ORDER_ID ORDER_DATE      CUSTOMER_STATUS
-------- -------------------------- ---------- --------------- -------------------
     201 Unisports                          97 28-AUG-92       First-time Customer
     202 OJ Atheletics                      98 31-AUG-92       First-time Customer
     203 Delhi Sports                       99 31-AUG-92       First-time Customer
     204 Womansport                        100 31-AUG-92       First-time Customer
         Womansport                        111 09-SEP-92       Returning  Customer
     205 Kam's Sporting Goods              101 31-AUG-92       First-time Customer
     206 Sportique                         102 01-SEP-92       First-time Customer
     208 Muench Sports                     103 02-SEP-92       First-time Customer
         Muench Sports                     104 03-SEP-92       Returning  Customer
     209 Beisbol Si!                       105 04-SEP-92       First-time Customer
     210 Futbol Sonora                     112 31-AUG-92       First-time Customer
         Futbol Sonora                     106 07-SEP-92       Returning  Customer
     211 Kuhn's Sports                     107 07-SEP-92       First-time Customer
     212 Hamada Sport                      108 07-SEP-92       First-time Customer
     213 Big John's Sports Emporium        109 08-SEP-92       First-time Customer
     214 Ojibway Retail                    110 09-SEP-92       First-time Customer

16 rows selected.

Mufasa
(aka Dave of Sandy, Utah, USA)
“People may forget what you say, but they will never forget how you made them feel.

 

[SantaMufasa]

Or, as another, simplified alternative (using Oracle Analytics funtion, RANK):

break on cust_id
select c.cust_id
      ,c.name
      ,o.order_id
      ,o.order_date
      ,decode(rank() over (partition by c.cust_id
                               order by o.order_date)
             ,1,'First-time Customer'
             ,'Returning  Customer') Customer_Status
  from orders o
      ,customers c
 where o.cust_id = c.cust_id
 order by cust_id,order_date
/

CUST_ID NAME                         ORDER_ID ORDER_DATE      CUSTOMER_STATUS
------- -------------------------- ---------- --------------- -------------------
    201 Unisports                          97 28-AUG-92       First-time Customer
    202 OJ Atheletics                      98 31-AUG-92       First-time Customer
    203 Delhi Sports                       99 31-AUG-92       First-time Customer
    204 Womansport                        100 31-AUG-92       First-time Customer
        Womansport                        111 09-SEP-92       Returning  Customer
    205 Kam's Sporting Goods              101 31-AUG-92       First-time Customer
    206 Sportique                         102 01-SEP-92       First-time Customer
    208 Muench Sports                     103 02-SEP-92       First-time Customer
        Muench Sports                     104 03-SEP-92       Returning  Customer
    209 Beisbol Si!                       105 04-SEP-92       First-time Customer
    210 Futbol Sonora                     112 31-AUG-92       First-time Customer
        Futbol Sonora                     106 07-SEP-92       Returning  Customer
    211 Kuhn's Sports                     107 07-SEP-92       First-time Customer
    212 Hamada Sport                      108 07-SEP-92       First-time Customer
    213 Big John's Sports Emporium        109 08-SEP-92       First-time Customer
    214 Ojibway Retail                    110 09-SEP-92       First-time Customer

16 rows selected.

Mufasa
(aka Dave of Sandy, Utah, USA)
“People may forget what you say, but they will never forget how you made them feel.

 

[ridhirao22]

Thank you and love you for your idea's Dave!

Stars for you

Thanks again,
RR