Q:

count user matches per day in SQL

for a table like:
CREATE TABLE IF NOT EXISTS `like` (
 `liking_user_id` int(6) unsigned NOT NULL,
 `liked_user_id` int(6) unsigned NOT NULL,
 `like_time` timestamp NOT NULL,
)

that would look like
liking_user_id                   liked_user_id                 like_time
1                               2               2018-04-18 00:02:07
2                               4               2018-04-19 00:09:07
2                               1               2018-04-22 00:02:07
4                               5               2018-04-23 00:02:07
1                               4               2018-04-24 00:02:07
4                               2               2018-04-25 00:02:07

in the above data set the matches happened on 4/22 between userid= 1 and userid= 2 and on 4/25 between userid=2 and userid=4
I would like to write a query that counts the number of mutual like per day (group by date(like_time)). A mutual like happens at the time a user likes another user who has liked him/her before.
the expected result should be like :
date(like_time) as Date               count(liking_user_id ...) as Count

2018-04-17                                      0

2018-04-18                                      0

2018-04-19                                      0

2018-04-20                                      0

2018-04-21                                      0

2018-04-22                                      1

2018-04-23                                      0

2018-04-24                                      0

2018-04-25                                      1

A:

With no unique constraint, and the specification for the matching row to be "who has liked him/her before", 
to avoid problems with potential "duplicates" from a JOIN, I'd opt for an EXISTS with a correlated subquery to test for the existence of the corresponding row with an earlier timestamp 
Something like this:
  SELECT DATE(t.`like_time`)  AS `date_`
       , COUNT(1)             AS `count_`
    FROM `like` t
   WHERE EXISTS (     -- corresponding row with earlier timestamp
                  SELECT 1
                    FROM `like` f
                   WHERE f.`liked_user_id`  = t.`liking_user_id`
                     AND f.`liking_user_id` = t.`liked_user_id`
                     AND f.`like_time`      < t.`like_time`
                )
   GROUP
      BY DATE(t.`like_time`) 

For verifying the query, I'd opt to use a query that returns detail rows (avoid the GROUP BY and aggregate function)

With no unique constraint, the counts we return might not be of "distinct" mutual likes. Consider 
t_id  f_id  liked_time
----  ----  ----------
 66    77   2019-06-01
 77    66   2019-06-02
 66    77   2019-06-05
 77    66   2019-06-05
 66    77   2019-06-06

The query is going to return a resultset like
 date_       count_
 ----------  ------
 2019-06-02       1
 2019-06-05       2
 2019-06-06       1

Followup:
to return "zero" counts, we can do something like this. wrap the query above in parens, as an inline view. and outer join that to a calendar row source, that returns a distinct list of date values we want to return, including the dates that don't have any liking/liked/liking rows to count
SELECT c.date_
     , IFNULL(s.count_,0) AS `count_`
  FROM ( SELECT DATE(d.`like_time`)  AS `date_`
           FROM `like` d
          GROUP
             BY DATE(d.`like_time`)
       ) c
  LEFT
  JOIN ( SELECT DATE(t.`like_time`)  AS `date_`
              , COUNT(1)             AS `count_`
           FROM `like` t
          WHERE EXISTS (     -- corresponding row with earlier timestamp
                         SELECT 1
                           FROM `like` f
                          WHERE f.`liked_user_id`  = t.`liking_user_id`
                            AND f.`liking_user_id` = t.`liked_user_id`
                            AND f.`like_time`      < t.`like_time`
                       )
          GROUP
             BY DATE(t.`like_time`)
       ) s
    ON s.`date_` = c.`date_`   
 ORDER
    BY c.`date_`

okay, that's kind of confusing. lets look at the indvidual SELECT statements. that inline view c, we can run that query separately 
         SELECT DATE(d.`like_time`)  AS `date_`
           FROM `like` d
          GROUP
             BY DATE(d.`like_time`)

That's getting us a list of dates, it's a "calendar" rowsource; we could reference a "calendar" table, something to give us the dates we want.
the inline view labeled/aliased as s is the query from the top of the question.
when we get our brain wrapped around that, we see what we have really is a query of the form:
SELECT c.date_
     , IFNULL(s.count_,0) AS count_
  FROM c
  LEFT
  JOIN s
    ON s.date_ = c.date_   
 ORDER
    BY c.date_

(The IFNULL lets us replace the NULL from the missing rows with a zero)
There are other query patterns that will return an equivalent result, e.g.
SELECT s.date_
     , SUM(s.has_match_) AS count_
  FROM ( SELECT DATE(t.like_time)  AS date_
              , EXISTS ( SELECT 1
                           FROM `like` f
                          WHERE f.liked_user_id  = t.liking_user_id
                            AND f.liking_user_id = t.liked_user_id
                            AND f.like_time      < t.like_time
                       ) AS has_match_
                  FROM `like` t
       ) s
 GROUP
    BY s.date_
 ORDER
    BY s.date_

