title: mysql counts the number of new visitors by day
body: <p>MySQL version: 5.7
  Here is <strong>users</strong> table:</p>
  <pre><code>+------------+------+
  | date       | uid |
  +------------+------+
  | 2020-06-29 05:00:00 | 352  |
  | 2020-06-29 08:00:00 | 354  |
  | 2020-06-29 09:25:53 | 354  |
  | 2020-06-30 08:00:00 | 863  |
  | 2020-06-30 09:00:01 | 352  |
  | 2020-06-30 09:59:59 | 352  |
  | 2020-07-01 07:00:00 | 358  |
  | 2020-07-01 09:00:00 | 358  |
  +------------+------+
  </code></pre>
  <p>I want to count the number of new visitors per day,But there is an important condition here that new visitors of the day cannot be visited before.</p>
  <p><strong>I want the result</strong>:</p>
  <pre><code>Result:
  +------------+------------------+
  | date       | new_user_count   |
  +------------+------------------+
  | 2020-06-29 |         2        |
  | 2020-06-30 |         1        |
  | 2020-07-01 |         1        |
  +------------+------------------+
  </code></pre>
  <p>The above result is equivalent to these three sql:</p>
  <pre><code>2020-06-29 (352,354) : select count( distinct uid ) as new_user_count from users where DATE(date) = '2020-06-29' and uid not in ( select distinct uid from users where date &lt; '2020-06-29 05:00:00'); #2 
  2020-06-30 (863): select count( distinct uid ) as new_user_count from users where DATE(date)= '2020-06-30' and uid not in ( select distinct uid from users where date &lt; '2020-06-30 08:00:00'); # 1
  2020-07-01 (358): select count( distinct uid ) as new_user_count from users where DATE(date)= '2020-07-01' and uid not in ( select distinct uid from users where date &lt; '2020-07-01 07:00:00'); # 1
  </code></pre>
  <p>I haven't thought of it until now, thanks
  Here is <a href="http://sqlfiddle.com/#!9/2c6ee7/1" rel="nofollow noreferrer">Online users table</a></p>