• Status: Solved
  • Priority: Medium
  • Security: Public
  • Views: 59
  • Last Modified:

SQL select query

How would you write a query to segment the customer based on Number of Unique City they have visited?
Based on the sample data, here is the output for reference.

NumberOfCityVisited      NumberOfCustomers
1                                                  3
2                                                  1
3                                                  1



Table CustomerVisit

CustId   VisitDate     VisitedCity
101      1/1/2017      City1
101      1/5/2016      City2
101      1/3/2016      City3
102      1/1/2016      City1
102      1/5/2016      City2
102      1/3/2016      City1
102      1/1/2016      City2
103      1/1/2017      City1
104      1/1/2017      City2
105      1/1/2017      City4
...     ...                     ...

Thank you in advance.
0
quasar_ee
Asked:
quasar_ee
1 Solution
 
Éric MoreauSenior .Net ConsultantCommented:
try this:
DECLARE @CustomerVisit TABLE (
	CustId		INTEGER,
	VisitDate	DATETIME,
	VisitedCity	VARCHAR(20)
)

INSERT INTO	@CustomerVisit ( CustId, VisitDate, VisitedCity )
VALUES 
(101, '1/1/2017','City1'),
(101, '1/5/2016','City2'),
(101, '1/3/2016','City3'),
(102, '1/1/2016','City1'),
(102, '1/5/2016','City2'),
(102, '1/3/2016','City1'),
(102, '1/1/2016','City2'),
(103, '1/1/2017','City1'),
(104, '1/1/2017','City2'),
(105, '1/1/2017','City4')

SELECT A.NumberOfCityVisited, COUNT(*) AS NumberOfCustomers
FROM (
	SELECT CustId, COUNT(DISTINCT VisitedCity) AS NumberOfCityVisited
	FROM @CustomerVisit 
	GROUP BY CustId
) AS A
GROUP BY A.NumberOfCityVisited

Open in new window

0
 
quasar_eeAuthor Commented:
Thanks!
0
Question has a verified solution.

Are you are experiencing a similar issue? Get a personalized answer when you ask a related question.

Have a better answer? Share it in a comment.

Join & Write a Comment

Featured Post

Free Tool: SSL Checker

Scans your site and returns information about your SSL implementation and certificate. Helpful for debugging and validating your SSL configuration.

One of a set of tools we are providing to everyone as a way of saying thank you for being a part of the community.

Tackle projects and never again get stuck behind a technical roadblock.
Join Now