I am trying to count unique user visits (user x visit link A 20 times and link B 5 times count 2) grouped by quarter and year
This is the table:

So far my query looks like this

SELECT 
  CONCAT(QUARTER(added_on)," ",YEAR(added_on)) AS quarter_year
FROM table
GROUP BY YEAR(added_on), QUARTER(added_on)
ORDER BY added_on DESC

CodePudding user response：

If you want the number of visits per user per link use this query.

SELECT 
  CONCAT(QUARTER(added_on)," ",YEAR(added_on)) AS quarter_year,
  user_id,
  link,
  count(id) as number_of_visits
FROM t
GROUP BY 
  1,
  user_id,
  link
ORDER BY 
  1 DESC,
  user_id;

If you want the number of different links visited by the user then use the following query.

SELECT 
  CONCAT(QUARTER(added_on)," ",YEAR(added_on)) AS quarter_year,
  user_id,
  count(distinct link) as number_of_links
FROM t
GROUP BY 
  1,
  user_id
ORDER BY 
  1 DESC,
  user_id;

If you want the number of distinct visits (user - link) per quarter then use this query:

select
  quarter_year,
  sum(number_of_links) as distinct_visits
from (
SELECT 
  CONCAT(QUARTER(added_on)," ",YEAR(added_on)) AS quarter_year,
  user_id,
  count(distinct link) as number_of_links
FROM t
GROUP BY 
  1,
  user_id
ORDER BY 
  1 DESC,
  user_id) q
group by quarter_year
order by quarter_year;

db<>fiddle here