CODEWITHSUNDEEP
mysqlsqlcountdistinct
Sharon
Sharon

Reputation: 43

Find the number of unique users who have visited at least two different countries per site

Find the number of unique users who have visited at least two different countries per site. Given timestamp,user,country,site

I think the query should look like this, but it doesnt seem right because it gives very similar answer to number of unique users per site.

SELECT site_id, COUNT (DISTINCT user_id) 
FROM SWE 
GROUP BY site_id
HAVING COUNT(country_id) >=2
ORDER BY site_id ASC;

Upvotes: 1

Views: 322

Answers (2)

Gordon Linoff
Gordon Linoff

Reputation: 1269973

Two levels of aggregation is the most natural way to write the query:

select site_id, count(*)
from (select user_id, site_id, count(*)
      from swe
      group by user_id, site_id
      having min(country) <> max(country)  -- or count(distinct country) >= 2
     ) us
group by site_id;

Upvotes: 3

mkRabbani
mkRabbani

Reputation: 16908

Try this- 

SELECT A.user_id,B.site_id,COUNT(DISTINCT B.country_id) [Country Visited]
FROM
(
    SELECT user_id 
    FROM SWE
    GROUP BY user_id
    HAVING COUNT(site_id) = COUNT(DISTINCT site_id)
)A
INNER JOIN SWE B ON A.user_id = B.user_id
GROUP BY A.user_id,B.site_id
HAVING COUNT(DISTINCT B.country_id) >= 2

Upvotes: 1

Related Questions