Customers who continue to order after using coupon

Please start any new threads on our new site at https://forums.sqlteam.com. We've got lots of great SQL Server experts to answer whatever question you can come up with.

All Forums

SQL Server 2008 Forums

Other SQL Server 2008 Topics

Customers who continue to order after using coupon

Author

Topic

fawadafr
Starting Member

47 Posts

Posted - 2010-01-11 : 22:42:29

Hell everyone,

I am very happy to join this community and this is my very first question.

I am trying to write SQL code to find the customers who took advantage of our coupons to place their first orders and continued to order from us.

Here is the SQL code I have written so far but unfortunately it doesn't return valid records. I really appreciate your help and support to help me get this done.


SELECT	o.OrderID
	,o.CustomerID
	,c.FirstName + ' ' + c.LastName AS Name
	,c.Country
	,o.OrderDate
	,o.OrderStatus
	,od.CouponCode
FROM	Orders AS o
JOIN	OrderDetails AS od
ON	o.OrderID = od.OrderID
JOIN	Customers AS c
ON	c.CustomerID = o.CustomerID
WHERE	o.OrderDate > (SELECT	MAX(OrderDate) AS OrderDate
			   FROM		Orders AS o1
			   JOIN		OrderDetails AS od1
			   ON		O1.OrderID = od1.OrderID
			   WHERE	od1.CouponCode IN (SELECT CouponCode FROM Discounts))
ORDER BY o.CustomerID

--
Fawad Rashidi

khtan
In (Som, Ni, Yak)

17689 Posts

Posted - 2010-01-12 : 00:20:33

[code]

;WITH
FirstCouponOrder
AS
(
SELECT CustomerID
FROM
(
SELECT o.CustomerID
,d.CouponCode
,OrderNo = row_number() OVER (PARTITION BY o.CustomerID ORDER BY o.OrderDate)
FROM Orders AS o
INNER JOIN OrderDetails AS od ON o.OrderID = od.OrderID
LEFT JOIN Discounts AS d ON od.CouponCode = d.CouponCode
) fo
WHERE fo.OrderNo = 1
AND fo.CouponCode IS NOT NULL
)
SELECT o.OrderID
,o.CustomerID
,c.FirstName + ' ' + c.LastName AS Name
,c.Country
,o.OrderDate
,o.OrderStatus
,od.CouponCode
FROM Orders AS o
INNER JOIN OrderDetails AS od ON o.OrderID = od.OrderID
INNER JOIN Customers AS c ON c.CustomerID = o.CustomerID
WHERE EXISTS
(
SELECT *
FROM FirstCouponOrder x
WHERE x.CustomerID = o.CustomerID
)

[/code]

KH
[spoiler]Time is always against us[/spoiler]

visakh16
Very Important crosS Applying yaK Herder

52326 Posts

Posted - 2010-01-12 : 01:49:35

[code]
SELECT o.OrderID
,o.CustomerID
,c.FirstName + ' ' + c.LastName AS Name
,c.Country
,o.OrderDate
,o.OrderStatus
,od.CouponCode
FROM Orders AS o
JOIN OrderDetails AS od
ON o.OrderID = od.OrderID
JOIN Customers AS c
ON c.CustomerID = o.CustomerID
JOIN Discounts d
ON d.CouponCode = od.CouponCode
CROSS APPLY (SELECT COUNT(*) AS OrderCnt
FROM Orders
WHERE CustomerID=c.CustomerID
AND OrderDate > o.OrderDate )o1
WHERE o1.OrderCnt > 0
ORDER BY o.CustomerID
[/code]

fawadafr
Starting Member

47 Posts

Posted - 2010-01-12 : 12:06:32

Hello visakh16,

Your modification to the SQL code only returns the orders with coupons. I also need to show all consecutive orders for each customer after using the coupon. Please click here to see the results: [url]http://www.implantdirect.com/ahmad/msdn/sql/images/DB_Results.jpg[/url]

quote:

Originally posted by visakh16


SELECT	o.OrderID
	,o.CustomerID
	,c.FirstName + ' ' + c.LastName AS Name
	,c.Country
	,o.OrderDate
	,o.OrderStatus
	,od.CouponCode
FROM	Orders AS o
JOIN	OrderDetails AS od
ON	o.OrderID = od.OrderID
JOIN	Customers AS c
ON	c.CustomerID = o.CustomerID
JOIN Discounts d
ON d.CouponCode = od.CouponCode
CROSS APPLY (SELECT COUNT(*) AS OrderCnt
             FROM Orders
             WHERE CustomerID=c.CustomerID
             AND OrderDate  > o.OrderDate )o1
WHERE o1.OrderCnt > 0
ORDER BY o.CustomerID

--
Fawad Rashidi

fawadafr
Starting Member

47 Posts

Posted - 2010-01-12 : 12:16:15

Hello Khtan,

Your modified SQL script doesn't show all customers' orders that were placed after using coupons for the first time. Please click here to see the screenshot: [url]http://www.implantdirect.com/ahmad/msdn/sql/images/DB_Results_2.jpg[/url]

quote:

Originally posted by khtan



