Selecting largest drop in number of visitors

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

Selecting largest drop in number of visitors

Author

Topic

Nokushi0
Starting Member

6 Posts

Posted - 2010-07-19 : 09:29:52

Hi, I had this question as part of a skills test for this job I applied to. I just for the life of me couldn't think of an elegant way of solving it. Anyways, here goes:

you have a two tables, one lists a number of cities, the second lists number of visitors per year. How would you select the city with the largest drop in visitors in the last year.

FOr simplicity's sake, tblCities has two fields: CityID and CItyName. tblVisitorsPerYear has 3 fields: CityID, Year, NumVisitors.

I would appreciate it if someone could help me out here. I've already not got the job, so I won't be cheating, but I can see this is a problem that could pop up in the real world.

webfred
Master Smack Fu Yak Hacker

8781 Posts

Posted - 2010-07-19 : 09:49:02

Something like this:

select top 1
c.CityName,
v.Year,
v.NumVisitors
from tblCities c
join tblVisitors v on c.CityId = v.CityId
where Year = year(dateadd(yy,-1,getdate()))
order by NumVisitors DESC

No, you're never too old to Yak'n'Roll if you're too young to die.

Lamprey
Master Smack Fu Yak Hacker

4614 Posts

Posted - 2010-07-19 : 11:04:09

maybe this?

SELECT 
	* 
FROM 
	tblCity AS City
INNER JOIN
	(
		SELECT TOP 1 A.CityID
		FROM tblVisitorsPerYear AS A
		LEFT OUTER JOIN tblVisitorsPerYear AS B
		ON A.Year = B.Year - 1
		AND A.CityID = B.CityID
		ORDER BY COALESCE((A.NumVisitors - B.NumVisitors), 0) DESC
	) AS Visitor
	ON City.CityID = Visitor.CityID

Note, this will only work if Years are sequential.

Nokushi0
Starting Member

6 Posts

Posted - 2010-07-19 : 12:03:30

quote:

Originally posted by Lamprey

maybe this?

SELECT 
	* 
FROM 
	tblCity AS City
INNER JOIN
	(
		SELECT TOP 1 A.CityID
		FROM tblVisitorsPerYear AS A
		LEFT OUTER JOIN tblVisitorsPerYear AS B
		ON A.Year = B.Year - 1
		AND A.CityID = B.CityID
		ORDER BY COALESCE((A.NumVisitors - B.NumVisitors), 0) DESC
	) AS Visitor
	ON City.CityID = Visitor.CityID

Note, this will only work if Years are sequential.

I haven't tested your code yet, but anyone who tests for NULL using COALESCE() must know what they're talking about. Thanks.

Kristen
Test

22859 Posts

Posted - 2010-07-19 : 12:56:52

"How would you select the city with the largest drop in visitors in the last year"

Thus I think this:


SELECT TOP 1 City.CityName,
             VisitorsLastYear.NumVisitors AS VisitorsLastYear,
             VisitorsThisYear.NumVisitors AS VisitorsThisYear
FROM    tblCity AS City
        JOIN tblVisitorsPerYear AS VisitorsLastYear
	    ON VisitorsLastYear.CityID = City.CityID
	   AND VisitorsLastYear.Year = 2009
        LEFT OUTER JOIN tblVisitorsPerYear AS VisitorsThisYear
	    ON VisitorsThisYear.CityID = City.CityID
	   AND VisitorsThisYear.Year = 2010
ORDER BY COALESCE(VisitorsLastYear.NumVisitors, 0) - COALESCE(VisitorsThisYear.NumVisitors, 0)

Lamprey
Master Smack Fu Yak Hacker

4614 Posts

Posted - 2010-07-19 : 13:14:49

quote:
Originally posted by Kristen

"How would you select the city with the largest drop in visitors in the last year"

Doh, I missed the part where it was just for last year.

Nokushi0
Starting Member

6 Posts

Posted - 2010-07-20 : 04:14:58

quote:
Originally posted by Lamprey

quote:
Originally posted by Kristen

"How would you select the city with the largest drop in visitors in the last year"
Doh, I missed the part where it was just for last year.

No worries. Your solution is more flexible, hence more useful. Plus, I am not entirely a n00b, so I managed to extract all the relevant info I needed from your solution.

Kristen
Test

22859 Posts

Posted - 2010-07-20 : 04:37:15

Well ... being pedantic Lamprey's solution requires a visit count for a city for this year, but the count for last year is optional. It should be the other way around because a city might have had lots of visitors last year and no record of any visitors this year - which might constitute the largest fall.

In the real world that's probably not realistic.

In an interview that is exactly the type of thought process that I would look for in a candidate.

webfred
Master Smack Fu Yak Hacker

8781 Posts

Posted - 2010-07-20 : 04:55:08

I'm feeling ignored

What is wrong with my solution to the question
How would you select the city with the largest drop in visitors in the last year.

No, you're never too old to Yak'n'Roll if you're too young to die.

Kristen
Test

22859 Posts

Posted - 2010-07-20 : 06:46:59

"How would you select the city with the largest drop in visitors in the last year "

webfred
Master Smack Fu Yak Hacker

8781 Posts

Posted - 2010-07-20 : 07:20:56

My bad english knowledge.
But then I think the question should say in which relation the drop has to be selected.
Last year in relation to the year before the last year?

No, you're never too old to Yak'n'Roll if you're too young to die.

Nokushi0
Starting Member

6 Posts

Posted - 2010-07-20 : 07:45:27

quote:
Originally posted by webfred

I'm feeling ignored
What is wrong with my solution to the question
How would you select the city with the largest drop in visitors in the last year.

No, you're never too old to Yak'n'Roll if you're too young to die.

Nothing wrong. I needed to pick a solution and went with the one I found the most useful.

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources