如何根据固定百分比计算不同的日期时间戳和过滤条目？

Question

visitor ---  timestamp
abc     ---  2018-02-03 03:24:26
abc     ---  2018-02-03 02:20:20
abc     ---  2018-02-01 15:10:26
abc     ---  2018-01-31 18:24:26
abc     ---  2018-01-30 12:20:20
abc     ---  2018-01-26 10:10:26
def     ---  2018-02-03 03:24:26
def     ---  2018-01-30 12:20:20
def     ---  2018-01-26 10:10:26

I am trying to create a query that finds whether visitors abc, def are loyal.

My though is to have a fixed timeframe of 14 days. The question is how many times does a visitor signed in. In th case of abc had made visits in 5 unique days on this 14 days timeframe.

On the other hand, def made visits in 3 unique days.

What I want is to filter, let' say, which visitor was at least 30% loyal on this 14 days timeframe. In this example, abc is, but def is not.

The problems with the query:

• it doesn't take into consideraton the distinct(date(timestamp) and it sums all the visits including multiple in a day for each visitor
• I don't know how to filter them let's say 30% as mentioned above

---

SELECT visitor, Sum(DATE (TIMESTAMP) BETWEEN Date_sub(Curdate(), INTERVAL 14 day)
            AND Curdate()) AS last_twoWeeks
FROM behaviour
WHERE TIMESTAMP >= Curdate() - INTERVAL 14 day
GROUP BY visitor
ORDER BY last_twoweeks DESC

Answer 1

You are looking for count(distinct). To get only the "loyal" visitors, you can use a HAVING clause:

SELECT visitor, 
       COUNT(DISTINCT Date(timestamp)) AS days_last_two_weeks
FROM behaviour
WHERE timestamp >= Curdate() - INTERVAL 14 day
GROUP BY visitor
HAVING days_last_two_weeks >= 0.3 * 14
ORDER BY last_twoweeks DESC;