;WITH 
FirstCouponOrder
AS
(
	SELECT	CustomerID
	FROM
	(
		SELECT	o.CustomerID
			,d.CouponCode
			,OrderNo = row_number() OVER (PARTITION BY o.CustomerID ORDER BY o.OrderDate)
		FROM	Orders AS o
			INNER JOIN OrderDetails AS od	ON	o.OrderID 	= od.OrderID
			LEFT JOIN Discounts AS d	ON	od.CouponCode 	= d.CouponCode
	) fo
	WHERE	fo.OrderNo	= 1
	AND	fo.CouponCode	IS NOT NULL
)
SELECT	o.OrderID
	,o.CustomerID
	,c.FirstName + ' ' + c.LastName AS Name
	,c.Country
	,o.OrderDate
	,o.OrderStatus
	,od.CouponCode
FROM	Orders AS o
	INNER JOIN	OrderDetails AS od	ON	o.OrderID 	= od.OrderID
	INNER JOIN	Customers AS c		ON	c.CustomerID 	= o.CustomerID
WHERE	EXISTS
	(
		SELECT	*
		FROM	FirstCouponOrder x
		WHERE	x.CustomerID	= o.CustomerID
	)

KH
[spoiler]Time is always against us[/spoiler]

--
Fawad Rashidi

visakh16
Very Important crosS Applying yaK Herder

52326 Posts

Posted - 2010-01-12 : 12:38:45

do you mean this?


SELECT	
        o.OrderID
	,o.CustomerID
	,c.FirstName + ' ' + c.LastName AS Name
	,c.Country
	,o.OrderDate
	,o.OrderStatus
	,od.CouponCode
FROM	Orders AS o
JOIN	OrderDetails AS od
ON	o.OrderID = od.OrderID
JOIN	Customers AS c
ON	c.CustomerID = o.CustomerID
LEFT JOIN Discounts d
ON d.CouponCode = od.CouponCode
CROSS APPLY (SELECT TOP 1 CouponCode
             FROM Orders AS o1
             JOIN	OrderDetails AS od1
             ON	o1.OrderID = od1.OrderID
             LEFT JOIN Discounts d1
             ON d1.CouponCode = od1.CouponCode
             WHERE o1.CustomerID=c.CustomerID
             ORDER BY o1.OrderDate)otmp
WHERE otmp.CouponCode IS NOT NULL

fawadafr
Starting Member

47 Posts

Posted - 2010-01-12 : 13:26:12

Thank you very much for your help and time Visakh16. I don't know why your SQL code only shows orders for CustomerIDs: 24, 43, 1063, 8519, 10283, and 10592 not all customers. Can you please check the screenshot here: [url]http://www.implantdirect.com/ahmad/msdn/sql/images/DB_Results_3.jpg[/url]

quote:

Originally posted by visakh16

do you mean this?


SELECT	
        o.OrderID
	,o.CustomerID
	,c.FirstName + ' ' + c.LastName AS Name
	,c.Country
	,o.OrderDate
	,o.OrderStatus
	,od.CouponCode
FROM	Orders AS o
JOIN	OrderDetails AS od
ON	o.OrderID = od.OrderID
JOIN	Customers AS c
ON	c.CustomerID = o.CustomerID
LEFT JOIN Discounts d
ON d.CouponCode = od.CouponCode
CROSS APPLY (SELECT TOP 1 CouponCode
             FROM Orders AS o1
             JOIN	OrderDetails AS od1
             ON	o1.OrderID = od1.OrderID
             LEFT JOIN Discounts d1
             ON d1.CouponCode = od1.CouponCode
             WHERE o1.CustomerID=c.CustomerID
             ORDER BY o1.OrderDate)otmp
WHERE otmp.CouponCode IS NOT NULL

--
Fawad Rashidi

Kristen
Test

22859 Posts

Posted - 2010-01-12 : 13:32:11

I would tackle this from a different angle.

I would first get a list of all Customers / Order Dates (using coupons) and then use that to join to get later orders for those customers.

What I'm not clear about is why you are selecting MAX(OrderDate) - isn't the order date a single value for the Orders table? (or perhaps its in the OrderDetails table, and thus there are multiple values per order?)

My aim is to get the customers as per your specification "find the customers who took advantage of our coupons to place their first orders and continued to order from us", so if I've got the logic for that bit wrong if you can correct it the rest of the query should then give you what you want ... hopefully!


SELECT	O2.OrderID
	,O2.CustomerID
	,C2.FirstName + ' ' + c.LastName AS Name
	,C2.Country
	,O2.OrderDate
	,O2.OrderStatus
	,OD2.CouponCode
FROM
(
	-- Get Customer ID and details of their (first?) Coupon Order
	SELECT	O1.CustomerID,
		MAX(OrderDate) AS MaxOrderDate
	FROM	Orders AS O1
		JOIN OrderDetails AS OD1
			 ON OD1.OrderID = O1.OrderID
			AND OD1.CouponCode IN (SELECT CouponCode FROM Discounts)
	GROUP BY O1.CustomerID
) AS O1
	-- now get subsequent orders for those customer (that used Coupons)
	JOIN Orders AS O2
		 ON O2.CustomerID = O1.CustomerID
		AND O2.OrderDate > O1.MaxOrderDate
	JOIN OrderDetails AS OD2
		 ON OD2.OrderID = O2.OrderID
	JOIN Customers AS C2
		 ON C2.CustomerID = O2.CustomerID
ORDER BY O2.CustomerID

visakh16
Very Important crosS Applying yaK Herder

52326 Posts

Posted - 2010-01-12 : 13:42:49

does all your orders have corresponding order item records? perhaps that explains why you're missing some records.

khtan
In (Som, Ni, Yak)

17689 Posts

Posted - 2010-01-12 : 18:15:52

fawadafr, pls post the sample data and expected output

KH
[spoiler]Time is always against us[/spoiler]

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources