Ranking Visitors - SQL Server Forums

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

General SQL Server Forums

New to SQL Server Programming

Ranking Visitors

Author

Topic

mgonda
Starting Member

29 Posts

Posted - 2011-11-04 : 14:09:24

I have 2 tables to track visitors to a hotel, as shown below. Is there a way to add the profits for each visit made by a customer, and then average those totals for the last 3 visits to the hotel? The ultimate goal is to set a ranking to each visitor by that average. Thank you ahead of time for your help.

CREATE TABLE TripStat
(ID INT,
StartVisit SMALLDATETIME,
EndVisit SMALLDATETIME)

CREATE TABLE DayStat
(ID INT,
Profit INT,
VisitDay SMALLDATETIME)

INSERT INTO TripStat (ID, StartVisit, EndVisit)
SELECT 1, '2011-01-01', '2011-01-03' UNION ALL
SELECT 1, '2011-02-01', '2011-02-03' UNION ALL
SELECT 1, '2011-03-01', '2011-03-03' UNION ALL
SELECT 2, '2011-01-01', '2011-01-03' UNION ALL
SELECT 2, '2011-02-01', '2011-02-03' UNION ALL
SELECT 2, '2011-03-01', '2011-03-03' UNION ALL
SELECT 2, '2011-04-01', '2011-04-03' UNION ALL
SELECT 3, '2011-01-01', '2011-01-05'

INSERT INTO DayStat (ID, Profit, VisitDay)
SELECT 1, 2, '2011-01-01' UNION ALL
SELECT 1, 5, '2011-01-02' UNION ALL
SELECT 1, 3, '2011-01-03' UNION ALL
SELECT 1, 9, '2011-02-01' UNION ALL
SELECT 1, 5, '2011-02-02' UNION ALL
SELECT 1, 4, '2011-02-03' UNION ALL
SELECT 1, 20, '2011-03-01' UNION ALL
SELECT 1, 25, '2011-03-02' UNION ALL
SELECT 1, 30, '2011-03-03' UNION ALL
SELECT 2, 900, '2011-01-01' UNION ALL
SELECT 2, 1000, '2011-01-02' UNION ALL
SELECT 2, 800, '2011-01-03' UNION ALL
SELECT 2, 24, '2011-02-01' UNION ALL
SELECT 2, 25, '2011-02-02' UNION ALL
SELECT 2, 30, '2011-02-03' UNION ALL
SELECT 2, 21, '2011-03-01' UNION ALL
SELECT 2, 24, '2011-03-02' UNION ALL
SELECT 2, 34, '2011-03-03' UNION ALL
SELECT 2, 21, '2011-04-01' UNION ALL
SELECT 2, 25, '2011-04-02' UNION ALL
SELECT 2, 39, '2011-04-03' UNION ALL
SELECT 3, 65, '2011-01-01' UNION ALL
SELECT 3, 100, '2011-01-02' UNION ALL
SELECT 3, 90, '2011-01-03' UNION ALL
SELECT 3, 80, '2011-01-04' UNION ALL
SELECT 3, 75, '2011-01-05'

Ranking
1 AverageTripSum >= 200
2 AverageTripSum BETWEEN 60 AND 200
3 AverageTripSum BETWEEN 0 AND 60

Desired Results
3 rank 1
2 rank 2
1 rank 3

ID 2 should not have had january trip contributing to the ranking at all.

singularity
Posting Yak Master

153 Posts

Posted - 2011-11-06 : 12:30:59

[code]
select ID, case when avg(Profit) between 0 and 60 then 3
when avg(Profit) > 60 and avg(Profit) < 200 then 2 else 1 end as rk
from
(select a.ID, a.StartVisit, a.EndVisit, sum(b.Profit) as Profit,
row_number() over (partition by a.ID order by a.EndVisit desc) as rn
from TripStat a
join DayStat b on a.ID = b.ID and b.VisitDay between a.StartVisit and a.EndVisit
group by a.ID, a.StartVisit, a.EndVisit) a
where rn <= 3
group by ID
[/code]

mgonda
Starting Member

29 Posts

Posted - 2011-11-07 : 12:16:42

Is there a way to do this without Row_Number()? My version of SQL server doesn't recognize that function.

X002548
Not Just a Number

15586 Posts

Posted - 2011-11-07 : 13:12:49

>> D 2 should not have had january trip contributing to the ranking at all.

What is this then? I'm confused

SELECT 2, 900, '2011-01-01' UNION ALL
SELECT 2, 1000, '2011-01-02' UNION ALL
SELECT 2, 800, '2011-01-03' UNION ALL

SELECT 2, '2011-01-01', '2011-01-03' UNION ALL

Brett

8-)

Hint: Want your questions answered fast? Follow the direction in this link
http://weblogs.sqlteam.com/brettk/archive/2005/05/25/5276.aspx

Want to help yourself?

http://msdn.microsoft.com/en-us/library/ms130214.aspx

http://weblogs.sqlteam.com/brettk/

http://brettkaiser.blogspot.com/

X002548
Not Just a Number

15586 Posts

Posted - 2011-11-07 : 13:16:37

And can you have the same ranking more than once..it also appears you want the ranking by Month

Can you give us a better idea what the result set is suppose to look like?

Brett

8-)

Hint: Want your questions answered fast? Follow the direction in this link
http://weblogs.sqlteam.com/brettk/archive/2005/05/25/5276.aspx

Want to help yourself?

http://msdn.microsoft.com/en-us/library/ms130214.aspx

http://weblogs.sqlteam.com/brettk/

http://brettkaiser.blogspot.com/

mgonda
Starting Member

29 Posts

Posted - 2011-11-07 : 13:39:00

What I want is a single ranking based off of the total for the month, but only for the last 3 visits. I may need to tie in the visitor table too, then if you think that these tables would only give a monthly ranking. I just through the data out quickly, but some visitors may have come only once each year for the last few years, but like in visitor 2, I only wanted the most recent 3 visits, and then to disregard visits beyond that. The economy is changing enough that 3 visits tends to give a better representation of what each visitor is currently.

mgonda
Starting Member

29 Posts

Posted - 2011-11-07 : 13:44:02

I hope that better explains it.

Thank you all, by the way, for your consideration.

mgonda
Starting Member

29 Posts

Posted - 2011-11-07 : 13:47:32

The reply from Singularity, from the little I know of SQL, looks like it would give me exactly what I'm looking for, if this database were on a newer version of SQL Server.

X002548
Not Just a Number

15586 Posts

Posted - 2011-11-07 : 13:57:55

I thought you wanted it by dates, so if I do this


SELECT ID, SUM(Profit) AS SUM_PROFIT, YEAR(VisitDay) AS [YEAR], MONTH (VisitDay) AS MONTH
		 , MAX(VisitDay) AS MAX_Visit FROM DayStat
GROUP BY ID, YEAR(VisitDay), MONTH (VisitDay)

I get



ID          SUM_PROFIT  YEAR        MONTH       MAX_Visit
----------- ----------- ----------- ----------- -----------------------
1           10          2011        1           2011-01-03 00:00:00
1           18          2011        2           2011-02-03 00:00:00
1           75          2011        3           2011-03-03 00:00:00
2           2700        2011        1           2011-01-03 00:00:00
2           79          2011        2           2011-02-03 00:00:00
2           79          2011        3           2011-03-03 00:00:00
2           85          2011        4           2011-04-03 00:00:00
3           410         2011        1           2011-01-05 00:00:00

(8 row(s) affected)

Is this somewhat in the right direction?

Brett

8-)

Hint: Want your questions answered fast? Follow the direction in this link
http://weblogs.sqlteam.com/brettk/archive/2005/05/25/5276.aspx

Want to help yourself?

http://msdn.microsoft.com/en-us/library/ms130214.aspx

http://weblogs.sqlteam.com/brettk/

http://brettkaiser.blogspot.com/

mgonda
Starting Member

29 Posts

Posted - 2011-11-07 : 14:12:23

Yes, then you average them, so:
ID___ AVG_SUM_Profit_ Rank
----- --------------- -----
1____ 34.33__________ 3____
2____ 81_____________ 2____
3____ 410____________ 1____

mgonda
Starting Member

29 Posts

Posted - 2011-11-07 : 14:15:34

Again, as you can see with visitor 2, only taking the most recent 3 visits into account.

X002548
Not Just a Number

15586 Posts

Posted - 2011-11-07 : 14:27:12

Only take the last 3 visits by Month?

Who and why is making the BRD?

The professor?

Brett

8-)

Hint: Want your questions answered fast? Follow the direction in this link
http://weblogs.sqlteam.com/brettk/archive/2005/05/25/5276.aspx

Want to help yourself?

http://msdn.microsoft.com/en-us/library/ms130214.aspx

http://weblogs.sqlteam.com/brettk/

http://brettkaiser.blogspot.com/

X002548
Not Just a Number

15586 Posts

Posted - 2011-11-07 : 14:41:36

here are the last 3 days..should it be by month as well?


SELECT ID, Profit, VisitDay
	FROM DayStat o
WHERE EXISTS (SELECT * FROM DayStat i
			   WHERE i.ID = o.ID
			GROUP BY ID
			  HAVING o.VisitDay = MAX(VisitDay))

Brett

8-)

Hint: Want your questions answered fast? Follow the direction in this link
http://weblogs.sqlteam.com/brettk/archive/2005/05/25/5276.aspx

Want to help yourself?

http://msdn.microsoft.com/en-us/library/ms130214.aspx

http://weblogs.sqlteam.com/brettk/

http://brettkaiser.blogspot.com/

X002548
Not Just a Number

15586 Posts

Posted - 2011-11-07 : 14:56:04

And here are the Last 3 rows


SELECT ID, Profit, VisitDay
	FROM DayStat o
WHERE EXISTS (SELECT * FROM DayStat i
			   WHERE i.ID = o.ID
			GROUP BY ID
			  HAVING o.VisitDay = MAX(VisitDay))
UNION

SELECT ID, Profit, VisitDay
  FROM DayStat o
 WHERE EXISTS (  
		SELECT *
		  FROM DayStat o1
		 WHERE EXISTS (SELECT * FROM DayStat i2
				        WHERE i2.ID = o1.ID
				     GROUP BY ID
				       HAVING o1.VisitDay < MAX(VisitDay))
		   AND o1.ID = o.ID
		 GROUP BY ID
		HAVING o.VisitDay = MAX(VisitDay))		 
UNION

SELECT ID, Profit, VisitDay
  FROM DayStat o
 WHERE EXISTS (
		SELECT *
		  FROM DayStat o1
		 WHERE EXISTS (  
				SELECT *
				  FROM DayStat o2
				 WHERE EXISTS (SELECT * FROM DayStat i3
								WHERE i3.ID = o2.ID
							 GROUP BY ID
							   HAVING o2.VisitDay < MAX(VisitDay))
				   AND o2.ID = o.ID
				 GROUP BY o2.ID
				HAVING o1.VisitDay < MAX(VisitDay))
		   AND o1.ID = o.ID
	  GROUP BY o1.ID
	    HAVING o.VisitDay = MAX(VisitDay))

mgonda
Starting Member

29 Posts

Posted - 2011-11-07 : 15:30:26

This is actually for work, we're trying to market special offers to our best customers, and alter the offers according to how much they're making us.

mgonda
Starting Member

29 Posts

Posted - 2011-11-07 : 16:25:31

quote:

Originally posted by X002548


SELECT ID, SUM(Profit) AS SUM_PROFIT, YEAR(VisitDay) AS [YEAR], MONTH (VisitDay) AS MONTH
		 , MAX(VisitDay) AS MAX_Visit FROM DayStat
GROUP BY ID, YEAR(VisitDay), MONTH (VisitDay)

I get



ID          SUM_PROFIT  YEAR        MONTH       MAX_Visit
----------- ----------- ----------- ----------- -----------------------
1           10          2011        1           2011-01-03 00:00:00
1           18          2011        2           2011-02-03 00:00:00
1           75          2011        3           2011-03-03 00:00:00
2           2700        2011        1           2011-01-03 00:00:00
2           79          2011        2           2011-02-03 00:00:00
2           79          2011        3           2011-03-03 00:00:00
2           85          2011        4           2011-04-03 00:00:00
3           410         2011        1           2011-01-05 00:00:00

