SQL Server Challenging questions - look for 1st buyer, 2nd/3rd buyers, 4th or more buyers
Happy Holidays, folks,
I am assigned to write a SQL query to show the number of buyers who purchase a particular product in a certain data range and in a location.
I need to find the following:- 1st time buyer ever.
- 2nd/3rd time buyers.
- 4th or more time buyers.
I was able to get the first time ever buyers using the queries below ; but unable to find the 2nd/3rd buyers, 4th or more buyers. Could someone please help.
DECLARE @from_dt datetime
DECLARE @Day_End_dt datetime
SET @from_dt = '10/01/2010'
SET @Day_End_dt = '12/29/2010'
select count(B.buyer_id) as California_1st_time_buyer
from
--LIST OF ALL BUYERS AND FIRST PURCHASED DATE 开发者_StackOverflowTHRU END OF DATE RANGE (10/1/98 - 12/28/10)
(select distinct buyer_id, min(purchased_date_time) as First_timer
from product_details
where purchased_date <@Day_End_dt
group by buyer_id
)A,
--LIST OF CALIFORNIA BUYERS AND FIRST PURCHASED DATE IN DATE RANGE (12/1/10 - 12/28/10)
(select distinct buyer_id, min(purchased_date_time) as First_timer_ca_in_date_range
from product_details
where purchased_date >=@from_d
and purchased_date <@Day_End_dt
and location = 'CA'
group by buyer_id
)B
where A.buyer_id = B.buyer_id
and A.First_timer = B.First_timer_ca_in_date_range
Input sample:
purchased_date_time purchased_date buyer_id
12/7/2010 12:30:00 PM 12/7/2010 5627242
--------------------------------------------------------------------
12/9/2010 4:30:00 PM 12/9/2010 9231374
12/19/2010 11:30:00 AM 12/19/2010 9231374
--------------------------------------------------------------------
12/9/2010 12:10:00 AM 12/9/2010 8061088
12/15/2010 5:00:00 PM 12/15/2010 8061088
12/21/2010 6:00:00 PM 12/21/2010 8061088
--------------------------------------------------------------------
12/1/2010 12:30:00 PM 12/1/2010 2288101
12/12/2010 6:30:00 PM 12/12/2010 2288101
12/27/2010 7:30:00 PM 12/27/2010 2288101
12/28/2010 6:30:00 PM 12/28/2010 2288101
--------------------------------------------------------------------
12/9/2010 10:45:00 AM 12/9/2010 2510454
12/16/2010 9:45:00 PM 12/16/2010 2510454
12/19/2010 4:19:00 AM 12/19/2010 2510454
12/22/2010 7:05:00 AM 12/22/2010 2510454
12/29/2010 2:30:00 AM 12/29/2010 2510454
--------------------------------------------------------------------
Output sample:
1st buyers =5 --count buyer_id who purchased the product first time ever in date range (12/1/10 - 12/28/10)
2nd/3rd buyers =7 --count buyer_id who purchased the product 2nd/3rd time in date range (12/1/10 - 12/28/10)
4th or more buyers =3 --count buyer_id who purchased the product 4th time or more in date range (12/1/10 - 12/28/10)
Please note these buyers are only from the date range mentioned above. Suppose if a a buyer had three purchases prior to 12/1/2010 and he purchased again on 12/1/2010, he would be considered as a 4th timer buyer.
I don't entirely understand what you want to do. If you could update the question with Table structure, a couple of sample rows and the expected output it would help.
Anyway, here is an attempt :)
The following query should give you a list of buyers that made four or less purchases. It isn't clear if you just wanted to count the purchases or if you need the actual purchase dates too, so I added the dates as well (for the first 4 purchases). If a buyer made only 1 purchase, the row would show NULL in purchase 2/3/4 columns.
If you want to include a range of dates or filter by location, those conditions would go in the inner select.
select buyer_id
,count(*) as purchases
,max(case when purchase_no = 1 then purchased_date end) as purchase_1
,max(case when purchase_no = 2 then purchased_date end) as purchase_2
,max(case when purchase_no = 3 then purchased_date end) as purchase_3
,max(case when purchase_no = 4 then purchased_date end) as purchase_4
from (select buyer_id
,purchased_date
,row_number() over(partition by buyer_id
order by purchased_date) as purchase_no
from product_details
)
where purchase_no <= 4
group by buyer_id;
Let me know if it works.
This query will provide the buyer_id and number of purchases in the date range:
DECLARE @from_dt datetime
DECLARE @Day_End_dt datetime
SET @from_dt = '10/01/2010'
SET @Day_End_dt = '12/29/2010'
SELECT [buyer_id],COUNT([buyer_id]) AS purchases_in_period
FROM [product_details]
WHERE purchased_date_time between @from_dt and @Day_end_dt
-- OPTIONAL LOCATION FILTER
-- AND [location] = 'CA'
GROUP BY [buyer_id]
GO
If you use this as a nested query you can count the buyer_id
values with a given number of purchases_in_period
.
I would normally approach this as a union of separate queries.
select buyer from T, 1 as FirstTimeBuyer, 0 as SecondTimeBuyer, 0 as thirdtimebuyer
where date is in the specified range
group by buyer having count(t.id) = 1
UNION
select buyer from T, 0 as FirstTimeBuyer, 1 as SecondTimeBuyer, 0 as thirdtimebuyer
where date is in the specified range
group by buyer having count(t.id) = 2
UNION
select buyer from T, 0 as FirstTimeBuyer, 0 as SecondTimeBuyer, 1 as thirdtimebuyer
where date is in the specified range
group by buyer having count(t.id) = 3
精彩评论