(8 row(s) affected)

I think we should go back to this point, because we seemed to be correct here. The later queries are confusing me. Now that we have these sums, we need to choose the 3 most recent Max_Visit dates for each visitor.

mgonda
Starting Member

29 Posts

Posted - 2011-11-07 : 16:33:36

Actually, I was looking at the output more than the query itself here, and now that I look closer at it, it doesn't actually take the trip into account, and that has to do with poor data I put in. This assumes that each visitor is only coming on one trip per month, which often isn't the case. We have some com every weekend, which amounts to multiple monthly trips. With this query the way it is, it doesn't take those weekend trips into account.

mgonda
Starting Member

29 Posts

Posted - 2011-11-07 : 16:59:57

quote:

Originally posted by singularity


select ID, case when avg(Profit) between 0 and 60 then 3 
                when avg(Profit) > 60 and avg(Profit) < 200 then 2 else 1 end as rk
from
(select a.ID, a.StartVisit, a.EndVisit, sum(b.Profit) as Profit, 
        row_number() over (partition by a.ID order by a.EndVisit desc) as rn
from TripStat a
join DayStat b on a.ID = b.ID and b.VisitDay between a.StartVisit and a.EndVisit
group by a.ID, a.StartVisit, a.EndVisit) a
where rn <= 3
group by ID

Like I said earlier, I'm pretty sure singularity had it right if the company had a newer version of SQL Server, I'll walk through what I see this doing, the only problem is that I can't use the row_number() function.


select ID, case when avg(Profit)--This is taking the sum of the daily profits, and averaging them, and then assigning the ranking to it 
                between 0 and 60 then 3 
                when avg(Profit) > 60 and avg(Profit) < 200 then 2 else 1 end as rk
from
(select a.ID, a.StartVisit, a.EndVisit, sum(b.Profit) as Profit, 
        row_number() over (partition by a.ID order by a.EndVisit desc) as rn -- this row_number is taking the visits, and ordering them by their most recent as the lower numbers, and doing so according the the EndVisit Date
from TripStat a
join DayStat b on a.ID = b.ID and b.VisitDay between a.StartVisit 
--Here is where it takes only the days during each visit
and a.EndVisit
group by a.ID, a.StartVisit, a.EndVisit) a
--This is where it takes only the most recent 3
where rn <= 3
group by ID

Hopefully seeing the needed aspects here will make the ultimate goal a little more clear. I do wish I had more authorities in this system, to where I could make some views that could potentially help, organize it a little cleaner, but I'm a new guy, and therefore have to do it all in one query. Again, I think this would work, if I could just get it to ohoose the most recent 3 visits from each customer.

X002548
Not Just a Number

15586 Posts

Posted - 2011-11-07 : 17:43:24

The query with the Unions gets you the 3 mosyt current rows based on ID. If you want to add month and year you need to add then as part of the grouping

Brett

8-)

Hint: Want your questions answered fast? Follow the direction in this link
http://weblogs.sqlteam.com/brettk/archive/2005/05/25/5276.aspx

Want to help yourself?

http://msdn.microsoft.com/en-us/library/ms130214.aspx

http://weblogs.sqlteam.com/brettk/

http://brettkaiser.blogspot.com/

mgonda
Starting Member

29 Posts

Posted - 2011-11-07 : 18:06:15

Thank you. I had tried running the UNION query to see exactly what it did, and let it run for 15 min, before I decided to stop it. I guess I just hadn't given it enough time.

Thanks again for your help.

X002548
Not Just a Number

15586 Posts

Posted - 2011-11-08 : 09:43:29

How many rows of data do you have?

How are the tables indexed?

We need to see all of the DDL including indexes and constraints

Do you know how to script a table?

Brett

8-)

Hint: Want your questions answered fast? Follow the direction in this link
http://weblogs.sqlteam.com/brettk/archive/2005/05/25/5276.aspx

Want to help yourself?

http://msdn.microsoft.com/en-us/library/ms130214.aspx

http://weblogs.sqlteam.com/brettk/

http://brettkaiser.blogspot.com/

Next Page

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources